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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.