The SAS Output Delivery System and reporting techniques

Mixing in subcolumns displaying row % and column % under ACROSS columns via PROC REPORT?

Reply
Contributor
Posts: 26

Mixing in subcolumns displaying row % and column % under ACROSS columns via PROC REPORT?

If you look at my sample code, what if I want a column reflecting row % for "Yes" and "No" columns and another for column % WRT "N/A"?  I figured out how to compute the former but how would I compute the latter?

 

data qbdats;
input QBoption $ answer $;
cards;
sneak yes
sneak yes
sneak yes
sneak no
sneak no
sneak n/a
sneak n/a
sneak n/a
sneak n/a
pass yes
pass yes
pass yes
pass yes
pass no
pass no
pass no
pass no
pass no
pass n/a
pass n/a
pass n/a
;
run;


PROC REPORT DATA = qbdats nowd split='~'
	STYLE(REPORT)=[BACKGROUND=WHITE ASIS=ON just=l frame=box]
	;

COLUMN qboption (answer,(n pct));

DEFINE qboption	/ group "QB option"	ORDER=data style(column)=[just=l];
define answer / across "" order=data;
define n / f=comma.;
define pct / computed f=percent8.2;

   /* Sum total number of responses to each question */
   compute before qboption;
      den = _c2_ + _c4_;
   endcomp;

   /* Calculate percentage */
   compute pct;
      _c3_ = _c2_ / den;
      _c5_ = _c4_ / den;
   endcomp;
RUN;

 

Other words, how do I change from this

  yes no n/a
QB option n pct n pct n pct
sneak 3 60.00% 2 40.00% 4 .
pass 4 44.44% 5 55.56% 3 .

 

to this

  yes no n/a
QB option n rowpct n rowpct n colpct
sneak 3 60.00% 2 40.00% 4 57.14%
pass 4 44.44% 5 55.56% 3 42.86%

 

?

Trusted Advisor
Posts: 1,128

Re: Mixing in subcolumns displaying row % and column % under ACROSS columns via PROC REPORT?

Not sure with proc report but you could try the below code if it helps

 

data qbdats;
input QBoption $ answer $;
cards;
sneak yes
sneak yes
sneak yes
sneak no
sneak no
sneak n/a
sneak n/a
sneak n/a
sneak n/a
pass yes
pass yes
pass yes
pass yes
pass no
pass no
pass no
pass no
pass no
pass n/a
pass n/a
pass n/a
;
run;

ods output CrossTabFreqs=CrossTabFreqs1;

proc freq data=qbdats ;
where answer^='n/a';
table QBoption*answer /out=chk;
run; 

ods output close;

ods output CrossTabFreqs=CrossTabFreqs2;

proc freq data=qbdats ;
where answer='n/a';
table QBoption*answer /out=chk;
run; 

ods output close;

proc sort data=CrossTabFreqs1;
by qboption;
where qboption ne '';
run;

proc transpose data=CrossTabFreqs1 out=want1 prefix=n_;
where answer ne '' ;
by QBoption;
var frequency;
id answer;
run;

proc transpose data=CrossTabFreqs1 out=want2 prefix=row_;
where answer ne '' ;
by QBoption;
var rowpercent;
id answer;
run;

proc sort data=CrossTabFreqs2;
by qboption;
where qboption ne '';
run;

proc transpose data=CrossTabFreqs2 out=want3 prefix=n_;
where answer ne '' ;
by QBoption;
var frequency;
id answer;
run;

proc transpose data=CrossTabFreqs2 out=want4;
where answer eq '' ;
by QBoption;
var percent;
run;

data all;
merge want1 want2 want3 want4;
by QBoption;
run;


PROC REPORT DATA = all nowd split='~' STYLE(REPORT)=[BACKGROUND=WHITE ASIS=ON just=l frame=box];

COLUMN qboption  ('no' n_no row_no) ('yes' n_yes row_yes) ('n_a' n_n_a col1);

DEFINE qboption	/ group "QB option"	ORDER=data style(column)=[just=l];
define n_n_a / order ;
define col1 / order ;
define n_no / order ;
define row_no / order ;
define n_yes / order ;
define row_yes / order ;
RUN;

 

 

Thanks,
Jag
Super User
Posts: 9,681

Re: Mixing in subcolumns displaying row % and column % under ACROSS columns via PROC REPORT?

data qbdats;
input QBoption $ answer $;
cards;
sneak yes
sneak yes
sneak yes
sneak no
sneak no
sneak n/a
sneak n/a
sneak n/a
sneak n/a
pass yes
pass yes
pass yes
pass yes
pass no
pass no
pass no
pass no
pass no
pass n/a
pass n/a
pass n/a
;
run;
proc sql;
create table want1 as
select QBoption,
 count(*) as yes_n label='n',
 calculated yes_n/(select count(*) from qbdats where QBoption=a.QBoption and  answer in ('yes' 'no')) 
 as yes_rowpct label='rowpct' format=percent7.2
 from qbdats as a
  where answer='yes'
   group by QBoption;

create table want2 as
select QBoption,
 count(*) as no_n label='n',
 calculated no_n/(select count(*) from qbdats where QBoption=a.QBoption and  answer in ('yes' 'no')) 
 as no_rowpct label='rowpct' format=percent7.2
 from qbdats as a
  where answer='no'
   group by QBoption;

create table want3 as
select QBoption,
 count(*) as na_n label='n',
 calculated na_n/(select count(*) from qbdats where  answer='n/a') 
 as na_rowpct label='colpct' format=percent7.2
 from qbdats as a
  where answer='n/a'
   group by QBoption;
quit;

data want;
 merge want1 want2 want3;
 by QBoption;
run;

proc report data=want nowd split='~';
column QBoption ('yes' yes_n yes_rowpct)
('no' no_n no_rowpct)
('n/a' na_n na_rowpct);
run;
Ask a Question
Discussion stats
  • 2 replies
  • 137 views
  • 0 likes
  • 3 in conversation