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!
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;
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;
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.