Desktop productivity for business analysts and programmers

Date within two weeks

Reply
Contributor
Posts: 42

Date within two weeks

[ Edited ]

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        .

.

Super User
Posts: 13,932

Re: Date within two weeks

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?

Contributor
Posts: 42

Re: Date within two weeks

output example is included i just want a col that if there is a next date within 14 days it says yes or no
Respected Advisor
Posts: 4,791

Re: Date within two weeks

@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;
Super User
Posts: 10,849

Re: Date within two weeks

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;
Ask a Question
Discussion stats
  • 4 replies
  • 160 views
  • 0 likes
  • 4 in conversation