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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@lizzy28

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;

View solution in original post

3 REPLIES 3
LinusH
Tourmaline | Level 20
First you need some logic to filter out non eligible codes. You code either code them, or have them in a look up table. With later a simple SQL inner join will do the filtering for you.
For the count I think you could use the count(distinct individual) construct, again in SQL.
Data never sleeps
lizzy28
Quartz | Level 8

Thank you! I like the idea of having a lookup table.

Patrick
Opal | Level 21

@lizzy28

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 1033 views
  • 0 likes
  • 3 in conversation