BookmarkSubscribeRSS Feed
dan999
Fluorite | Level 6

The purpose of the code is to determine when (yrmth) a person reaches $100 in claims. In the sample data when the code processes the last row it goes into the "else" part but it has lost the total from the previous pass so I get total as missing (.). What am I doing wrong?

data

claims1;

input

indvid paid yrmth;

datalines

;

1 50 201101

2 25 201102

2 85 201103

;

proc sort data=claims1;

     by indvid yrmth;

run;

data claims2 claimsmth1;

     set claims1;

     output claims2;

     if indvid ne lag(indvid) then do;

           total=0;

          total=total+paid;

           if total>=100 then output claimsmth1;

          end;

     else do;

          total=total+paid;

          if total>=100 then output claimsmth1;

          end;

run;

I wasn't able to paste my code in here. It kept not pasting half the code so I had to type it. If there are any punctuation errors please disregard. The code does run.

Thanks,

DanD

6 REPLIES 6
art297
Opal | Level 21

I don't understand why you want to output claims2 as it will merely be a copy of claims1.  What you were missing was a retain statement for total or, as I used below, a form that inherently retains the variable:

data claims1;

  input indvid paid yrmth;

  datalines;

1 50 201101

2 25 201102

2 85 201103

;

proc sort data=claims1;

     by indvid yrmth;

run;

data claims2 claimsmth1;

  set claims1;

  by indvid;

  output claims2;

  if first.indvid then total=0;

  total+paid;

  if total>=100 then output claimsmth1;

run;

dan999
Fluorite | Level 6

Thanks for the reply art. That is a much simpler solution.

DanD

DLing
Obsidian | Level 7

1) Since the dataset is sorted, using first.indvid and last.indvid is easier and cleaner than using lag().

2) the total variable needs to be retained across observations via "retain total;" or using the "a + b;" sum statement.

You're probably looking for something like:

data claims2 claimsmth1;

     set claims1;

     by indvid yrmth;

     output claims2;                             /* replication of claims1 */

     if first.indvid then total = 0;

     total = total + paid;     retain total 0;  /*  or you can use "total + paid;" which has an implied retain  */

     if total>=100 then output claimsmth1;

run;

dan999
Fluorite | Level 6

Thanks for the advice on using retain DLing. I'll try that.

DanD

Peter_C
Rhodochrosite | Level 12

to reduce to the most basic elements, and return a data set with indvid, and date at which "paid" passes 100, try

data;

   Do until( last.indvid );

      set claims1;

      by indvid;

      TotPaid = sum( TotPaid, paid );

      If totPaid GT 100 and missing( pass100 ) then pass100 = yrmth;

   End;

   Keep indvid pass100 totPaid yrmth;

Run;

dan999
Fluorite | Level 6

Thanks for the alternative method Peter.

DanD

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1174 views
  • 0 likes
  • 4 in conversation