DO UNTIL STATEMENT

[CamRutherford]

Hello,

I have some code which shows multiple records per ID with a transaction date for each row. I want to perform a looped expression that will do the following...

Grouped by ID, beginning with the row that has the min(trans_date), flag all of those that fall within 4months after. However, on the next instance where the trans_date does not fall within 4months then repeat the expression with the amended min(trans_date) being this one.

EG.

ID	TRAN_DATE	   WITHIN 4MTHS
MBR123	01JAN2017	   N
MBR123	12FEB2017	   Y
MBR123	01JUL2017	   N
MBR123	22JUL2017	   Y
MBR123	06AUG2017	   Y

 Hope this makes sense.

Cheers,

Cam 

[Kurt_Bremser]

proc sort data=have;
by id tran_date;
run;

data want;
set have;
by id;
retain min_date;
if first.id then min_date = tran_date;
if intck('month',min_date,tran_date) <= 4
then within_4 = 'Y';
else do;
  within_4 = 'N';
  min_date = tran_date;
end;
run;

[CamRutherford]

Hi Kurt,

Thanks for this, however it flags the records with the min_date within 4 months as well? I don't want to flag the starting dates.

Cheers

[Kurt_Bremser · Accepted solution]

Then you need to slightly expand the conditions:

data want;
set have;
by id;
retain min_date;
if first.id
then do;
  min_date = tran_date;
  within_4 = 'N';
end;
else do;
  if intck('month',min_date,tran_date) <= 4
  then within_4 = 'Y';
  else do;
    within_4 = 'N';
    min_date = tran_date;
  end;
end;
run;

[Kurt_Bremser]

Depending on your needs, you should give @Astounding's suggestions a good look. Especially the method with the intnx function.

[Astounding]

While Kurt has the right structure to the program, I strongly urge you to reconsider use of INTCK.  It probably does not measure "4 months" the way you expect that it would.  Here are couple of different versions that could replace it:

if (tran_date - min_date) <= 120 then ...

if tran_date <= intnx('month', min_date, 4, 's') then ...

With INTCK, you will find (for example) that this interval is still measured as 4 months:

min_date = 02Jan2017

tran_date = 30May2017