topic Re: Count users per usage in SAS Data Management

Count users per usage

lizzy28 — Fri, 05 Jan 2018 19:56:25 GMT

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

Re: Count users per usage

LinusH — Fri, 05 Jan 2018 20:05:16 GMT

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.

Re: Count users per usage

lizzy28 — Fri, 05 Jan 2018 20:15:29 GMT

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

Re: Count users per usage

Patrick — Sat, 06 Jan 2018 01:50:01 GMT

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