04-24-2018 01:12 PM - edited 04-24-2018 05:22 PM
I have a id and date column and I want to find out if the id had a second within 2 weeks(14 days);
id date SEEN within 2 weeks(14 days from the first date)
1 07JAN2018 No
2 28MAR2018 yes
3 14FEB2018 yes
1 28JAN2018 .
2 11APR2018 .
3 24FEB2018 .
04-24-2018 01:25 PM
Please show what you expect for output for your example input.
"Week" is a somewhat slippery concept. By within two weeks do you mean 14 days, do you have any rules about "workdays" "weekdays", which day of a week starts a week.
And the ever popular is your date variable actually a SAS date valued variable?
04-24-2018 10:19 PM
Below should work. Please post next time your sample data via a working SAS data step as I've done below.
data have; input id date :date9.; format date date9.; datalines; 1 07JAN2018 2 28MAR2018 3 14FEB2018 1 28JAN2018 2 11APR2018 3 24FEB2018 ; run; proc sql; create table want as select o.*, case when (select i.date from have i where i.id=o.id and i.date between o.date+1 and o.date+14 ) ne . then 'Yes' else 'No' end as YesNo length=3 from have o ; quit;
04-25-2018 09:51 AM
data have; input id date :date9.; format date date9.; datalines; 1 07JAN2018 2 28MAR2018 3 14FEB2018 1 28JAN2018 2 11APR2018 3 24FEB2018 ; run; data temp; set have; if id=1 then group+1; run; proc sort data=temp; by id descending group; run; data temp; set temp; dif=dif(date); if id=lag(id) and abs(dif)<=14 then flag='yes'; else flag='no'; run; proc sort data=temp out=want; by group id; run; proc print;run;