Desktop productivity for business analysts and programmers

Create report with proc tabulate

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Create report with proc tabulate

I have a huge table, and tried to summarize as below:

TermCampusGenderid
2014WestFemale15
2014WestMale20
2015WestMale22
2015EastMale23
2015EASTFemale

25

2014EASTFemale26
2015EastMale54
2014EastMale54
2015WestUnreported58
2014EASTMale62
2015EastFemale85

  I want to create a report with that. I created with proc tabulate with most part. But i need difference part too. So i want to achieve a report as below:

                                                                                                                         Campus

                                                            East                                                                                                                                     West

                  2014        %Tot Enrol        2015     %Tot Enrl           Difference          %Tot Enrl                 2014        %Tot Enrol        2015     %Tot Enrl           Difference         %Tot Enrl

Gender            

Female             1                %33.3          2           %50                    1                        %100                        1             %50                      0           %0                      -1                 

Male                 2                %66.6          2            %50                   0                          %0                          1            %50                      1            %50                    0

Unreported       0                  %0              0             %0                   0                           %0                         0             %0                      1            %50                     1

All                    3               %100             4            %100                 1                           %100                     2             %100                  2           %100                     0                   %100                           

Is this report achievable? I used this code before but there is no differencecode,  in here?

proc tabulate data=M format=8.2 out= gender;

class TERM CAMPUS GENDER/MISSING;

table

      GENDER ALL, (CAMPUS ALL)*TERM*(n='#Enrl'*f=8.

pctn<GENDER ALL/*CAMPUS*TERM*/  ALL*GENDER   GENDER>

='% Tot Enrl') / rts=20;

run; 


Accepted Solutions
Solution
‎08-27-2015 10:43 AM
Grand Advisor
Posts: 9,567

Re: Create report with proc tabulate

Make a new variable Difference into your dataset.

Code: Program4.sas

data have;
infile cards expandtabs truncover;
input (TERM CAMPUS GENDER id ) (: $upcase20.);
cards;
2014 West Female 15
2014 West Male 20
2015 West Male 22
2015 East Male 23
2015 EAST Female 25
2014 EAST Female 26
2015 East Male 54
2014 East Male 54
2015 West Unreported 58
2014 EAST Male 62
2015 East Female 85
;
run;
proc freq data=have noprint;
table TERM*CAMPUS*GENDER/list nocum  out=temp(drop=percent);
run;
proc sort data=temp;by GENDER CAMPUS TERM;run;
data want;
set temp;
by GENDER CAMPUS;
output;
dif=dif(count);
if last.CAMPUS then do;
  TERM='Diff';count=dif;output;
end;
drop dif;
run;
proc tabulate data=want;
class TERM CAMPUS GENDER ;
var count ;
table
   GENDER ALL, CAMPUS*TERM*(count=' '*f=best8.*sum=' ' count=' '*colpctsum=' '
   ) / rts=20;


run;

View solution in original post


All Replies
Solution
‎08-27-2015 10:43 AM
Grand Advisor
Posts: 9,567

Re: Create report with proc tabulate

Make a new variable Difference into your dataset.

Code: Program4.sas

data have;
infile cards expandtabs truncover;
input (TERM CAMPUS GENDER id ) (: $upcase20.);
cards;
2014 West Female 15
2014 West Male 20
2015 West Male 22
2015 East Male 23
2015 EAST Female 25
2014 EAST Female 26
2015 East Male 54
2014 East Male 54
2015 West Unreported 58
2014 EAST Male 62
2015 East Female 85
;
run;
proc freq data=have noprint;
table TERM*CAMPUS*GENDER/list nocum  out=temp(drop=percent);
run;
proc sort data=temp;by GENDER CAMPUS TERM;run;
data want;
set temp;
by GENDER CAMPUS;
output;
dif=dif(count);
if last.CAMPUS then do;
  TERM='Diff';count=dif;output;
end;
drop dif;
run;
proc tabulate data=want;
class TERM CAMPUS GENDER ;
var count ;
table
   GENDER ALL, CAMPUS*TERM*(count=' '*f=best8.*sum=' ' count=' '*colpctsum=' '
   ) / rts=20;


run;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 296 views
  • 0 likes
  • 2 in conversation