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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.