How to I set 2 consecutive observations before and after a dummy equal to one

[skyland1991]

Hello SAS Community,

I ran into a problem when I was trying to set the 2 consecutive observations before and after a certain observation equal to one. the dataset looks like this:

Date      TradingDate      TradingDummy        What I want

1/1/23        .                             .                            .

1/2/23        .                             .                            1

1/3/23        .                             .                            1

1/4/23       1/4/23                    1                            1

1/5/23        .                            .                             1

1/6/23        .                            .                             1

1/7/23        .                            .                             .

1/8/23        .                            .                             .

1/9/23        .                            .                            1

1/10/23      .                            .                            1

1/11/23     1/11/23                  1                           1

1/12/23     .                             .                            1

1/13/23     .                             .                            1

1/14/23     .                             .                             .

...              ...                          ...                          ...

Basically what I want is for each trading date, I want to create a dummy that equals 1 for 2 days before and after (including the trading date). For this example, I want the trading dummy to equal 1 from 1/2 to 1/6, and from 1/9 to 1/13.

Any help would be greatly appreciated!

Best,

skyland

[DerylHollick]

Here's one way:

data trades;
   format Date TradingDate mmddyy8.;
   infile datalines missover;
   input Date:mmddyy8. TradingDate:mmddyy8. TradeDummy What_I_Want;
   datalines;
   01/01/23
   01/02/23
   01/03/23
   01/04/23   01/04/23   1
   01/05/23
   01/06/23
   01/07/23
   01/08/23
   01/09/23
   01/10/23
   01/11/23   01/11/23   1
   01/12/23
   01/13/23
   01/14/23
   ;
run;

proc sql noprint; 
   select count(date) into :num_dates from trades;
quit;

data trades;
   * Pre-load all TradeDummy values into an array;
   array trade[&num_dates] _TEMPORARY_;
   if _N_ = 1 then do p = 1 to nobs;
      set trades nobs=nobs point=p;
      trade[p] = TradeDummy;
   end;

   * For each row with a trade update array values +/-2 consecutive elements away;
   set trades end=lastobs;
   if TradeDummy then do;
      if _N_ > 2 then trade[_N_-2] = 1;
      if _N_ > 1 then trade[_N_-1] = 1;
      trade[_N_+1] = 1;
      trade[_N_+2] = 1;
   end;

   * Write out the dataset with values from the array;
   if lastobs then do p = 1 to nobs;
      set trades nobs=nobs point=p;
      What_I_Want = trade[p];
      output;
   end;

   drop p;
run;

[PGStats]

With a single SQL step:

   proc sql;
   create table want as
   select 
    *,
    case when exists (
        select * 
        from trades 
        where intck("day", a.date, tradingDate) between -2 and 2
        )   then 1
            else . end as what_I_want
   from trades as a
   ;
   quit;