Count users per usage

[lizzy28]

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

[LinusH]

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.

[lizzy28]

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

[Patrick · Accepted solution]

@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;