topic Flagging rows based on dates across two variables that are on different rows in SAS Programming

Flagging rows based on dates across two variables that are on different rows

joshCRF — Thu, 04 Apr 2024 17:18:47 GMT

So as the image below shows, this is currently data that is within one data-step.

I'm currently trying to figure out a way in which rows can be flagged which contain the same date or same date + 1; when the end date matches the start date on the following row or if the end date is within a day of the start date on the following row.

Essentially we are checking for when the row has 'chgdt_10' populated and then if the end date matches the start date of the following row or within +1 day(s).

Using the lag function I am able to (testflg) the consecutive records but unable to flag the initial record where the match occurs, so where I've linked the rows with the blue line I would like the very first row to be populated for 'testflg' too.

Thanks in advance for any responses.

Re: Flagging rows based on dates across two variables that are on different rows

sbxkoenk — Thu, 04 Apr 2024 18:05:29 GMT

data have;
start='25sep2021'd; end='28apr2022'd; output;
start='28apr2022'd; end='15sep2022'd; output;
start='15sep2022'd; end='03oct2022'd; output;
format start end date9.;
run;
data have; set have; rowid=_N_; run;

/* you probably have this now : */
/*
data want(drop=a);
 set have;
 a=lag(end);
 if start=a then testflg=1;
run;
*/

/* you need this : */
/* just an example -- other solutions are possible !! */
proc timedata data=have out=wanthmmm outarray=work.testflg_array print=(arrays);
   id rowid interval=day;
   vars start end;
   outarrays testflg;
      do t= 1 to dim(end);
         if end[t]   = start[t+1] then do; testflg[t]=1; end;
		 else do; testflg[t]=0; end;
      end;
run;

data work.testflg_array;
 set work.testflg_array;
 keep  start end testflg;
 format start end date9.;
run;
/* end of program */

PROC TIMEDATA is part of SAS/ETS software.

Koen

Re: Flagging rows based on dates across two variables that are on different rows

data_null__ — Thu, 04 Apr 2024 18:42:28 GMT

I think you want to look ahead. You should be able to find lots of discussion here in the community.

Look-Ahead and Look-Back - SAS Support Communities

Is a good start the part from @mkeintz is really good.

If you post some useable data in text format not a picture you will get more helpful solutions.

Re: Flagging rows based on dates across two variables that are on different rows

mkeintz — Fri, 05 Apr 2024 01:59:41 GMT

Why did you flag the last line (START=05MAR2024), but not flag its predecessor (END=04MAR2024)?

In the absence of usable sample data in the form of a working DATA step, here is my untested guess as to what you want.

data want (drop=nxt_:);
  set have ;
  by chgdt_10 notsorted;
  merge have
        have (firstobs=2 keep=chgdt_10 start rename=(chgdt_10=nxt_chgdt start=nxt_start)) ;

  retain _beg_series  _end_series 0;
  _beg_series = (first.chgdt_10=1 and chgdt_10^=.);
  _end_series = (last.chgdt_10=1  and nxt_chgdt^=.);

  flag=  (start-1 <= lag(end)  and _beg_series=0)
         or
         (end+1   >= nxt_start and _end_series=0);
run;

This assume that each series

is sorted by START and
starts with a non-missing value for CHGDT_10.