How do I Pick previous end_date value as start_date when flag2 = 1 by ID?
I have
ID1 | ID2 | Start_date | End_date | Band | Flag1 | Flag2 |
101 | AA | 27-May-14 | 12-Oct-15 | A | 1 | |
101 | BB | 28-May-14 | 13-Oct-15 | B | 1 | |
101 | AA | 29-May-14 | 14-Oct-15 | C | 1 | |
101 | BB | . | . | 1 | ||
201 | AA | 27-Jun-14 | 12-Nov-15 | A | 1 | |
201 | BB | . | . | B | 1 | |
201 | AA | 29-Jun-14 | 14-Nov-15 | C | 1 | |
201 | BB | . | . | 1 | ||
102 | DD | . | . | 1 |
I want the dataset to look like:
ID1 | ID2 | Start_date | End_date | Band | Flag1 | Flag2 |
101 | AA | 27-May-14 | 12-Oct-15 | A | 1 | |
101 | BB | 28-May-14 | 13-Oct-15 | B | 1 | |
101 | AA | 29-May-14 | 14-Oct-15 | C | 1 | |
101 | BB | 14/10/2015 | . | 1 | ||
201 | AA | 27-Jun-14 | 12-Nov-15 | A | 1 | |
201 | BB | 12-Nov-15 | . | B | 1 | |
201 | AA | 29-Jun-14 | 14-Nov-15 | C | 1 | |
201 | BB | 14-Nov-15 | . | 1 | ||
102 | DD | . | . | 1 |
Thanks in advance.
Please use the code as suggested by @Kurt_Bremser , but make an updated as suggested below
data want;
set have;
by id1;
start_date = ifn(flag2=1 and start_date=.,lag(end_date),start_date);
run;
Please try the below code
data want;
set have;
by id1 ;
if not first.id1 then do;
if start_date eq . and end_date eq . then start_date=lag(end_date);
end;
run;
data want;
set have;
start_date = ifn(flag2,lag(end_date),start_date);
run;
@Jagadishkatam don't call LAG() in a conditional branch, as it puts a new value to the FIFO chain only when it is called.
ID1 | ID2 | Start_date | End_date | Band | Flag1 | Flag2 |
101 | AA | 27-May-14 | 12-Oct-15 | A | 1 | |
101 | BB | 28-May-14 | 13-Oct-15 | B | 1 | |
101 | AA | 29-May-14 | 14-Oct-15 | C | 1 | |
101 | BB | . | . | 1 | ||
201 | AA | 27-Jun-14 | 12-Nov-15 | A | 1 | |
201 | BB | . | . | B | 1 | |
201 | AA | 29-Jun-14 | 14-Nov-15 | C | 1 | |
201 | BB | . | . | 1 | ||
102 | DD | . | . | 1 | ||
301 | AA | 27-May-14 | 12-Oct-15 | A | 1 | |
301 | BB | 28-May-14 | 13-Oct-15 | B | 1 | |
401 | CC | . | . | 1 |
Thanks for suggestions
Sorry not to mention above scenario ( last 3 rows )earlier. In case of ID 401, both the solutions pick up previous 13-Oct-15 as start date which I don't want because its a different ID.
Please use the code as suggested by @Kurt_Bremser , but make an updated as suggested below
data want;
set have;
by id1;
start_date = ifn(flag2=1 and start_date=.,lag(end_date),start_date);
run;
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.