Dates backward and forward

[RandyStan]

Dear All:

   I have flags for certain dates.  I want to construct two variables, for VarA and VarB, called VarA_BF and VarB_BF, that are lagged two days from the date of the flag and two days forward from the flag date.

Thanx in advance

My Data is as Follows

Date                         VarA              VarB          VarA_BF        VarB_BF

06/20/2019                                                                                    1

06/20/2019                                                                                    1

06/20/2019                                                                                    1

06/20/2019                                                                                    1

06/21/2019                                                            1                      1

06/21/2019                                                            1                      1                                  

06/21/2019                                                            1                      1

06/22/2019                                   1                       1                      1

06/22/2019                                   1                       1                      1

06/22/2019                                   1                       1                      1

06/22/2019                                   1                       1                      1

06/23/2019          1                                                1                      1

06/23/2019          1                                                1                      1

06/23/2019          1                                                1                      1

06/24/2019                                                            1                      1

06/24/2019                                                            1                      1

06/24/2019                                                            1                      1

06/24/2019                                                            1                      1

06/26/2019                                                            1

06/26/2019                                                            1

06/27/2019

06/27/2019

06/27/2019

06/28/2019

[PaigeMiller]

Since there is a LAG function in SAS but no LEAD function, you use LAG2() to obtain results from two days in the past, then sort the data set so that dates are descending, and use LAG2() to obtain results from two days in the future.

[novinosrin]

Are your dates continuous or not?

25th is missing, otherwise it seems continuous???

[PaigeMiller]

---

@novinosrin wrote:

Are your dates continuous or not?

25th is missing, otherwise it seems continuous???

---

Good question, also most (all?) dates have multiple records, it's not clear how that should be handled.

[RandyStan]

The dates are NOT continuous.  Sorry for not making it clear.

Using the Lag function does not work, as the dates are repeated in the data set.

Thanx so much.

---

@novinosrin wrote:

Are your dates continuous or not?

25th is missing, otherwise it seems continuous???

---

[PaigeMiller]

---

@RandyStan wrote:

The dates are NOT continuous.  Sorry for not making it clear.

Using the Lag function does not work, as the dates are repeated in the data set.

Thanx so much.

---

---

Then clearly my approach will not work. 😞

[novinosrin]

Okay @RandyStan   Can you please post the sample that I could easily copy paste. i.e border the cells, for missing ones put  .(dot) and paste it back here. Thank you!

[RandyStan]

Here goes.

Thanx.

Date	VarA	VarB	VarA_BF	VarB_BF
6/20/2019				1
6/20/2019				1
6/20/2019				1
6/20/2019				1
6/21/2019			1	1
6/21/2019			1	1
6/21/2019			1	1
6/22/2019		1	1	1
6/22/2019		1	1	1
6/22/2019		1	1	1
6/22/2019		1	1	1
6/23/2019	1		1	1
6/23/2019	1		1	1
6/23/2019	1		1	1
6/24/2019			1	1
6/24/2019			1	1
6/24/2019			1	1
6/24/2019			1	1
6/26/2019			1
6/26/2019			1
6/27/2019
6/27/2019
6/27/2019
6/28/2019

[RandyStan]

Just added 0's to the table

Date	VarA	VarB	VarA_BF	VarB_BF
6/20/2019	0	0	0	1
6/20/2019	0	0	0	1
6/20/2019	0	0	0	1
6/20/2019	0	0	0	1
6/21/2019	0	0	1	1
6/21/2019	0	0	1	1
6/21/2019	0	0	1	1
6/22/2019	0	1	1	1
6/22/2019	0	1	1	1
6/22/2019	0	1	1	1
6/22/2019	0	1	1	1
6/23/2019	1	0	1	1
6/23/2019	1	0	1	1
6/23/2019	1	0	1	1
6/24/2019	0	0	1	1
6/24/2019	0	0	1	1
6/24/2019	0	0	1	1
6/24/2019	0	0	1	1
6/26/2019	0	0	1	0
6/26/2019	0	0	1	0
6/27/2019	0	0	0	0
6/27/2019	0	0	0	0
6/27/2019	0	0	0	0
6/28/2019	0	0	0	0

[Reeza]

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[novinosrin]

Hello @RandyStan  Thank you. 

See if this works and helps. Feeling not too well today and so might require a minor tweak. Please test and let me know. Thank you!

data have;
input Date :mmddyy10.	VarA	VarB;
format date mmddyy10.;
cards;
6/20/2019	.	.
6/20/2019	.	.
6/20/2019	.	.
6/20/2019	.	.
6/21/2019	.	.
6/21/2019	.	.
6/21/2019	.	.
6/22/2019	.	1
6/22/2019	.	1
6/22/2019	.	1
6/22/2019	.	1
6/23/2019	1	.
6/23/2019	1	.
6/23/2019	1	.
6/24/2019	.	.
6/24/2019	.	.
6/24/2019	.	.
6/24/2019	.	.
6/26/2019	.	.
6/26/2019	.	.
6/27/2019	.	.
6/27/2019	.	.
6/27/2019	.	.
6/28/2019	.	.
;

dm log 'clear';
data want;
 dcl hash H () ;
 h.definekey  ("_iorc_") ;
 h.definedata ("_date") ;
 h.definedone () ;
 do until(z);
  set have end=z;
  by date;
  if first.date then do;
  _iorc_+1;
  _date=date;
  rc=h.add();
  end;
 end;
 z=0;
 _iorc_=0;
 do until(z);
  set have end=z;
  by date;
  array u VarA	VarB;
  array v  VarA_BF  VarB_BF;
  array t(2,999999) _temporary_;
  if first.date then do;
  _iorc_+1;
  do i=1 to dim(u);
  if u(i)=1 then do;
   do _n_=_iorc_-2 to _iorc_+2;
    rc=h.find(key:_n_);
	if rc=0 then t(i,_n_)=_date;
   end;
  end;
 end;
end;
end;
z=0;
do until(z);
 set have end=z;
 by date;
 if first.date then do;
 call missing(of v(*));
 do i=1,2;
  do j=1 to 999999;
  if t(i,j)=date then do;
  v(i)=1;
  leave;
  end;
  end;
 end;
 end;
 output;
end;
stop;
keep date var:;
run;

[Tom]

Do you want to carry forward two DAYS or two distinct dates?  In your example the A flag is being carried forward 3 days, from 23rd to 26th.

[RandyStan]

Since there is no 06/25/2019 the flag should go to 06/26/2019.

As I have noted earlier the date series is not continuous.

[Tom]

Do the multiple observations for the same date always have the same values for the two flag variables?

If not then which value is to be used to create the new variables?

[RandyStan]

Tom:  I do not follow your question