How to get the frequency of options by group

[lapetitemaman]

Hi everyone,

I have a data set with multiple options and I need to get the frequency count of each option (the total number of options A, B, C, etc. by ID). 

data example;

input ID option1 $ option2 $ option3 $;

datalines;

Item1 A D F 

Item1 A C E

Item1 B D F

Item1 C D E

Item2 A B D

Item2 A C F

Item2 C D F

Item3 A E

Item3 B D

Item3 C E

Item3 D E

;

data want;

ID A  B  C  D  E  F ;

datalines;

Item1 2 1 2 3 2 2

Item2 2 1 2 2 0 2

Item3 1 1 1 2 3 .

;

The data I would like can be a table like using proc sql. I can't get the proc sql to work. Thank you in advance!

[ballardw · Accepted solution]

First, test your data step before posting. It has errors because the values of ID are not numeric.

Second, paste code in either a text box or code box, opened on the forum with the "running man" or </> icons.

The message windows will reformat text and position dependent code often will not run correctly.

One way:

data example;
infile datalines truncover;
input ID $ option1 $ option2 $ option3 $;
datalines;
Item1 A D F 
Item1 A C E
Item1 B D F
Item1 C D E
Item2 A B D
Item2 A C F
Item2 C D F
Item3 A E
Item3 B D
Item3 C E
Item3 D E
;

data helper;
   set example;
   array o (*) option: ;
   do i= 1 to dim (o);
      opt= o[i];
      if not missing(opt) then output;
   end;
   keep id opt;
run;

proc freq data=helper noprint;
   tables id*opt/ out=counted (drop=percent);
run;

proc transpose data=counted out=want (drop=_name_ _label_);
   by id;
   id opt;
   var count;
run;

This is to create a data set. Getting a count for something that doesn't occur would be another step if you really want a 0.

If you just wanted a report for people to read perhaps:

proc tabulate data=helper;
   class id opt;
   table id,
         opt=''  * n=" "
         /misstext='0'
   ;
run;

[lapetitemaman]

Thank you and I will be more careful in the future.