CODEWITHSUNDEEP
sasintervals
gduhoffmann
gduhoffmann

Reputation: 39

SAS Code for summing lines between dates

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.

Upvotes: 0

Views: 340

Answers (1)

Amit Patel
Amit Patel

Reputation: 361

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;

Upvotes: 1

Related Questions