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

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

BigPete — Thu, 10 Aug 2017 23:45:28 GMT

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%

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

Jagadishkatam — Fri, 11 Aug 2017 02:24:19 GMT

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;

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

Ksharp — Fri, 11 Aug 2017 14:41:28 GMT

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;