SAS on UNIX@hsph

direct filename with path to a file

2011-03-14T17:33:00.000-07:00

dir /s/b>list.txt

Regular expression wildcard

2011-03-14T08:49:00.001-07:00

http://www.ats.ucla.edu/stat/sas/code/perl_wildcard.htm

Regular expressions

2011-03-10T12:36:00.000-08:00

1. Search and extract

a. Use Prxmatch before Prxposn

2. Remove whitespace

readhtml

xls2csv to speed up the reading

Create a variable having value of median in categories

2008-05-08T08:07:00.000-07:00

To find the median values of the categories (e.g. to assess test of trend)

%macro hint(var1,var2);

proc means data=fret median;
class &var1;
var &var2;
OUTPUT OUT=&var2 MEDIAN= ;
run;

data null;
set &var2;
call symputx("&var2.m"||put(_n_,1.),&var2,'g');
run;
%put &var2.m;
%put _user_;
%mend;
**depending on the number of categories (n), you would have n+1 macro variables.
**********run like this *************;
%hint(_alcoc,alc) /***_alcoc has 4 categories **/

/*** this would create macro variables as &alcm2 - &alcm(n+1)***/

data new;
set fret;

if _alcoc =. then alcm = .;
else if _alcoc =1 then alcm=&alcm2; /**note this is &alcm2 ***/
else if _alcoc =2 then alcm=&alcm3;
else if _alcoc= 3 then alcm=&alcm4;
else if _alcoc = 4 then alcm=&alcm5;

Coding interactions/effect modification to get Confidence Intervals

2008-03-27T11:32:00.000-07:00

/************ THIS IS 4 X 3 interaction *************/

VARA0VARB0=0; VARA0VARB1=0;VARA0VARB2=0; VARA0VARB3=0;
VARA1VARB0=0; VARA1VARB1=0;VARA1VARB2=0; VARA1VARB3=0;
VARA2VARB0=0; VARA2VARB1=0;VARA2VARB2=0; VARA2VARB3=0;
VARA3VARB0=0; VARA3VARB1=0;VARA3VARB2=0; VARA3VARB3=0;

IF VARA=0 THEN DO;
IF VARB=0 THEN VARA0VARB0=0;
IF VARB=1 THEN VARA0VARB1=1;
IF VARB=2 THEN VARA0VARB2=2;
IF VARB=3 THEN VARA0VARB3=3;
END;

IF VARA=1 THEN DO;
IF VARB=0 THEN VARA1VARB0=0;
IF VARB=1 THEN VARA1VARB1=1;
IF VARB=2 THEN VARA1VARB2=2;
IF VARB=3 THEN VARA1VARB3=3;
END;

IF VARA=2 THEN DO;
IF VARB=0 THEN VARA2VARB0=0;
IF VARB=1 THEN VARA2VARB1=1;
IF VARB=2 THEN VARA2VARB2=2;
IF VARB=3 THEN VARA2VARB3=3;
END;

IF VARA=3 THEN DO;
IF VARB=0 THEN VARA3VARB0=0;
IF VARB=1 THEN VARA3VARB1=1;
IF VARB=2 THEN VARA3VARB2=2;
IF VARB=3 THEN VARA3VARB3=3;
END;

%LET VARB_VARAINT=
VARA0VARB1 VARA0VARB2 VARA0VARB3
VARA1VARB0 VARA1VARB1 VARA1VARB2 VARA1VARB3
VARA2VARB0 VARA2VARB1 VARA2VARB2 VARA2VARB3
VARA3VARB0 VARA3VARB1 VARA3VARB2 VARA3VARB3;

/* To find the point estimates and confidence intervals of each level of VARA with each level of VARB just enter the &VARB_VARAINT
in the models */

/* you can tailor the code with find and replace in SAS/Xemacs/Word/Textpad. */

/************ THIS IS 4 X 4 interaction *************/

VARA0VARB0=0; VARA0VARB1=0; VARA0VARB2=0; VARA0VARB3=0; /* 1ST QUINTILE*/
VARA1VARB0=0; VARA1VARB1=0; VARA1VARB2=0; VARA1VARB3=0; /* 2ND QUINTILE*/
VARA2VARB0=0; VARA2VARB1=0; VARA2VARB2=0; VARA2VARB3=0; /* 3RD QUINTILE */
VARA3VARB0=0; VARA3VARB1=0; VARA3VARB2=0; VARA3VARB3=0; /* 4TH QUINTILE */

IF VARA=0 then do;
if VARB=0 then VARA0VARB0=1;
if VARB=1 then VARA0VARB1=1;
if VARB=2 then VARA0VARB2=1;
if VARB=3 then VARA0VARB3=1;
END;

IF VARA=1 then do;
if VARB=0 then VARA1VARB0=1;
if VARB=1 then VARA1VARB1=1;
if VARB=2 then VARA1VARB2=1;
if VARB=3 then VARA1VARB3=1;
END;

IF VARA=2 then do;
if VARB=0 then VARA2VARB0=1;
if VARB=1 then VARA2VARB1=1;
if VARB=2 then VARA2VARB2=1;
if VARB=3 then VARA2VARB3=1;
END;

IF VARA=3 then do;
if VARB=0 then VARA3VARB0=1;
if VARB=1 then VARA3VARB1=1;
if VARB=2 then VARA3VARB2=1;
if VARB=3 then VARA3VARB3=1;
END;

%LET VARA_VARBINT=
VARA0VARB1 VARA0VARB2 VARA0VARB3
VARA1VARB0 VARA1VARB1 VARA1VARB2 VARA1VARB3
VARA2VARB0 VARA2VARB1 VARA2VARB2 VARA2VARB3
VARA3VARB0 VARA3VARB1 VARA3VARB2 VARA3VARB3 ;

Use a sample to hasten preliminary analysis

2008-02-01T17:43:00.000-08:00

I have used following ways;

**************************
proc surveyselect data=onenn method=srs n=10000 out=onen;
run;

**************************
data onen;
merge
fa7684 fa8694 fa9600 nur92 nur94 n94_dt
temp(in=mstr) nur96 nur98 act8600
nur82 nur88 n84_dt n86_dt n90_dt
fileb n767880 meddata
temp db7602
fatalmi mi stroke anginew
fatalstk deadff2004 pact spact end=_end_;

