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.
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
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.