Counts by 2 variables in claims data

[markus24135]

Hey all,

I am using SAS 9.4 and I have a data set from a healthcare claims database. One particular class of drug that I am interested in has 3 categories based on how bad the drug is based on previous literature ("category of drug" variable below). This data spans from January 2003 to June 2015 which means I have 150 months (don't worry if 150 is rounded or inaccurate or whatever, it is just how many months I ended up with) - (month variable below). Each claim for the drug falls into one of the months as you see below (claim_id). The data is sorted by month and can be sorted by drug category too. 

What I need though is how many times a category (1, 2, or 3)  falls into a particular month and that number carried down for the whole time the month is carried down. So a count of drug category per month, each one in a separate variable (columns: drugcat1, drugcat2, drugcat3), carried down for the entire time the month is carried down.

After that, I need the total number of claims per month (total drug) in a separate column.

I was thinking the first part could be accomplished by:

data nolkbktrytocountaaobese;
set perm.nolkbkcohortmon;
by month;
if category_of_drug=1 then drugcat1 = 1;
drugcat1 + 1;
if category_of_drug=2 then drugcat2 = 1;
drugcat2 + 1;
if category_of_drug=3 then drugcat3 = 1;
drugcat3 + 1;
run;

And the latter part:

data finallyitworked;
set nolkbktrytocountaaobese;
by month;
if first.month then totaldrug = 1;
totaldrug + 1;
if last.month then output;
run;

I'm so sorry about this, I have literally looked around for a few days now trying to figure this out. I would really, really, really appreciate any input here. I need this done soon 😕

If you have any other questions, please let me know.

Thanks!!!
Mark

[seemiyah · Accepted solution]

Hi Mark,

One way this can be achieved is using SQL with an implicit re-merge (see the note in the log).

data have ;
   input Month Category Claim_ID $9.;
cards;
1 3 000123654
1 2 000765921
1 2 010458923
1 1 001973654
2 1 000760982
2 3 000564228
3 2 011587795
;
run ;

proc sql;
   select
    Month
   ,Category
   ,Claim_Id
   ,sum(Category=1) as DrugCat1
   ,sum(Category=2) as DrugCat2
   ,sum(Category=3) as DrugCat3
   ,count(*) as TotalDrug

   from have
   group by Month ;
quit ;

[ballardw]

First question: Do you need a data set for further processing or a report for people to read?

It would seriously help to have examples of your data, at least enough to generate some output and what the output for that example data should be. It looks like we should only need an ID, if these are sensitive you can mask them as long as the expected output references the included value, the month and the drug cat.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You will need to explain, if there is a reason, that the "category of drug" kept in the output is that particular value if it is actually needed. Typically any single one of multiple values wouldn't make much sense on a summary.

Something like perhaps

proc summary data= perm.nolkbkcohortmon  nway;
   class month claim_id  category_of_drug;
   output out = work.sum (drop=_type_);
run;

proc transpose data=work.sum
    out=work.trans(drop=_name_) prefix=drugcat;
    by month claim_id;
    var _freq_;
    id category_of_drug;
run;

data want;
   set work.trans;
   totaldrug = sum(drugcat1, drugcat2, drugcat3);
run;

Proc summary without any VAR statement will count the combinations of the CLASS variables. Data sets do not need to be sorted for CLASS grouping. The output will be in order of the CLASS variables.

Transpose makes the data "wide" as desired. The Prefix sets the base part of the output name and the ID variable contains the levels of the category to make the name as needed.

Then a simple data step to add the drugcat variables for your total.

And if you don't need a data set but a report perhaps:

proc tabulate data=perm.nolkbkcohortmon  ;
   class month claim_id  category_of_drug;
   table month*claim_id,
         (category_of_drug all='Drug total') *n=''
         /misstext=' ';
run;

[markus24135]

Thank you all.

I need a data set for further processing. Thank you so much for your responses. I will have to check these this afternoon (around 5:00pm CST) and get back to you to let you know if any of the responses worked.

Thank you all again!!!

Mark