BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CamRutherford
Fluorite | Level 6

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User
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
Fluorite | Level 6

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
Super User

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;
Astounding
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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