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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.