Contributor
Posts: 29

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;
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]
;

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%

?

Posts: 1,147

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;
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 ;
run;

ods output close;

ods output CrossTabFreqs=CrossTabFreqs2;

proc freq data=qbdats ;
run;

ods output close;

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

proc transpose data=CrossTabFreqs1 out=want1 prefix=n_;
by QBoption;
var frequency;
run;

proc transpose data=CrossTabFreqs1 out=want2 prefix=row_;
by QBoption;
var rowpercent;
run;

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

proc transpose data=CrossTabFreqs2 out=want3 prefix=n_;
by QBoption;
var frequency;
run;

proc transpose data=CrossTabFreqs2 out=want4;
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: 10,761

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

``````data qbdats;
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
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
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
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;``````
Discussion stats
• 2 replies
• 207 views
• 0 likes
• 3 in conversation