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
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;
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;
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
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;
Depending on your needs, you should give @Astounding's suggestions a good look. Especially the method with the intnx function.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.