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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: