topic Re: How do i pick previous date value by ID in SAS Programming

How do i pick previous date value by ID

anandrc — Mon, 15 Jun 2020 08:46:52 GMT

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.

Re: How do i pick previous date value by ID

Jagadishkatam — Mon, 15 Jun 2020 08:57:33 GMT

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;

Re: How do i pick previous date value by ID

Kurt_Bremser — Mon, 15 Jun 2020 09:22:14 GMT

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.

Re: How do i pick previous date value by ID

anandrc — Mon, 15 Jun 2020 09:58:30 GMT

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.

Re: How do i pick previous date value by ID

Jagadishkatam — Mon, 15 Jun 2020 10:43:03 GMT

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;