Summarizing Multiple categorical response from multiple column

[Akter]

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

[Kurt_Bremser]

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

[Akter]

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.

[Patrick]

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.

[Ksharp · Accepted solution]

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;