topic Re: Summarizing Multiple categorical response from multiple column in SAS Programming

Summarizing Multiple categorical response from multiple column

Akter — Sat, 24 Apr 2021 18:55:22 GMT

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

Re: Summarizing Multiple categorical response from multiple column

Kurt_Bremser — Sat, 24 Apr 2021 19:11:34 GMT

From where do you get those counts? response 2 has only three values, and response 3 just two.

Re: Summarizing Multiple categorical response from multiple column

Akter — Sun, 25 Apr 2021 02:25:45 GMT

Sorry for the confusion.
1, 2, 3 and 4 are different categorical values of the variables (column) Resp_1, Resp_2, Resp_3 and Resp_4. Resp_1, Resp_2, Resp_3 and Resp_4 are multiple response. I.e. The subject ID 1 responded multiple responses such as 1, 2 and 3 and subject ID 2 responded only 1 which is 2 in column Resp_1.
You can think of column value 1=orange, 2=Apple, 3=Graphs, 4=Blackberry. Each subject ID can make max 4 responses, which are written in 4 columns as Resp_1, Resp_2, Resp_3 and Resp_4. I would like to calculate how many respondents preferred 1=orange, 2=Apple, 3=Graphs, 4=Blackberry.

Re: Summarizing Multiple categorical response from multiple column

Patrick — Sun, 25 Apr 2021 02:45:23 GMT

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.

Re: Summarizing Multiple categorical response from multiple column

Ksharp — Sun, 25 Apr 2021 12:30:04 GMT

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;