BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bncoxuk
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Super User

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 868 views
  • 6 likes
  • 4 in conversation