Tom, thanks for your reply. I was able to figure it out using your code for grouping prescriptions together. ... View more

You have to go through each person_id twice - once to review only the non-zero payments, keeping the last date of this subgroup (last_non_zero).  The second time reads ALL the records, setting DUMMY=1 only for the case in which current payment=0 and the single record lag of date matches last_non_zero:   data want (drop=_:); set have (where=(payment^=0) in=non_zero_payment) have (in=second_pass); by person_id; if non_zero_payment=1 then _last_non_zero=claim_date; else if first.person_id then _last_non_zero=.; retain _last_non_zero; if second_pass; dummy=ifn(payment=0 and lag(claim_date)=_last_non_zero,1,0); run;   The SET statement reads each person_id twice.  The first pass reads only non-zero payment and the second reads all records. The _last_non_zero date variable is updated with each non-zero payment.  Since _last_non_zero is a retained variable, then it is reset to missing whenever a person_id has ONLY zero payments. The "if second_pass" is a subsetting if. The DUMMY= expression tests for current payment=0 and lag of date matching the _last_non_zero date.   Edited addition: This program assume data are sorted by person_id/claim_date, but it purposely only used "BY PERSON_ID" in the by-statement. ... View more

Hi,   With any solution here, you will need to work out a rule as to what observation from the joining table should be kept.  Its difficult to say, maybe post some test data - a few data elements of table as a datastep - and what the output should look like - and specify why a certain rwo should be pulled out, this is the key part here.  Once you know hwich record you want to pull out then you should be able to simply sub-query the input dataset to filter for this record. ... View more

  There seems to be an error since the only three values of birthmonth from the output dataset are 2, 3, and 12.  I think this has to do with the fact that the code uses lag2 instead of all possible values of lag like lag3, lag4, etc.      Focusing on scenario 1 and 2 only for the moment, I'm trying the following code that manually compares every age to January age; if they are not equivalent, birth month is set to that month and then the loop should exit.  However, this code results in an infinite loop.     data want; set have; by id; retain _jan_age; if month = 1 then _jan_age = age; birthmonth = .; do while(birthmonth = .);      if age NE _jan_age then birthmonth = month - 1;      if month = 12 and age = _jan_age then birthmonth = 12; end; run; ... View more