SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Count users per usage

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

Count users per usage

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


Accepted Solutions
Solution
‎01-09-2018 02:37 PM
Respected Advisor
Posts: 4,673

Re: Count users per usage

@liziwu

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


All Replies
Super User
Posts: 5,851

Re: Count users per usage

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
Frequent Contributor
Posts: 110

Re: Count users per usage

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

Solution
‎01-09-2018 02:37 PM
Respected Advisor
Posts: 4,673

Re: Count users per usage

@liziwu

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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