topic Re: Distinct frequencies for multiple variables in SAS Programming

Distinct frequencies for multiple variables

Chris_LK_87 — Mon, 08 Jun 2020 15:17:27 GMT

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!

Re: Distinct frequencies for multiple variables

ed_sas_member — Mon, 08 Jun 2020 15:22:55 GMT

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,

Re: Distinct frequencies for multiple variables

ed_sas_member — Mon, 08 Jun 2020 15:25:33 GMT

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;

Re: Distinct frequencies for multiple variables

novinosrin — Mon, 08 Jun 2020 16:11:07 GMT

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;

Re: Distinct frequencies for multiple variables

Chris_LK_87 — Tue, 09 Jun 2020 06:32:48 GMT

Thanks!