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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.