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.
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.
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
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.