BookmarkSubscribeRSS Feed
BigPete
Obsidian | Level 7

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%

 

?

2 REPLIES 2
Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 1006 views
  • 0 likes
  • 3 in conversation