topic Re: Date within two weeks in SAS Enterprise Guide

Date within two weeks

hk2013 — Tue, 24 Apr 2018 21:22:27 GMT

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

1 07JAN2018

2 28MAR2018

3 14FEB2018

1 28JAN2018

2 11APR2018

3 24FEB2018

OUTPUT:

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 .

Re: Date within two weeks

ballardw — Tue, 24 Apr 2018 17:25:29 GMT

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?

Re: Date within two weeks

hk2013 — Tue, 24 Apr 2018 23:01:37 GMT

output example is included i just want a col that if there is a next date within 14 days it says yes or no

Re: Date within two weeks

Patrick — Wed, 25 Apr 2018 02:19:02 GMT

@hk2013

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;

Re: Date within two weeks

Ksharp — Wed, 25 Apr 2018 13:51:46 GMT

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;