DATA Step, Macro, Functions and more

DO UNTIL STATEMENT

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

DO UNTIL STATEMENT

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 


Accepted Solutions
Solution
‎07-12-2017 08:06 AM
Super User
Posts: 7,868

Re: DO UNTIL STATEMENT

Posted in reply to CamRutherford

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 7,868

Re: DO UNTIL STATEMENT

Posted in reply to CamRutherford
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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 60

Re: DO UNTIL STATEMENT

Posted in reply to 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

 

 

Solution
‎07-12-2017 08:06 AM
Super User
Posts: 7,868

Re: DO UNTIL STATEMENT

Posted in reply to CamRutherford

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,868

Re: DO UNTIL STATEMENT

Posted in reply to CamRutherford

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,518

Re: DO UNTIL STATEMENT

Posted in reply to CamRutherford

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 129 views
  • 1 like
  • 3 in conversation