by id;
exrec=1;
if first.id and mstr then exrec=0; /*** mas = master file ie. n80_cf ***/
if famdb82=1 then famdb88=1;
else famdb88=0;
random=RANUNI(-1); /* GENERATE A RANDOM VECTOR */
%let k=5000;
run;

PROC SORT DATA=onen;
BY random; /* SORT OBSERVATIONS BY THE RANDOM VECTOR */
run;

DATA onensample;
SET onen(drop=random);
IF _N_ le &k; /* SELECT THE FIRST K OBSERVATIONS */
/*both magne

Concatenating sas macro variables

2007-06-27T04:52:00.000-07:00

I always forget how to do it.

%let nagasuchi=cases;
data new;
set library.old&nagasuchi;

This would be read as
data new;
set library.oldcases;

If the macro variable is a prefix

data new;
set library.&nagasuchi.old;
/* note the period*/

This would be read as
data new;
set library.casesold;

If the character following a macro variable is a period, then you need to use two periods.
set in&nagasuchi..select;

After resolution, SAS would read this as SET incases.select;

More on this here.

LaTeX output in sas

2007-06-15T19:35:00.000-07:00

/*Use one of the following ods statements */
/* Legacy LaTeX for ODS */
ods tagsets.latex file="legacy.tex";

/* Legacy LaTeX with color for ODS */
ods tagsets.colorlatex file="color.tex" stylesheet="sas.sty"(url="sas");

/* Simplified LaTeX output that uses plain LaTeX tables */
ods tagsets.simplelatex file="simple.tex" stylesheet="sas.sty"(url="sas");

/* Same as above, but only prints out tables (no titles, notes, etc.) */
/* Also, prints each table to a separate file */
ods tagsets.tablesonlylatex file="C:\Documents and Settings\mk\My Documents\tablesonly.tex" (notop nobot) newfile=table;

proc reg data=sashelp.class;
model Weight = Height Age;
run;quit;

/*Use one of the following ods statements corresponding to open statements*/

ods tagsets.latex close;
ods tagsets.colorlatex close;
ods tagsets.tablesonlylatex close;
ods tagsets.simplelatex close;

/*from SAS*/

Unix Banner for interesting titles

2007-05-03T22:34:00.000-07:00

ASCII Banner 1 , 2

  .'|.           .                                   '||  
.||.    ....   .||.   ....      ....   .. ...      .. ||  
 ||    '' .||   ||   ||. '     '' .||   ||  ||   .'  '||  
 ||    .|' ||   ||   . '|..    .|' ||   ||  ||   |.   ||  
.||.   '|..'|'  '|.' |'..|'    '|..'|' .||. ||.  '|..'||. 
                                                          
                                                          
                          ||                   
 ....   .. ...     ... . ...  .. ...    ....   
'' .||   ||  ||   || ||   ||   ||  ||  '' .||  
.|' ||   ||  ||    |''    ||   ||  ||  .|' ||  
'|..'|' .||. ||.  '||||. .||. .||. ||. '|..'|' 
                 .|....'                       


  __       _                         _                     _            
