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

Hello!

 

I have a dataset that contains diagnosis for individuals in multiple variables Diag1-Diag3. I would like to make a frequency table for distinct diagnosis for each individual in my dataset so that I can sum it for my the dataset.

 

Data Have;

 

Ind_ID$            Diag1$  Diag2$ Diag3$ 
1                        I11        I11      C10                

2                       C12       I11      C10         

3                       D12      D12     I11    

 

Data want; 

 

var.  freq.

I11   3 

C10 2

D12 1

C12 1

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

Hi @Chris_LK_87 

Here is an approach to achieve this:

data have;
	input Ind_ID$ Diag1$ Diag2$ Diag3$;
	datalines;
1 I11 I11 C10                
2 C12 I11 C10         
3 D12 D12 I11
;
run;

proc transpose data=have out=have_tr (drop=_: rename=(col1=diag));
	var Diag:;
	by Ind_ID;
run;

proc sort data=have_tr nodupkey;
	by Ind_ID Diag;
run;

proc freq data=have_tr;
	table Diag / nocum nopercent;
run;

Best,

View solution in original post

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

Hi @Chris_LK_87 

Here is an approach to achieve this:

data have;
	input Ind_ID$ Diag1$ Diag2$ Diag3$;
	datalines;
1 I11 I11 C10                
2 C12 I11 C10         
3 D12 D12 I11
;
run;

proc transpose data=have out=have_tr (drop=_: rename=(col1=diag));
	var Diag:;
	by Ind_ID;
run;

proc sort data=have_tr nodupkey;
	by Ind_ID Diag;
run;

proc freq data=have_tr;
	table Diag / nocum nopercent;
run;

Best,

ed_sas_member
Meteorite | Level 14

An equivalent approach using proc sql:

data have;
	input Ind_ID$ Diag1$ Diag2$ Diag3$;
	datalines;
1 I11 I11 C10                
2 C12 I11 C10         
3 D12 D12 I11
;
run;

proc transpose data=have out=have_tr (drop=_: rename=(col1=diag));
	var Diag:;
	by Ind_ID;
run;

proc sql;
	select diag, count(distinct Ind_ID) as freq from have_tr group by diag;
run;
novinosrin
Tourmaline | Level 20

Hi @Chris_LK_87  This is fun stufff

data have;
input Ind_ID$            Diag1$  Diag2$ Diag3$ ;
cards;
1                        I11        I11      C10                

2                       C12       I11      C10         

3                       D12      D12     I11
;

data _null_ ;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("diag") ;
   h.definedata ("diag", "freq") ;
   h.definedone () ;
 end;
 set have end=z;;
 array d diag1-diag3;
 array t(999)$ _temporary_;
 do over d;
  if d in t then continue;
  diag=d;
  if h.find()=0 then  freq=sum(freq,1);
  else freq=1;
  t(_i_)=d;
  h.replace();
 end;
 call missing(of t(*));
 if z;
 h.output(dataset:'want');
run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 669 views
  • 0 likes
  • 3 in conversation