topic Re: Dates backward and forward in SAS Programming

Dates backward and forward

RandyStan — Mon, 16 Sep 2019 16:41:35 GMT

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/21/2019 1 1

06/22/2019 1 1 1

06/23/2019 1 1 1

06/24/2019 1 1

06/26/2019 1

06/27/2019

06/28/2019

Re: Dates backward and forward

PaigeMiller — Mon, 16 Sep 2019 16:44:08 GMT

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.

Re: Dates backward and forward

novinosrin — Mon, 16 Sep 2019 16:48:53 GMT

Are your dates continuous or not?

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

Re: Dates backward and forward

PaigeMiller — Mon, 16 Sep 2019 16:50:50 GMT

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

Re: Dates backward and forward

RandyStan — Mon, 16 Sep 2019 16:51:42 GMT

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???

Re: Dates backward and forward

PaigeMiller — Mon, 16 Sep 2019 16:52:40 GMT

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

Re: Dates backward and forward

novinosrin — Mon, 16 Sep 2019 16:58:45 GMT

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!

Re: Dates backward and forward

RandyStan — Mon, 16 Sep 2019 17:26:22 GMT

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

Re: Dates backward and forward

RandyStan — Mon, 16 Sep 2019 18:20:37 GMT

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

Re: Dates backward and forward

Tom — Mon, 16 Sep 2019 18:57:17 GMT

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.

Re: Dates backward and forward

Reeza — Mon, 16 Sep 2019 19:14:26 GMT

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-data-AKA-generate/ta-p/258712

Re: Dates backward and forward

RandyStan — Mon, 16 Sep 2019 19:21:58 GMT

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.

Re: Dates backward and forward

Tom — Mon, 16 Sep 2019 19:37:21 GMT

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?

Re: Dates backward and forward

novinosrin — Tue, 17 Sep 2019 12:36:41 GMT

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;

Re: Dates backward and forward

RandyStan — Tue, 17 Sep 2019 04:06:03 GMT

Tom: I do not follow your question

Re: Dates backward and forward

novinosrin — Tue, 17 Sep 2019 18:11:24 GMT

Here's a variant of the previous using dates as key unlike record identifier and setcur hash method

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;
 if 0 then set have have(rename=(date=_date));
 dcl hash H (dataset:'have(rename=(date=_date))',ordered:'a');
 h.definekey  ("_date");
 h.definedata ("_date");
 h.definedone ();
 dcl hiter hi('h');
 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;
   _count=0;
  do i=1 to dim(u);
   if u(i)=1 then do;
	rc = hi.setcur(key:date);
	do _count = 1 to 2 while (rc = 0);
     rc = hi.prev();
	 _n_+1;
	 t(i,_n_)=_date;
	end;
	do rc=hi.setcur(key:date) by 0 while(rc=0 and _count<=5);
	 _n_+1;
	 _count+1;
	 t(i,_n_)=_date;
	 rc=hi.next();
	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;

Re: Dates backward and forward

Tom — Tue, 17 Sep 2019 18:19:58 GMT

@RandyStan wrote:

Tom: I do not follow your question

You have multiple observations with the same value for the date. In theory they could each have a different value for varA.

Is that actually possible? Does it actually happen? And if it happens how does that change what you want for the new variable?

As a side note do you need to keep multiple observations for the same date in the output dataset? Is there some other variable that will have different values one each of the multiple observations for the same date? If not then why not just collapse to one observation per date and make the process easier.

Re: Dates backward and forward

RandyStan — Wed, 18 Sep 2019 03:52:50 GMT

Hi Tom:

Sorry for not getting back earlier in the day.

This is a trading data set and there are multiple observations for other variables on a particular date.

The study is an event study where the window opens some days prior to the event and shuts some days after the event. The event date is flagged as VarA = 1 and VarB = 1. For a normal event study the code is simple (from the SA s Boards)

data want;
   x='15JAN2019'd;
   fivedaysprior = intnx('day',x,-5);
   twodaysafter = intnx('day',x,2);
   format x fivedaysprior twodaysafter date9.;
run;

But what is complicating is that there are multiple events and multiple observations of other variables on the same day.

Does anyone have any suggestions?

Randy

Re: Dates backward and forward

hashman — Wed, 18 Sep 2019 04:16:08 GMT

@RandyStan:

If the general pattern of your input data set is exactly as you've shown - in other words, the data variations whose possibility @Tom has sagely spelled out do not apply, you can simplify your program logic by eliminating the duplicates by DATE (again, @Tom's suggestion), creating the required variables for the unduplicated dates, and merging the results back with the original data. It can be done as a multi-step process creating a separate data set at every step. Below, it's done in a single step via a hash table made use of two things at once: (a) auto-eliminating the duplicates and (b) flagging the required items which are then merged with the original data.

data have ;                                                                                                                             
  input date :mmddyy10. vara varb ;                                                                                                     
  format date yymmdd10. ;                                                                                                               
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      .      .                                                                                                                 
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want (drop = _:) ;                                                                                                                 
  if _n_ = 1 then do ;                                                                                                                  
    dcl hash h (ordered:"a") ;                                                                                                          
    h.definekey  ("date") ;                                                                                                             
    h.definedata ("date", "vara", "varb", "vara_bf", "varb_bf") ;                                                                       
    h.definedone () ;                                                                                                                   
    dcl hiter hi ("h") ;                                                                                                                
    do until (z) ;                                                                                                                      
      set have end = z ;                                                                                                                
      h.ref() ;                                                                                                                         
    end ;                                                                                                                               
    do _q = 1 by 1 while (hi.next() = 0) ; link fill ; end ;                                                                            
    do _q = 1 by 1 while (hi.prev() = 0) ; link fill ; end ;                                                                            
  end ;                                                                                                                                 
  set have ;                                                                                                                            
  h.find() ;                                                                                                                            
  return ;                                                                                                                              
  fill: if _q = 1 then call missing (_qa, _qb) ;                                                                                        
        if vara then _qa = _q ;                                                                                                         
        if varb then _qb = _q ;                                                                                                         
        if _qa then if _q <= _qa + 2 then vara_bf = 1 ;                                                                                 
        if _qb then if _q <= _qb + 2 then varb_bf = 1 ;                                                                                 
        h.replace() ;                                                                                                                   
  return ;                                                                                                                              
run ;

Kind regards

Paul D.