BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Celia
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

2 REPLIES 2
Astounding
PROC Star

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.

art297
Opal | Level 21

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1014 views
  • 2 likes
  • 3 in conversation