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;
inputindvid 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
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;
Thanks for the reply art. That is a much simpler solution.
DanD
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;
Thanks for the advice on using retain DLing. I'll try that.
DanD
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;
Thanks for the alternative method Peter.
DanD
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.