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!
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,
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,
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;
Thanks!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.