topic Re: check a date between all dates ranges by groups in SAS in SAS Programming

check a date between all dates ranges by groups in SAS

tSAS1 — Sun, 27 Mar 2022 11:38:50 GMT

Hello everyone,

I have the below dataset in SAS, and I want to produce the "flag" column:

What I want to do is to check each date for each ID if it's between any date_start and date_end or not, if yes then flag will take 1, else 0

Thank you in advance!

Re: check a date between all dates ranges by groups in SAS

PeterClemmensen — Sun, 27 Mar 2022 11:41:43 GMT

Why is flag = 1 in obs 2 and 0 in obs 3?

Re: check a date between all dates ranges by groups in SAS

tSAS1 — Sun, 27 Mar 2022 11:56:40 GMT

@PeterClemmensen for obs=2 for ID=1, date_start=07/12/1998<=date=01/10/2001<=date_end=01/09/2002 ==> flag=1

and in obs=3 for ID=1, date=01/10/2002 > date_end=01/09/2002 ==> date is not included in any ranges of dates of date_start-date_end that's why flag=0

Re: check a date between all dates ranges by groups in SAS

yabwon — Sun, 27 Mar 2022 13:39:21 GMT

If both start and end dates are null then you want 1.

What if only one of them is null?

Re: check a date between all dates ranges by groups in SAS

tSAS1 — Sun, 27 Mar 2022 13:44:50 GMT

@yabwon if both or one of them is missing, we could skip that line (like it is deleted)

Re: check a date between all dates ranges by groups in SAS

yabwon — Sun, 27 Mar 2022 13:56:11 GMT

Something like this should do the job:

data have;
  call streaminit(123);

  do ID = 1 to 3;
    do date = today()-3 to today()+3;
      mi = rand("integer",-10,10);
      mx = rand("integer",-10,10);
      start_date = date + min(mi,mx);
      end_date   = date + max(mi,mx);
      if date = today() then end_date = .;
      output;
    end;
  end;
  format date start_date end_date yymmdd10.;
  drop mi mx;
run;

data want;
  set have;

  if . < start_date <= end_date then
    flag = start_date <= date <= end_date;

run;

Bart