/ _| __ _| |_ ___    __ _ _ __   __| |   __ _ _ __   __ _(_)_ __   __ _
| |_ / _` | __/ __|  / _` | '_ \ / _` |  / _` | '_ \ / _` | | '_ \ / _` |
|  _| (_| | |_\__ \ | (_| | | | | (_| | | (_| | | | | (_| | | | | | (_| |
|_|  \__,_|\__|___/  \__,_|_| |_|\__,_|  \__,_|_| |_|\__, |_|_| |_|\__,_|

Have fun. Don´t forget to bookmark this website :)

Make results more presentable

2007-04-19T18:59:00.000-07:00

SAS regression output requires additional steps to make it presentable. In previous posts, I have highlighted how to only get the results for variables of interest. However, these results are in row format

Obs F1 or lci uci p_value

1 exp1 1.202 1.110 1.34 .001
2 exp2 1.340 1.202 1.56 .001
3 exp3 1.560 1.340 1.89 .001
4 exp4 1.890 1.560 1.98 .001

This output need to be further transposed in Excel to get the results in following format.

Obs exp0 exp1 exp2 exp3 exp4

1 1 1.2020 1.3400 1.5600 1.8900
2 1.11,1.34 1.202,1.56 1.34,1.89 1.56,1.98
3 0.0010 0.0010 0.0010 0.0010

The following program eliminates that

proc import datafile="C:\Documents and Settings\mkaushik\Desktop\Output results.xls" out=auto replace;
run;
data inter /* / view=intermediate*/;
set auto;
orc= put(or,6.4);
pvaluec= put(p_value,6.4);
new=compress(lci||','||uci);
output; *output the input record;
if _n_=1 then do;
F1='exp0';
or=1; *set values for your added obs;
lci=.;uci=.;p_value=.;
orc="1";
pvaluec=" ";
new=" ";
output; *output your added obs;
end;
proc sort;
by F1;
run;
data inter;
set inter;
array Value (*) orc new pvaluec;
do id =1 to 3;
_value_ = value [id]; * since first numeric is that date;
F1=F1;
output;
end;
drop or p_value _name_;
run;
proc print data=inter;
run;

proc sort data=inter;
by id;
run;
proc transpose data=inter out=outset(drop=id _name_) ;
by id ;
id F1 ;
var _value_;
run;
proc print data=outset;
run;

Correspondence between genmod and logistic

2007-04-05T12:57:00.000-07:00

I have been exploring this for my work. I found some guidance here. I am including some details here.
data drug;
input drug$ x r n;
cards;
A .1 1 10
A .23 2 12
A .67 1 9
B .2 3 13
B .3 4 15
B .45 5 16
B .78 5 13
C .04 0 10
C .15 0 11
C .56 1 12
C .7 2 12
D .34 5 10
D .6 5 9
D .7 8 10
E .2 12 20
E .34 15 20
E .56 13 15
E .8 17 20
;

proc genmod data=drug;
class drug;
model r/n=x drug / dist=binomial link=logit;
estimate 'A vs E' drug 1 0 0 0 -1/exp;
run;

proc logistic data=drug;
class drug/param=ref;
model r/n=x drug;
run;

/* generates dummy variables coded as follows
drug A 1 0 0 0
B 0 1 0 0
C 0 0 1 0
D 0 0 0 1
E 0 0 0 0 */

proc logistic data=drug;
class drug;
model r/n=x drug;
run;

/* generates dummy variables coded as follows

Class Value Design Variables

drug A 1 0 0 0
B 0 1 0 0
C 0 0 1 0
D 0 0 0 1
E -1 -1 -1 -1
*/

However, the results (Odds ratio) are going to be the same. Accessible help on writing contrast statements is here.

Proc tabulate/proc report

2007-04-02T16:42:00.000-07:00

I have been trying to learn these new tools.

Here is a pdf with clear instructions and uses of both.

Some available options are ACROSS, ANALYSIS, CENTER, COLOR, COMPUTED, CSS, CV, DESCENDING, DISPLAY, EXCLUSIVE, F, FLOW, FORMAT, GROUP, ID, ITEMHELP, LEFT, MAX, MEAN, MEDIAN, MIN, MISSING, N, NMISS, NOPRINT, NOZERO, ORDER, P1, P10, P25, P5, P50, P75, P90, P95, P99, PAGE, PCTN, PCTSUM, PRELOADFMT, PRT, Q1, Q3, QRANGE, RANGE, RIGHT, SPACING, STD, STDERR, STYLE, SUM, SUMWGT, T, USS, VAR, WEIGHT, WGT, WIDTH.

Macro to output only relevant results to ods

2007-04-01T19:00:00.000-07:00

/** following macro runs logistic regression and outputs the results from only the relevant variables to the html file. In this case, I am running regression with various variables but keeping age in all the models. Age is not the variable of interest. */

%macro jncht(dataname,var1);
title "Age and sex adjusted &var1";
ods select OddsRatio ParameterEstimates;
proc logistic data=&dataname ;
model jncht=&var1 ahage ahsex ;
ods output OddsRatios=orrr;
ods output ParameterEstimates=Param;
run;

data param;
set param;
drop DF Estimate StdErr;
run;

proc sort;
by variable;

data orrr;
set orrr;
variable=effect;
drop effect;
proc sort;
by variable;
run;

data new;
merge param orrr;
by variable;
run;
%let cuts=%SUBSTR(&var1,1,4);

ods html select all;
title " age and sex adjusted &var1";
proc print data=new;
var variable OddsRatioEst LowerCL UpperCL ProbChiSq;
where variable like "%NRBQUOTE(%)&cuts%NRBQUOTE(%)";
run;
ods html exclude all;
proc datasets;
delete param orrr new;
%mend ;

Invocation of this macro.
/*include the following statement in the beginning of the program*/
ods html file ="%sysfunc
(reverse(%sysfunc(substr(%sysfunc(reverse(%sysfunc(reverse(%scan(%sysfunc(reverse(%sysfunc(getoption(sysin)))),1,/))))),5)))).html"
STYLE=MINIMAL;

data...;
.
.
.;
%jncht(trott1,&wstci_)

/*Where &wstci_ is a macro variable and equals 'wstci1 wstci2 wstci3'. */

Wildcards in different situations

2007-04-01T18:46:00.000-07:00

This is a broad area to handle in one posting but here are few links that I found useful:

Matching with a wildcard using Perl regular expression A possible problem with this method is inability of this method to handle macro variables.

Matching with like and percent (%) It is used in the next post.

Using wildcards to read many files into one SAS data set

Interactions in logistic regression using proc genmod

2007-03-31T09:20:00.000-07:00

I have been trying to do logistic regression with interactions. Since this would have required a lot of dummy coding in proc logistic, I used proc genmod.

proc genmod;
class ahsex(ref=first) ah66a qtype &smok_ &alco_;
model jncht= wstrs wstrs*ahsex ahsex ah66a ahage &smok_ &alco_ WLTHINDF QTYPE/ error=bin link=logit type3;
run;

Another way to encode the interaction term and the main effects would be using "wstrs|ahsex". This equals "wstrs wstrs*ahsex ahsex".
The ref option could be replaced with (ref="1") . Specifying more than one REF= variable option in the CLASS statement could be a problem.

In case you are wondering, &smok_ refer to the macro variable I created earlier in the code as follows:
%let &sm0k= smok1 smok2 smok3 smokm;

Find the number of observations in the dataset

2007-03-21T09:04:00.000-07:00

I work with huge daatsets - more than 100,000 observations followed up for 20 years. In the process of merging datasets read in through macros, I have lost track of the number of observations.

Following code gives the number of observations in the dataset - dataname:
%let dsid=%sysfunc(open(dataname));
%let num=%sysfunc(attrn(&dsid,nobs));
%let rc=%sysfunc(close(&dsid));
%put There are &num observations in dataset dataname.;

This is from the SAS samples.

Centering around mean or calculating standard deviation

2007-03-19T19:29:00.000-07:00

data original;
set original;
var=1; /*creates a constant variable*/

/*creates means ,standard deviation and no of obs and puts them in dataset called starwars which has only one observation*/
proc means data=original;
var ahbmi ah98 ah99 ah9900 ;
OUTPUT OUT=starwars MEAN=avbmi av98 av99 STD=stbmi stah98 stah99 N=nbmi n98 n99 ;
run;

data starwars;
set starwars;
var=1; /*creates constant variable for merging with original dataset*/
drop _freq_ _type_;

data original ;
merge original starwars;
by var;

centerbmi=ahbmi-avbmi; /*centers bmi*/
bmisd=ahbmi/stbmi;/*creates variable to do regression with each unit increment of standard deviation*/

/**************Alternate way***************************/
data original;
set original;
proc means data=original;
var ahbmi ah98 ah99 ah9900 hipcr;
OUTPUT OUT=starwars MEAN=avbmi av98 av99 STD=stbmi stah98 stah99 N=nbmi n98 n99 ;
run;

/*creates means ,standard deviation and no of obs and puts them in dataset called starwars which has only one observation*/


data _null_;
set starwars;
call symput("bmibar",avbmi); /*creates macro var bmibar that has the value of avbmi*/
call symput("a98bar",av98);
call symput("a99bar",av99);
call symput("s98",stah98);
call symput("s99",stah99);
call symput("sbmi",stbmi);
run;

%put mean of bmi is &bmibar;
%put mean of ah98 is &a98bar;
%put mean of ah99 is &a99bar;

data original;
set original;
ceterbmi=avbmi-&bmibar; /*centers bmi*/
bmisd=ahbmi/&sbmi; /*creates variable to do regression with each unit increment of standard deviation */
run;

/**************Alternate way***************************/
/**************Standardized Coefficients***************************/

proc reg;
model dependent= independent1 independent2 independent3/stb;
run;

This gives standardized estimates i.e. when all variables in the
models (including dependent variable) are standardized to zero
mean and unit variance. Each coefficient indicates the number
of SD change in the dependent variable with a SD change in the
independent variable holding constant all other variables constant.
This is useful to compare the relative importance of independent
variables independent of the scales.

Productivity tools for sas

2007-03-14T11:14:00.000-07:00

Comparison of two SAS programs: Sometimes similar looking files lead to different results. Following programs help finding the difference.

Beyond Compare
ExamDiffPro

Extracting columns of results from output e.g.

without painful editing as

TextPad
xemacs for windows

Prevent code from executing

2007-03-08T10:39:00.000-08:00

My sas programs have a lot of code which I need intermittently but I don't need it to run everytime the program executes. Since, I need the code from time to time, I am reluctant to delete it.

There are various ways to prevent code from executing:

/*comment out using asterisk slash*/ pieces of code enclosed by asterisk slash are treated as comments by sas and ignored.
*comment using asterisk; pieces of code enclosed by asterisk and ';' are treated as comments and ignored by sas.
%macro junk; enclose code as a macro; %mend; pieces of code enclosed as macro are ignored. This is extremely useful since method1 and 2 only work if there are no other comments in between. Junk can be replaced with any word.

e.g.

/* this is sample code */
proc means;
table age;
run;

/* this is second proc */
proc univariate;
var age;
run;

If this code needs to be ignored as a whole, method 1 would generate a error and method 2 will be tedious. This kind of code can be prevented from executing as follows:

%macro abcdg;
/* this is sample code */
proc means;
table age;
run;

/* this is second proc */
proc univariate;
var age;
run;

%mend;

Find missing information

2007-03-05T06:35:00.001-08:00

Missing information bugs me! Missing missing information bugs me more!!

However, there is an easy way to find variables with missing information. This method can be used for both continuous and categorical variables. Try the following after changing the keywords in color with your data specific names.

proc means data=trott1 NMISS N ;
var sbp dbp age smoking alcohol;
run;

More detailed information is available here.

Using Proc Genmod for logistic, poisson and log binomial regression

2007-01-07T04:32:00.000-08:00

PROC GENMOD is a procedure for fitting generalized linear models. This procedure is flexible and offers various advantages.

Indicator variables do not have to be constructed in advance because it uses a class statement for specifying categorical (classification) variables.
Interactions can be fitted by specified by using asterisk, for example, batch*gender.
In some procedures, variables necessarily have to be numerical. However, in proc genmod, the variables (both outcome and explanatory) can be character.
Proc genmod reports log likelihood ratio for each variable in the model.
Because of the generalized nature, different models can be fitted with one procedure.

data file11;
/* generate data using random numbers. details here*/
DO MINUTE=0 TO 1000 BY 1;
X=UNIFORM (0);
X1=UNIFORM (15452);
X2=UNIFORM (29561);
OUTPUT ;
END;
run;
data filea;
set file11;
if x>0.5 then gender=1;
if x<=0.5 then gender=0; if x1>0.8 then emmig=1;
if x1<=0.8 then emmig=0; if x1>0.8 then emmig1='yes';
if x1<=0.8 then emmig1='no'; if x2>0.4 then cat=1;
if x2<=0.4 then cat=0;  id=_n_; /* following creates a compressed/collapsed dataset (fam8)
with the same information as original dataset*/
if cat=0 then do;
if gender=1 and emmig=1 then index=1;
if gender=0 and emmig=1 then index=2;
if gender=1 and emmig=0 then index=3;
if gender=0 and emmig=0 then index=4;
end;
else if cat=1 then do;
if gender=1 and emmig=1 then index=5;
if gender=0 and emmig=1 then index=6;
if gender=1 and emmig=0 then index=7;
if gender=0 and emmig=0 then index=8;
end;
PROC MEANS DATA=filea NWAY NOPRINT ;
CLASS index gender emmig cat emmig1;
VAR index ;
OUTPUT OUT=fam8 SUM=number;
RUN;

data file;
set fam8;
drop _type_ number;
numb=_freq_;
id=_n_;

/* Calculate odds ratio using logistic regression */
proc genmod data=file descending ;
class cat ;
freq numb; /* method to analyze aggregate data */
model emmig1 = cat/ dist=binomial link=logit ;
estimate 'Beta' cat 1 -1/ exp;
run;

/* Calculate risk ratio using log binomial regression */
proc genmod data=filea descending ;
class cat ;
model emmig = cat/ dist=binomial link=log ;
estimate 'Beta' cat 1 -1/ exp;
run;

/* Calculate risk ratio using Poisson Regression with Robust Error Variance*/
proc genmod data=filea ;
class cat id;
model emmig = cat/ dist=poisson link=log ;
repeated subject = id/ type = unstr;
estimate 'Beta' cat 1 -1/ exp;
run;

/*logic of using risk ratio vs odds ratio and details of log-binomial and
poisson regression is here*/

Finding the number of times a threshold has exceeded cutoff

2006-12-30T08:57:00.000-08:00

I was approached by a student who had a time series data (120 individual animals observed at 250 time points). She want to find the following:

1)How man times an animal's outcome has been above a threshold for 5, 6,7 ... j consecutive times?

2) An animal can be above a threshold for 'j' consecutive times and then go below it for 'n' consecutive time points and go above it for 'k' consecutive time points . I would prefer this pattern to be counted distinctly and also as only once.

/*this solution was offered through SAS-L */
/*Generate test data */
data test;
do animal = 1 to 120;
do time = 1 to 250;
outcome = floor(10*ranuni(123) );
output;
end;
end;
run;

/*First step is to create a variable indicating whether the threshold (3, for example) is exceeded*/

data step1 /*/ view=step1/*;
set test;
over = (outcome > 3);
run;

/* Next, reduce to one observation for each series of consecutives */

data step2(drop = outcome) /* /view=step2 */;
do consecutive = -1 by -1 until (last.over);
set threshold;
by animal over notsorted;
end;
run;

proc freq data=step2;
tables consecutive / nopercent;
where over;
run;

Finding the largest observation

2006-12-30T08:29:00.000-08:00

There might be many ways to do this. A simple way would be to as follows:

data new;
Infile '/udd/n2man/epimarks/classmarks.txt';
input roll 4. ExamDate MMDDYY10. @17 MidFinal $2. Marks 4.;
obsno=_n_;
/* creates a new variable obsno whose value is the same as observation number in the dataset */

proc univariate;
var marks;
run;

/* see the observation number for the largest variable(s) in the output. Run the program again with following added*/

proc print;
where obsno=15; /* 15 is the obsno from the output of proc univariate */

A sophisticated way

data new2;
Infile '/udd/n2man/epimarks/classmarks.txt';
input roll 4. ExamDate MMDDYY10. @17 MidFinal $2. Marks 4.;

proc sort data =new2;
by descending marks;

data _null_ ; /* null datasets only exist for the particular datastep where they are called */
set new2 ;
If _n_=1 then call symput("IDNumber",Roll);
/* this creates a macro variable IDNumber whose value is the roll of the first observation which is the one with largest marks because of sorting*/
else stop;

proc print data=new2;
where Roll="&IDnumber";
format ExamDate WORDDATE18.;
title "Student &IDNumber Had the highest marks ";
run;

Redirecting sas log and output files

2006-12-30T08:25:00.000-08:00

When sas program mal or mal.sas is run on unix, it would produce output files sql.log and sql.lst. It is possible to redirect these files as follows:

sas mal.sas -log mallog1 -print mresults &

where mallog1 and mresults could be any valid Unix file names. The ampersand (&) ensures that the sas program is run in the background (see earlier posts).

Running SAS jobs in background

2006-12-25T21:36:00.000-08:00

If you want to continue editing programs while SAS jobs run in the background, you can do that by placing an ampersand ("&") after program name. For example:

   sas myprog &

Running SAS on UNIX in Interactive mode

2006-12-25T20:45:00.000-08:00

If you have got used to running SAS on Windows and now you are stuck with using SAS on UNIX, accept my sympathies ;)

Anyways, you can run SAS on UNIX with X11 tunelling. The X-Windows interface is similar in appearance and functionality to SAS for Windows.

To do this you need two programs:

Putty or Secure CRT to connect to the UNIX server from Windows PC.
An X-Windows server running on your computer.

Installing X-Windows server is easy.
Download the program from here. Unzip this folder and run setup. You will get a window titled MicroImages X Server.

To run SAS, you will need to configure it to allow XWindows "tunneling."

In SecureCRT, after starting click on Properties.

In the Properties window click on X11 tab and check Forward X11 Packets.

Connect to the server as usual and once connected type
sas

After a pause of 4 seconds or so you would be able to see windows similar to what you see in Windows in the MicroImages X Server window.

The response of SAS in this environment can be a little slow.

Convert character format to numeric format

2006-12-25T18:19:00.000-08:00

In the previous post, if you use the substr function to tease out a number e.g. roll, the number is in character format. Mathematical functions can not be performed on character variables.

To convert a variable from character to number format, there are following ways:

rollno=roll*1;

rollno=input(roll,4.0)

Parsing names using scan and substr functions

2006-12-23T14:27:00.000-08:00

Most of the times the raw data is received which requires processing before analysis. Recently, I assisted a researcher in analyzing this school data which was in the following format. The names in (brackets) is the maiden name. We have to find the everyone's name before marriage among other things.

DATA dataset ;
INPUT obs 1 name $ 2-45 idno $ 46-58 state $ 59 ;
LENGTH obs 3 ;
cards;
6RED MURAR: DID NOT COMPLETE 09/2251/0018Â 2
7MARLA (WOHL) RANDH 08/3559/0074Â 3
8RED (KRAKAS) MOHLK 07/1452/0025Â 6
9MNIT K. RAJHU 02/1758/0069Â 1
; RUN ;

/*notice the patterns which will be used to identify different variables*/
data grit;
set dataset;
roll1= SCAN(idno,-1,'/');
roll= substr(roll1,1,4);
name1= SCAN(name,1,':');
first1= SCAN(name1,1,' ');
len_temp=indexc(name,'(');
if len_temp gt 0 then do;
last1=scan(name,-1,'(');
last2=scan(last1,1,')');
last3=compress(last2," ");
maiden=trim(first1)||" "||last3;
end;
if maiden=" " then maiden=trim(name1);
proc print;
var roll name1 first1 last3 maiden;
run;

/*Pitfalls detailed here */

/* A very useful program is available here */

Elegant plotting without ODS

2006-12-21T08:03:00.000-08:00

One file for all the graphs

If you want to plot all the graphs in one file without using ODS, you should add the following line at the beginning of your program.

filename mygraph 'usrroom.ps';
goptions reset=global DEVICE=pscolor gsfmode=append gsfname=mygraph;

**this would generate 3.ext - a postscript file that contains the boxplots ****;
**note that ‘device=PSLEPSFC’ and filename mygraph 'usrroom.eps' ; would result in a eps file with colors**;
**note that ‘device=pscolor’ would result in a graph with colors**;

**if you do not use gsfname option, then sas would name the file by default sasgraph.ps. **;

This would generate a single file postscript(ps) file ‘usrroom.ps’ with all your plots .

inserting ps files in word

Convert ps files to enhanced post script files for inserting into word documents in unix/hsph by typing

convert file.ps file.eps

making a pdf file from ps
ps files can be made into a pdf file in unix/hsph by typing
ps2pdf usrroom.ps graphs.pdf

If you run the proc goptions or other advanced graphics would this line you would get the following prompt
"No device name has been given--please enter device name:"

If you enter ‘ps’ or ‘pscolor’, you would get a postscript file.

Separate file for each graph

If you want to make separate files for each graph it can be done by using goptions before each plot as shown in the following code

gif files

If you want to plot separate file for each graph, you should add the following line at the beginning of your program.

filename mygraph '/usr2/users/student/username/graphs';
goptions reset=global DEVICE=html gsfmode=append gsfname=mygraph;

This would generate gif files in files in folder /graphs. gif files are easy to insert in word .

pdf files

If you want to plot separate pdf file for each graph, you can add the following line at the beginning of each proc gplot command.

filename grafout '1.pdf';
goptions device=pdf gsfname=grafout gaccess=sasgastd gsfmode=replace;
symbol value=dot color=purple;
proc gplot data=weights;
plot bwt*age;
run;
**this would generate 1.pdf file that contains the plots of birth weight with age****;
**However, SAS would automatically map colors to greyscale in this file**

A good guide on plotting (pdf) is available here.

Using WinSCP to run SAS programs

2006-12-21T08:01:00.000-08:00

In the screenshot of WinSCP, Novell files are on the left, Unix files are on the right. You can drag and drop to transfer files between systems.

Create a new SAS program file using WinSCP.

From the top menu bar, choose Files/Edit new file.
Name it your_pgm.sas. (NB: No spaces allowed in Unix file names.)

Edit an existing file using WinSCP.

Right click on a file. You will see some preset Unix commands (Edit, Copy, etc.)
Choose Edit to edit the file

3 ways to execute SAS as a Unix command through WinSCP

From the top menu bar, choose Commands/Terminal. Type sas your_pgm.sas
From the top menu bar, choose Options/Command line. In the command line box that appears at the bottom of the window, type sas your_pgm.sas
Set up a custom command, as described below

Add a SAS command to the custom menu.

Right click anywhere on the Unix side of the window.
Choose Custom Commands/Customize/Add
Enter SAS in the Description box.
Enter sas “!” in the Custom Command box
The command SAS will be added to the Custom Commands

Some useful commands at HSPH. (If you set them up as Custom Commands, you won’t have to remember them.)

Description (becomes the menu choice)	Command (this format for custom commands uses “!” to refer to the file you clicked)
SAS [Version 9]	sas “!”
SAS8 [In case you need V8]	sas8 “!”
mpage [multi-page print in the Microlab to printer L17a (2 pages on one side)]*	mpage –Pmlab58 -2 “!”
lp [stream print to printer LL17a in Microlab]*	lp “!”
SAS Sudaan	sas8 -path /usr/local2/sudaan90/sas-callable "!"

*For best results, set “options linesize=78;” in your SAS program.

Run the SAS program using the custom SAS command

Click the SAS program file
Choose SAS from Custom Commands
Select SAS your_pgm.sas and execute

Managing your SAS session

Use Edit to examine .log and .lst files
Use mpage to print .sas, .log, and .lst files
If SAS is installed on your PC, consider using it to write your programs using the enhanced SAS editor. Drag and drop your program from the PC side of the window to the Unix side. (Be sure to save the editor contents before you do so). Execute the program using one of the methods, above. Drag and drop your .log file back to PC SAS for viewing in color.

This page was created by Amy Cohen.

Reading log files

2006-12-21T07:59:00.000-08:00

Sometimes SAS error log would show the line number. Simple text editors like pico and text readers such as less or more do not show the line number.
We can use nl (number line) command in such situation. If the file is big, to make the file the readable through scrolling, pipe can be used.
This would be the output.

The default behavior of nl is to skip blank lines, as shown above. The treatment of blank lines can be modified with the -b switch. Some -b options are -ba (number all lines), -bt (number only text lines -- the default behavior)

Running SAS on Unix

2006-12-21T07:55:00.000-08:00

The format of the command to run SAS is:

hsph% sas filename

SAS would print the log file in filename.log and the results in filename.lst.

Following is a sample sas program in unix is

options pagesize=59 ls=64;
libname new '.'; ** sets the library to the directory in which the program resides**
data ex;
infile '/usr2/users/student/mkaushik/la/tumor' TRUNCOVER;
/** reads the .txt file. Note the way file is referenced using backslash.**/

input id treat y;
proc print;
run;
proc genmod ;
class treat;
model y=treat/dist=binomial link=logit;
ESTIMATE 'thiopeta vs. Placebo' treat 1 -1 / e exp;
run;

This program can be written using text editors such as pico, emacs or vi. Alternatively, it can be written in notepad in windows and then transferred to UNIX account using WinSCP.

You can download tumor from here and transfer the file to your UNIX account.

Aggregate analysis in SAS

2006-12-21T07:22:00.000-08:00

If there are aggregate data rather than individual data, analysis in SAS is less intuitive compared to STATA.

/* the aim of this analysis is to assess if the proportion of infection is changing across different years */

data xtrct;
input yr inf mech upgr other;/*infection has number of infection patients */

cards;
2000 12 7 0 40
2001 10 7 1 52
2002 34 13 4 24
2003 30 27 6 21
;

/* We need to convert this wide data set into long dataset*/
data trite;
set xtrct;
ninf=mech+upgr+other; /*total number of non infection patients */
ARRAY a(1:2) inf ninf;
DO t = 1 to 2 ;
num= a(t) ;
indicinf=t; /*indicator if num represents infections or non infections */
OUTPUT ;
END ;

proc print;
run;

/* The data table should be as follows. Note num corresponds to number of cases with infection and without infection.*/

Obs	yr	inf	mech	upgr	other	ninf	inf	infi	num
1	2000	12	7	0	40	47	12	1	12
2	2000	12	7	0	40	47	12	2	47
3	2001	10	7	1	52	60	10	1	10
4	2001	10	7	1	52	60	10	2	60
5	2002	34	13	4	24	41	34	1	34
6	2002	34	13	4	24	41	34	2	41
7	2003	30	27	6	21	54	30	1	30
8	2003	30	27	6	21	54	30	2	54

/* To assess the proportion of patients with infections is same over the 3 year period */

proc freq order=data;
title3 '(Two one-way tables followed by a 2x2 table)';
table yr*indicinf / chisq;
weight num;
run;

******************************************************************************
The output is here.

Create a format library from SAS dataset

2006-12-21T07:03:00.000-08:00

To create SAS format library, SAS requires a datset in following format and with some of these variables.

To create a format or informat from a dataset using the CNTLIN option of PROC FORMAT, you need atleast the following variables in the

FMTNAME – Contains name of format or informat to be created
START – The value of the variable that we want to be formatted
LABEL - The variable containing the format value we want displayed..

There are many optional variables (see online documentation for complete list), the most useful of which is

TYPE - character variable that indicates the type of format. Possible values are
C character format
I numeric informat
J character informat
N numeric format (excluding pictures)
P picture format

The CNTLIN dataset must have these characteristics:

* For both numeric and character formats, the data set must contain the variables FMTNAME, START, and LABEL. The remaining variables are not required.
* If you are creating a character format, a character informat, or a PICTURE statement format, you must specify a TYPE variable with the value that indicates the type of informat or format you are creating.
* If range values are to be noninclusive, the variables SEXCL and EEXCL must each have a value of Y. Inclusion is the default.

You can create more than one format from an input control data set if the observations for each format are grouped together.

Following is the way to create a format from a dataset:

/*************************************SAS CODE *************************************/
options nodate pageno=1 linesize=80 pagesize=60;
libname manas '/udd/n2man/tkk';
libname library '/udd/n2man/'; /* creates a format library in the location /udd/n2man/ */
/* READ THE ORIGINAL DATASET */
/* VARIABLE DIAGCO CONTAINS THE VALUES TO WHICH A FORMAT (IN VARIABLE RISK) SHOULD BE ASSIGNED */
data dummy;
infile '/udd/n2man/tkk/surgpro.txt' missover;
input diagco $ risk desc $;
KEEP diagco risk;
length diagco $8;
run;
proc sort data=sample; by risk diagco;
run;

data new;
set dummy;
start =diagco; /* CREATE NEW VARIABLES NEEDED TO MAKE THE FORMAT */
label=risk; /* CREATE NEW VARIABLES NEEDED TO MAKE THE FORMAT */
/* to make a format from a range of values include the following */
/* end= endrange; *here values from start-endrange will be assigned */
/* a value */
fmtname='$random';
/* CREATES THE FORMAT NAME. IN THIS CASE ALL VARIABLES NEED TO HAVE THE SAME FORMAT */
/* format names can also be in the dataset but in this dataset they had to be created */

PROC SORT DATA = new OUT = FMT NODUPKEY;
BY START;
/* removes duplicate values of start that should only one label. Having two labels for the same value can be dangerous */

proc sql;
create table fmt as
select distinct start, label, fmtname
from new;
quit;
data finalfmt;
set fmt;
/**** This step converts the dataset fmt to format library */
PROC FORMAT LIB=library fmtlib CNTLIN=finalFMT;
run;

/*********************************************************************************/

I have used/copied some segments from http://www.uchsc.edu/coho/SAS_Uers/The%20Many%20Ways%20to%20use%20Formats.doc.

Reading CSV files in SAS on UNIX

2006-12-19T13:05:00.000-08:00

Each individual Excell work sheet can be converted into CSV or tab delimited files for being read in SAS.

CSV files are in following format (note that commas separate different data points). This format is especially suitable if you have missing data.

12,10,34
6,9,0
78,89,46
2,3,2

To read csv files, use one of the following statements

data new;
infile '/usr2/users/student/mkaushik/shh/data1.csv' dsd;/* change the location of the file */
INPUT rake note pgg ; /* these are variable names of your choice*/
run;

OR

data new;
infile '/usr2/users/student/mkaushik/shh/data1.csv' delimiter=','; /* change the location of the file */
INPUT rake note pgg ; /* these are variable names of your choice*/
run;

Table 1 Analysis Macro

2006-12-19T12:59:00.000-08:00

I found this great macro for Table 1 analysis. This macro could be polished further but it works as is and is quite helpful.

Channing Labs have their own macro for Table 1 analysis but it adjusts for age which is not required in all analysis.

Making Indicator(dummy) variables

2006-12-18T19:20:00.000-08:00

Following code can be used to make indicator variables.

Method 1
if state1 ne 20 & status ne 4 & sex ne .;
else if state1 in (1,3,4,19) then reg=1; /* Bread basket */
else if state1 in (8,16) then reg=2; /* maharashtraAP */
else if state1 in (9,10,12,13,17) then reg=3; /* bimaru states */
else if state1 in (5,11,21) then reg=5; /* north eastWB */
else if state1 in (2) then reg=6; /* Delhi */
else if state1 =. then reg=7; /* Missing */

ARRAY dummys {*} 3. reg_1 - reg_7;

DO i=1 TO 7;
dummys(i) = 0;
END;
dummys( reg ) = 1;

This is similar to the method given here.

Method 2
/* if variable values start from some arbitrary level */
/* following makes indicator for years */
/* Here year values can be from 1988 to 2000 */

ARRAY dummyb {1988:2000} 3. bat_1 - bat_13;

DO i=1988 TO 2000;
dummyb(i) = 0;
dummyb(i) =(year=i);
END;

Macros for creating indicator (dummy) variables are available from 1.
and SAS-L archives
Thanks for the comments Amy!

Exporting a SAS dataset to Microsoft Excel

2006-12-18T19:16:00.000-08:00

There are atleast two ways to export a SAS dataset to Microsoft Excel.

Proc Export

PROC EXPORT DATA=datasetname
OUTFILE="/usr2/users/student/mkaushik/ names9900.csv"
DBMS=csv
REPLACE;
delimiter=','; */
RUN;

The program (say atlantis) that includes this, should be submitted with the -noterminal option to i.e.:

sas atlantis -noterminal

ODS
ODS html FILE='/usr2/users/student/mkaushik/AIIMS/becker/try2.xls';
PROC PRINT DATA =&dataset NOOBS;
RUN;
ODS html CLOSE;

Use it when a few observations need to be extracted and sent it for review, additional data entry, data confirmation.

Calculating percentiles in SAS

2006-12-18T19:15:00.000-08:00

Following code can be used to find the percentiles.

proc univariate data=food noprint;
var omega sat;
output out=pctl pctlpts=20 40 60 80 pctlpre=omega sat;
run;
proc print data=pctl;
run;

Output would be in the following format
omega20 omega40 omega60 omega80 sat20 sat40 sat60 sat80

(from http://www.ats.ucla.edu/STAT/sas/faq/percentiles.htm)

Directing SAS output to a pdf file

2006-12-18T19:11:00.000-08:00

ods pdf file= "/usr2/users/student/mkaushik/bio213/testd.pdf";
*** would direct all the output to testd.pdf file*

*ods pdf file ="%sysfunc
(reverse(%sysfunc(substr(%sysfunc(reverse(%sysfunc(reverse(%scan(%sysfunc(reverse(%sysfunc(getoption(sysin)))),1,/))))),5)))).pdf";
goptions reset=global device=gif ftitle=swissb ftext=swissb htitle=2 htext=2;

data weights;
infile '/usr2/users/student/mkaushik/ne/lbw1';
****change the above line to reflect where you have saved lbw.dat**;

input id low age lwt race smoke ptl ht ui ftv bwt;
run;

proc means;
var age lwt bwt;
run;

proc univariate plot;
var age lwt bwt;
run;

proc gplot;
plot bwt*lwt;
proc corr pearson spearman;
var bwt lwt;

proc gplot data =results;
plot bwt*lwt= '.' pred1*lwt='*' lowerm*lwt='L' upperm*lwt='U'/ overlay;
run;

quit;
ods pdf close;

Making nice plots & name ods html files after SAS files

2006-12-18T18:45:00.000-08:00

Output Delivery System (ODS) is a preferred way of making nice tables and graphs.
I recommend using following statement which automatically directs the ODS output to a file with the same name and in the same directory as the program. The following code makes a html file. HTML files can be viewed in UNIX (using lynx) as well as Windows , open quickly and cut pasted easily in Document and Spreadsheet programs.

(remove spaces from following to make this work)
ods html file ="%sysfunc
(reverse(%sysfunc(substr(%sysfunc(reverse(%sysfunc(reverse
(%scan(%sysfunc(reverse(%sysfunc(getoption(sysin)))),1,/))))),5)))).html"
STYLE=MINIMAL;

gpath="/usr2/users/student/mkaushik/web/";

data=
.
.
proc anova;
.
run;

ods html close;

For an html file with frames, you can enter the following code spanning the proc statements

ods html body="/usr2/users/student/mkaushik/web/mixed_output.html"
contents="/usr2/users/student/mkaushik/web/mixed_contents.html"
frame="/usr2/users/student/mkaushik/web/mixed_frame.html";

proc
.
.
run;

ods html close;

In this ODS statement there are three files:

1. BODY which specifies the file to contain the output from the PROC.
2. CONTENTS which specifies the file to contain the table of contents.
3. FRAME which specifies the file to contain reference to the table of contents and the output from the PROC.

Reading compressed data

2006-12-18T18:40:00.000-08:00

Sometimes large datasets are available as ASCII files. However, they are too large to be put on HSPH unix system because of limited disk space. These files can be compressed and put on Unix system and uncompressed ‘on the go’. While 7Z and gzip compressed files can be read as well, the simple compressed files are most easily read. There are two ways to compress a file,

compress filename &

Replace "filename" with the name of the data file you wish to compress. This creates a new file with the extension ".Z". For example, if you compress a file called "unit.dat," a compressed file called "unit.dat.Z" would be created, replacing the original file (unit.dat).

gzip filename &

This creates a new file with the extension ".gz". For example, if you compress a file called "unit.dat," a compressed file called "unit.dat.gz" would be created, replacing the original file (unit.dat).

To read the .Z compressed file into SAS without having to uncompress it beforehand, you should add the following to your program:

FILENAME pipedata PIPE 'zcat /usr2/users/student/mkaushik/ne/unit.z' ; /* pipedata is user choosen word */
DATA weights;
INFILE pipedata ; /* This pipedata is user choosen word */
input cogscore race hosmokin htn hochf age female charlson hrtdis pf36 mh36 pn36 enr36 sf36 nyha;
run;

/* Zcat decompresses the data of the input file, and writes the result on the standard output. This data is piped into SAS for being read in. */

Gzipped files can also be read in using following commands.

FILENAME pipedata1 PIPE 'gzcat /usr2/users/student/mkaushik/ne/page.gz' ; /* pipedata1 is user choosen word */
DATA weights;
INFILE pipedata1;
input cogscore race hosmokin htn hochf age female charlson hrtdis pf36 mh36 pn36 enr36 sf36 nyha;
run;
/* This would only work on HSPH system and not on Channing system */

FILENAME pipedata1 PIPE 'gzip –dc /usr2/users/student/mkaushik/ne/page.gz' ; /* pipedata1 is user choosen word */
DATA weights;
INFILE pipedata1;
input cogscore race hosmokin htn hochf age female charlson hrtdis pf36 mh36 pn36 enr36 sf36 nyha;
run;
/* This would work on both HSPH and Channing system */

Reading Tab delimited files into SAS

2006-12-18T18:37:00.000-08:00

Tab delimited files are in following format (note that the tabs separate different data points). Missing data in this format can cause error.

12 10 34

6 6 9

78 89 2

45 84 84

To read tab delimited files, use one of the following statements

INFILE "'/usr2/users/student/mkaushik/shh/cars6.dat" DELIMITER='09'x; /* change the location of the file */
INPUT rake note pgg ; /* these are variable names of your choice*/
run;

Reading CSV file

2006-12-18T18:34:00.000-08:00

12,10,34
6,9,0
78,89,46
2,3,2

To read csv files, use one of the following statements

infile '/usr2/users/student/mkaushik/shh/data1.csv' dsd;/* change the location of the file */
INPUT rake note pgg ; /* these are variable names of your choice*/

run;

infile '/usr2/users/student/mkaushik/shh/data1.csv' delimiter=','; /* change the location of the file */
INPUT rake note pgg ; /* these are variable names of your choice*/
run;