Hello Dear Friends,
Could you pls help me counting number and % (Response and or Case) of multiple response from multiple columns. % Response and %cases can in be either in separate table or same table with number count column
Data have;
ID Gender resp_1 resp_2 resp_3 resp_4
1 M 1 2 3
2 F 2
3 M 1 2 3 4
4 F 3
5 M 2
6 M 1 3
Output want:
Respse N Response (%) Case (%)
1 3 25.0 50.0
2 4 33.3 66.7
3 4 33.3 66.7
4 1 8.3 16.7
I really appreciate your support.
Thanks in advance.
Regards, Akter
Data have;
infile cards truncover;
input ID Gender $ resp_1 resp_2 resp_3 resp_4;
cards;
1 M 1 2 3
2 F 2
3 M 1 2 3 4
4 F 3
5 M 2
6 M 1 3
;
data temp;
set have;
array x{*} resp_:;
do i=1 to dim(x);
if not missing(x{i}) then do;value=x{i};output;end;
end;
drop i resp_:;
run;
proc sql;
create table want as
select value as resp,count(distinct id) as n ,
calculated n/(select count(*) from temp) as per1 label='Response(%)' format=percent8.2,
calculated n/(select count(distinct id) from temp) as per2 label='Case(%)' format=percent8.2
from temp
group by value;
quit;
From where do you get those counts? response 2 has only three values, and response 3 just two.
Transpose your wide data to a long structure. This makes it much easier to then use SAS procs like Freq, Report, Means, Tabulate, SQL.... for what you're after.
data have;
infile datalines truncover;
input (ID Gender resp_1 resp_2 resp_3 resp_4) ($);
datalines;
1 M 1 2 3
2 F 2
3 M 1 2 3 4
4 F 3
5 M 2
6 M 1 3
;
proc transpose
data=have
out =have_long(drop=_name_ rename=(col1=response) where=(not missing(response)) )
;
by id gender;
var resp_:;
run;
And as a side note: Posting fully working SAS data step code to create the have table would be highly appreciated.
Data have;
infile cards truncover;
input ID Gender $ resp_1 resp_2 resp_3 resp_4;
cards;
1 M 1 2 3
2 F 2
3 M 1 2 3 4
4 F 3
5 M 2
6 M 1 3
;
data temp;
set have;
array x{*} resp_:;
do i=1 to dim(x);
if not missing(x{i}) then do;value=x{i};output;end;
end;
drop i resp_:;
run;
proc sql;
create table want as
select value as resp,count(distinct id) as n ,
calculated n/(select count(*) from temp) as per1 label='Response(%)' format=percent8.2,
calculated n/(select count(distinct id) from temp) as per2 label='Case(%)' format=percent8.2
from temp
group by value;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.