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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.