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