BookmarkSubscribeRSS Feed
RandyStan
Fluorite | Level 6

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

18 REPLIES 18
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
novinosrin
Tourmaline | Level 20

Are your dates continuous or not?

 

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

PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
RandyStan
Fluorite | Level 6

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
Diamond | Level 26

@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. 😞

--
Paige Miller
novinosrin
Tourmaline | Level 20

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
Fluorite | Level 6

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
Fluorite | Level 6

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

novinosrin
Tourmaline | Level 20

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
Super User Tom
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6

Tom:  I do not follow your question

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 18 replies
  • 2214 views
  • 1 like
  • 6 in conversation