Hi all!
I'm confused between to procedure that I have to do. I think first I have to transpose my dataset and then calculate the frequency by proc tabulate, but I have not idea how to do it.
My dataset is something like this, but a little bit more complicated:
(every row is independient)
Year1 | Year2 | Year3 | Gender |
1A | 1A | 1A | A |
3A | A | ||
2A | 3A | B | |
2A | 3A | 3A | B |
1A | 1A | A |
And I need create something like this:
Gender | Year | Var | Frequency |
A | Year1 | 1A | 2 |
A | Year1 | 2A | 0 |
A | Year1 | 3A | 1 |
A | Year2 | 1A | 1 |
A | Year2 | 2A | 0 |
A | Year2 | 3A | 0 |
A | Year3 | 1A | 2 |
A | Year3 | 2A | 0 |
A | Year3 | 3A | 0 |
B | Year1 | 1A | 0 |
B | Year1 | 2A | 1 |
B | Year1 | 3A | 0 |
B | Year2 | 1A | 0 |
B | Year2 | 2A | 1 |
B | Year2 | 3A | 1 |
B | Year3 | 1A | 0 |
B | Year3 | 2A | 0 |
B | Year3 | 3A | 2 |
It's meant tabulate the frequency for every combination of my variables (gender, year and "var").
Thank's! 🙂
Here is a way to do it using a combination of proc format, proc transpose, two datasteps and proc means:
data have; input (Year1 Year2 Year3 Gender) ($); cards; 1A 1A 1A A 3A . . A . 2A 3A B 2A 3A 3A B 1A . 1A A ; proc format; value $gender 'A'='A' 'B'='B' ; value $year 'Year1'='Year1' 'Year2'='Year2' 'Year3'='Year3' ; value $var '1A'='1A' '2A'='2A' '3A'='3A' ; run; data need; set have; recnum=_n_; run; proc transpose data=need out=need (drop=recnum rename=(_name_=year col1=Var)); var Year1 Year2 Year3; by recnum Gender; run; data need; set need; count=1; run; PROC MEANS DATA=need completetypes nway NOPRINT; FORMAT gender $gender. year $year. var $var.; CLASS gender year var/preloadfmt; VAR_count; OUTPUT OUT=want (drop=_ :) N=count; RUN;
Art, CEO, AnalystFinder.com
data have; input (Year1 Year2 Year3 Gender) ($); cards; 1A 1A 1A A 3A . . A . 2A 3A B 2A 3A 3A B 1A . 1A A ; data temp; set have; array x{*} $ _character_; do i=1 to dim(x); name=vname(x{i}); value=x{i}; if not missing(value) then output; end; drop i; run; proc freq data=temp noprint; table name*value/out=want sparse list nocum nopercent; run;
There is a problem in my above code. use this : data have; input (Year1 Year2 Year3 Gender) ($); cards; 1A 1A 1A A 3A . . A . 2A 3A B 2A 3A 3A B 1A . 1A A ; data temp; set have; array x{*} $ year:; do i=1 to dim(x); name=vname(x{i}); value=x{i}; if not missing(value) then output; end; drop i; run; proc freq data=temp noprint; table gender*name*value/out=want sparse list nocum nopercent; 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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.