Distinct frequencies for multiple variables

[Chris_LK_87]

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!

[ed_sas_member · Accepted solution]

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]

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;

[Chris_LK_87]

Thanks!

[novinosrin]

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;