topic Re: DO UNTIL STATEMENT in SAS Programming

DO UNTIL STATEMENT

CamRutherford — Wed, 12 Jul 2017 11:26:26 GMT

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

Re: DO UNTIL STATEMENT

Kurt_Bremser — Wed, 12 Jul 2017 11:32:04 GMT

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;

Re: DO UNTIL STATEMENT

CamRutherford — Wed, 12 Jul 2017 11:39:03 GMT

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

Re: DO UNTIL STATEMENT

Kurt_Bremser — Wed, 12 Jul 2017 11:45:38 GMT

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;

Re: DO UNTIL STATEMENT

Astounding — Wed, 12 Jul 2017 12:12:22 GMT

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

Re: DO UNTIL STATEMENT

Kurt_Bremser — Wed, 12 Jul 2017 12:16:45 GMT

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