BookmarkSubscribeRSS Feed
skyland1991
Obsidian | Level 7

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

2 REPLIES 2
DerylHollick
SAS Employee

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
Opal | Level 21

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;
PG

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 2 replies
  • 909 views
  • 1 like
  • 3 in conversation