Hi I have categorical data in mulitple columns like this:
ID | T1 | T2 | T3 | T4 | M1 | M2 | M3 |
1 | NN | NN | YN | NN | YY | NY | NN |
2 | YN | NN | YN | YN | NY | YN | NN |
3 | YY | YY | YN | YY | NY | YN | NN |
4 | NY | YY | NY | NY | YN | NN | NN |
5 | NN | NN | YN | NN | YY | NY | NN |
6 | YN | NN | YN | YN | NY | YN | NN |
7 | YY | YY | YN | YY | NY | YN | NN |
8 | NY | YY | NY | NY | YN | NN |
NN |
The categorical values (NY,NN, YN, and YY) are the same in each of the columns.
I would like aggregated count output for the categories in each column as a single table, like:
T1 | T2 | T3 | T4 | M1 | M2 | M3 | |
NN | 2 | 4 | 2 | 2 | 7 | ||
YN | 2 | 6 | 2 | 2 | 4 | 1 | |
YY | 2 | 4 | 2 | 2 | |||
NY | 2 | 2 | 2 | 4 | 2 |
Can anyone suggest some code that would do this?
Appreciate your help,
Celia.
It's not quite clear if you are looking for a report or a data set. But here is a step in the right direction in any case:
data want;
set have;
array in {7} t1-t4 m1-m3;
do i=1 to 7;
value = in{i};
category = vname(in{i});
output;
end;
keep value category;
run;
proc freq data=want;
tables value * category;
run;
You can always add options to the TABLES statement (such as norow nocolumn nopercent) to change the appearance of the table.
It's not quite clear if you are looking for a report or a data set. But here is a step in the right direction in any case:
data want;
set have;
array in {7} t1-t4 m1-m3;
do i=1 to 7;
value = in{i};
category = vname(in{i});
output;
end;
keep value category;
run;
proc freq data=want;
tables value * category;
run;
You can always add options to the TABLES statement (such as norow nocolumn nopercent) to change the appearance of the table.
If you want a dataset, here is one way:
data have; infile cards dlm='09'x; input ID (T1 T2 T3 T4 M1 M2 M3) ($); cards; 1 NN NN YN NN YY NY NN 2 YN NN YN YN NY YN NN 3 YY YY YN YY NY YN NN 4 NY YY NY NY YN NN NN 5 NN NN YN NN YY NY NN 6 YN NN YN YN NY YN NN 7 YY YY YN YY NY YN NN 8 NY YY NY NY YN NN NN ; ods output onewayfreqs=need; proc freq data=have; tables t1--m3; run; data need (drop=f_t1--m3); retain frequency; set need (drop=percent cum_freq cum_pct); array ts(*) f_t1--m3; table=scan(table,2); t=coalescec(of ts(*)); run; proc sort data=need; by t; run; proc transpose data=need out=want (drop=_:); by t; var frequency; id table; run; data want; retain t t1-t4 m1-m3; set want; run;
Art, CEO, AnalystFinder.com
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.