Hi,
I'd like to have your help with counting unique individuals per usage (code in my case) before I spend too much time on working out nothing. Basically I tried to count unique individuals per diagnosis/procedure code. An individual can have multiple records, and in each record there are 10 diagnosis codes and 5 procedure codes. Many codes are actually invalid (having values like 000000 and 999999). There are nearly 50 eligible diagnosis codes and 30 eligible procedure codes to check. I attached some sample data for more details.
Could anyone guide me how to count unique individuals per eligible code?
Thanks a lot,
Lizi
Something along the line of below could work:
data long(keep=sampleid service_date key value);
set have;
array diags {*} diag_:;
array procs {*} proc_:;
length key $4 value $8;
key='Diag';
do _i=1 to dim(diags);
value=diags[_i];
output;
end;
key='Proc';
do _i=1 to dim(procs);
value=procs[_i];
output;
end;
run;
proc sql;
create table want as
select
key,
value,
count(distinct sampleid) as cnt_dist_id
from long
group by key, value
;
quit;
Thank you! I like the idea of having a lookup table.
Something along the line of below could work:
data long(keep=sampleid service_date key value);
set have;
array diags {*} diag_:;
array procs {*} proc_:;
length key $4 value $8;
key='Diag';
do _i=1 to dim(diags);
value=diags[_i];
output;
end;
key='Proc';
do _i=1 to dim(procs);
value=procs[_i];
output;
end;
run;
proc sql;
create table want as
select
key,
value,
count(distinct sampleid) as cnt_dist_id
from long
group by key, value
;
quit;
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.