SAS Code for summing lines between dates

Question

So I have data which goes something like this

 obs | date_of_service | units | CPT | ID
 -----------------------------------------
  1. |   11/03/2015    |   40  |xxxx6| 234
  2. |   11/04/2015    |    1  |xxxx5| 234
  3. |   11/11/2015    |    1  |xxxx5| 234
  4. |   11/18/2015    |    1  |xxxx5| 234
  5. |   11/20/2015    |   40  |xxxx6| 234
  6. |   11/25/2015    |    1  |xxxx5| 234
  7. |   12/02/2015    |    1  |xxxx5| 234
  etc....

over many intervals for many Ids, what I need to do is sum the units for obs between the CPT xxxx6 for each interval since it is a very large dataset with many unique IDs.

ok so this is what I have now:

%MACRO lags();
   %let n=1;
   data out;
      set in;
      by id  Date_of_service ;
      DO _n_=&n. until (last.id);
         if (id=lag&n.(id) and CPT="xxxx6") then do;
            if units ne . then output;
            call missing (TOTAL);
         end;
         if CPT="xxxx5" or CPT= "xxxx7" then TOTAL + count;
      END;
   run;
%MEND;
%lags

the problem is if I have two or more xxxx6 in a row without a xxxx5 or xxxx7 in between I get a null (.) value or a 0 in TOTAL column. and I'm not getting the first observation line for all of the ids with the xxxx6 cpt.

I've tried doing two macro loops with lag&n. (date of service) and tagging them with a tag and trying to sum it. I can only imagine this would have been a common issue... — gduhoffmann
Output would be grouped by provider id, additional ids. ultimately but that isn't what I need assistance with its more getting my head around a way to code this... I have thought of an SQL doing a sub query join after tagging the xxxx6 in an ascending count variable, but how I would join them back with a sum of the units for xxxx5 between the intervals of the new count variable of 1-2, 2-3, 3-4 and so one... using the lag macro loops I cannot capture the first occurance. — gduhoffmann

Amit Patel Amit Patel · Accepted Answer · 2015-11-04T11:05:05

Edited answer because I can't figure out how to post a new one: Looks for CPT="xxx6" and stores variables at that point. Then goes through the next rows summing units till the next xxx6 is reached and outputs before repeating. This will cause issues if no xxx6 is reached for the last set of observations. Also, what happens if the ID changes from 234 to 235 mid unit count? In the solution it would take 234 as the ID to sum.

data In;
infile cards;
input OBS DATE_OF_SERVICE:mmddyy10. UNITS CPT:$5. ID;
format DATE_OF_SERVICE date9.;
cards;
1 11/03/2015 40 xxxx6 234
2 11/04/2015 1 xxxx5 234
3 11/11/2015 1 xxxx5 234
4 11/18/2015 1 xxxx5 234
5 11/20/2015 41 xxxx6 234
6 11/25/2015 1 xxxx5 234
7 11/25/2015 1 xxxx5 234
8 11/25/2015 45 xxxx6 234
;
run;

data OUT(drop=ID UNITS DATE_OF_SERVICE OBS
rename=(STORE_UNITS=UNITS STORE_ID=ID STORE_DATE=DATE_OF_SERVICE STORE_OBS=OBS));
set IN;
retain STORE_OBS STORE_DATE STORE_UNITS STORE_ID;
if CPT="xxxx6" then do;
    if STORE_UNITS ne . then output;
    call missing (TOTAL);

    STORE_UNITS=UNITS;
    STORE_DATE=DATE_OF_SERVICE;
    STORE_ID=ID;
    STORE_OBS=OBS;
end;
if CPT="xxxx5" then TOTAL+UNITS;
run;

proc print; run;

SAS Code for summing lines between dates

1 Answers