Thursday, April 19, 2007

Make results more presentable

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;