Add Superscript in UltraEdit Using Alt Codes

If you have a dedicated numeric keypad on your keyboard, you can hold down Alt and type the decimal Unicode value. Note that your file in UltraEdit must be encoded in UTF-8 or UTF-16 for these to render properly. 

Superscript 1 (¹): Hold Alt and type 0185
Superscript 2 (²): Hold Alt and type 0178
Superscript 3 (³): Hold Alt and type 0179
Superscript 4 (⁴): Hold Alt and type 8308

Compare all variable values between two datasets and high-light mismatches in EXCEL

Click Here 

Functions can be used in fuzzy match

Notes taken from PharmSUG seminar

SOUNDEX (Sound Alike)
Ignore case, embedded blanks and punctuations; works best with English-sounding names;

Example:
WHERE varx = * "Michael";


SPEDIS (Spelling Distance)
Translating a keyword into a query containing the smallest value distance;

Example:
Spedis_Value = SPEDIS (title, "Michael"); (exact match is value 0)


COMPELVE (Levenshtein Edit Distance)
Provides an indication of how close tow strings are;

Example:
COMPLEV (Category, "Drama") as Complev_Number; (exact match is value 0)


COMPGED (Generalized Edit Distance)
Measure of dissimilarity between two strings;

Example:
COMPGED (M.Title, A.Title, 'ILN') as Compged_Score; (lowest score indicates better match)
I: ignore case, L: ignore leading blank; N:ignore quotation mark

Compress - Keep Writable

varname = compress(varname, , 'kw');

The modifier “k” stands for ‘KEEP’ and the modifier “w” stands for ‘WRITABLE’. When compress function is used in combination of K & W modifiers, it keeps all the writable characters which means it deletes all the non writable characters.

Add shade to Kaplan Meier plot

data km;
  seed=12345;
  do loc=1 to 2;
    do time=2 to 22 by 1+int(4*ranuni(seed));
      status=int(2*ranuni(seed));
      output;
    end;
  end;
run;

proc lifetest data=km plots=s outsurv=os;
  ods select survivalplot;
  time time*status(1);
  strata loc;
run;

data os;
  retain survhold 1;
  set os;
  if _censor_ = 1 then  SURVIVAL= survhold;
  else survhold= SURVIVAL;
run;

proc sgplot data=os;
  step x=time y=Survival / name="survival" legendlabel="Survival" group=stratum;
  band x=time lower=0 upper=survival / modelname="survival" transparency=.5;
run;

Create random number using call ranuni

retain seed 111618;
call ranuni(seed, ranno);

VVALUE and VVALUEX functions

VVALUEX

VVALUE

Make contents in legend in ASCENDING order

Include the GROUPORDER=ASCENDING option in the VBARPARM statements.  For example:

vbarparm category=subject response=&var / 
      group=&group datalabel=&byvar dataskin=pressed datalabelattrs=(size=6 weight=bold)
     groupdisplay=cluster clusterwidth=1 grouporder=ascending;

or

keylegend / location=inside position=topright title="Highest Grade" sortorder=ascending;

INSET in Proc SGPLOT

INSET "Mean of the best percent change = &pmean." / position = bottomleft;

Example: Click Here and Here

Import password protected EXCEL into SAS

Click Here

%macro readpass(xlsfile1,xlsfile2,passwd,outfile,sheetname,getnames);

options macrogen symbolgen mprint nocaps; options noxwait noxsync;

%* we start excel here using this routine here   *;

filename cmds dde 'excel|system';

data _null_;
  length fid rc start stop time 8;
  fid=fopen('cmds','s');
  if (fid le 0) then do;
    rc=system('start excel');
    start=datetime();
    stop=start+20;
    do while (fid le 0);
      fid=fopen('sas2xl','s');
      time=datetime();
      if (time ge stop) then fid=1;
      end;
    end;
  rc=fclose(fid);
run; quit;

%* then we open the excel sheet here with its password *;

filename cmds dde 'excel|system';

data _null_;
  file cmds;
  put '[open("'"&xlsfile1"'",,,,"'"&passwd"'")]';
run;

%* then we save it without the password *;

data _null_;
  file cmds;
  put '[error("false")]';
  put '[save.as("'"&xlsfile2"'",51,"")]';
  put '[quit]';
run;

%* Then we import the file here *;

proc import datafile="&xlsfile2" out=&outfile dbms=xlsx replace;
  %* sheet="%superq(datafilm&i)";
  sheet="&sheetname";
  getnames=&getnames;
run; quit;

%* then we destroy the non password excel file here *;

systask command "del ""&xlsfile2"" ";

proc contents data=&outfile varnum;
run;

%mend readpass;

%readpass(j:\access\accpcff\excelfiles\passpro.xlsx, /* name of the xlsx 2007 file */
          c:\sastest\nopass.xlsx,  /* temporary xls file for translation for import */
    mypass,               /* password of the excel spreadsheet          */
           work.temp1,  /* name of the sas dataset you want to write */
           sheet1,     /* name of the sheet */
           yes) ;     /* getnames  */

PROC SGPLOT: Scatter with mean value

Click Here

Fix for invalid characters in data

For "ERROR: Some character data was lost during transcoding in the dataset DB.XXXDAT. Either the data contains characters that are not representable in the new encoding or truncation occurred during transcoding." use the following code in program:

proc options option=config; run;
proc options group=languagecontrol; run;

/* Show the encoding value for the problematic data set */
%let dsn=db.xxxdat;
%let dsid=%sysfunc(open(&dsn,i));
%put &dsn ENCODING is: %sysfunc(attrc(&dsid,encoding));

/*Renaming item desc file  (encoding=any) allowed reading */
data temp;
set db.xxxdat (encoding=any);
run;

Annotate +/- one standard deviation of the mean with PROC GCHART

Click Here

{nbspace x} in PROC REPORT

ods escapechar="^";

proc report data = statsp nowd split = '|' headline headskip

            style(report) = [asis = on PROTECTSPECIALCHARS=off outputwidth=9in]

            center missing contents="" spanrows;

define rowlbl  /order "Parameter" left width=20

                     style(column)=[/*font_weight=bold*/ cellwidth=2in asis=on]

                     style(header)=[just=left];

*** Use asis=on in DEFINE statement to make {nbspace x} in ROWLBL work

Read XLS with data starts on 3rd row, and column names on 2nd row

proc import file='C:\Users\procx\sample.xls'

        out=test dbms=xls replace;

        sheet=disposition;

        namerow=2;

        startrow=3;

        getnames=yes;

run;

Get around ‘WARNING: Fisher's exact test is not computed when the total sample size exceeds 32767.’

Fisher's Exact test can be memory and computation extensive when the sample size is large. For the large sample size problem, you can use Monte-Carlo estimates of the exact p-value to get around the issue. For example,

exact fisher /mc;

More details of the MC option can be found here -

http://support.sas.com/documentation/cdl/en/statug/67523/HTML/default/viewer.htm#statug_freq_syntax03.htm#statug.freq.freqexfisher

Apply color to PROC REPORT

Highlight a row:

 

compute __stresc;

  if index(__stresc,'>') then call define(_row_,"style","style={backgroundcolor=red}");

endcomp;

Highlight a cell:

 

compute __stresc;

   if index(__stresc,'>') then call define(_col_,"style","style={color=red}");

endcomp;

Color a row:

compute __stresc;

   if index(__stresc,'>') then call define(_row_,"style","style={color=red}");

endcomp;

Dixon's Q test for outlier identification

Click Here
And Here
Here

Learn PK/PD

This is a good blog!

How to perform a fuzzy match using SAS functions

Click Here