How to create a report by combining results from simple PROC SUMMARY

[bncoxuk]

I have a simple but tricky work. Basically, individual summary tables for each of the classification variable have been produced as below in SAS:

sex   source     pct

F     offline    0.288

F     online     0.113

M     offline    0.427

M     online     0.172

married   source     pct 

C         offline    0.127

C         online     0.232

S         offline    0.183

S         online     0.134

X         offline    0.038

X         online     0.286

There are over 20 variables like this (e.g., sex, married), and each variable has unknown levels (e.g., 'F', 'M'). Now I need to combine all these small tables and make the final Excel report like below:

               offline   online

sex       F     0.288    0.113

          M     0.427    0.172

married   C     0.127    0.232

          S     0.183    0.134

          X     0.038    0.286

...

...

...

Many thanks for your help.

[ballardw]

My first take would probably be to replace a SUMMARY step with PROC TABULATE. Since proc summary doesn't actually do percents (assuming that's what your variable PCT is) how did you get them? And does your pct of 0.127 mean 12.7%?

Assuming your OFFLINE and ONLINE variables are coded as 0 or 1 for NO/ YES you might try:

proc tabulate data=<your dataset name used for summary>;

     class sex married <other classification variables>; /* warning this works best if NONE of the records are missing class variables*/

     var online offline;

    table sex married <other classification variables>,

          (online offline) * mean=""*f=f5.3;

run;

[Reeza]

Adding to BallardW comments and code, if observations have a missing value for any of the variables included in the class statement that observation is excluded for ALL summaries.

Also, since it is percent you can use a percent format to get a cleaner report.

proc format;                          
   picture tabpct low-high='000,009.000%';  
run;

proc tabulate data=<your dataset name used for summary>;

     class sex married <other classification variables>/missing; /* include missing observations*/

     var online offline;

    table sex married <other classification variables>,

          (online offline) * mean=""*f=tabpct.;

run;

[Ksharp · Accepted solution]

Assuming you only have two datasets , and the name of the first variable in the first importing datasets(i.e. sex) is SEX.

data  sex;
input sex $  source  $   pct ;
cards;
F     offline    0.288
F     online     0.113
M     offline    0.427
M     online     0.172 
;
run;
data married;
input married $  source  $   pct ;
cards;
C         offline    0.127
C         online     0.232
S         offline    0.183
S         online     0.134
X         offline    0.038
X         online     0.286 
;
run;




data list;
input tname $20. ;
cards;
work.sex
work.married
;
run;
data _null_;
set list;
call execute('data '||strip(tname)||'(drop=dsid);set '||strip(tname)||';'||'dsid=open("'||strip(tname)||'");name=varname(dsid,1);run;');
run;

data _null_;
 set list end=last;
if _n_ eq 1 then call execute('proc sql;create table want as ');
call execute('select * from '||strip(tname));
if not last then call execute('union all');
 else call execute(';quit;');
run;

ods listing close;
ods tagsets.excelxp file='c:\temp\x.xls' style=sasweb;
proc tabulate data=want;
class name sex source ;
var pct ;
table name=''*sex='',source=''*pct=''*sum=''*format=best8.3;
run;
ods tagsets.excelxp close;
ods listing;

Ksharp