topic How to I set 2 consecutive observations before and after a dummy equal to one in SAS Programming

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

skyland1991 — Wed, 03 May 2023 00:03:19 GMT

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

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

DerylHollick — Mon, 15 May 2023 00:19:41 GMT

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;

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

PGStats — Mon, 15 May 2023 03:29:50 GMT

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;