topic Re: How to create an extra row after the last row of each BY group? in SAS Programming

How to create an extra row after the last row of each BY group?

somebody — Mon, 09 Nov 2020 09:38:35 GMT

I have a stock dataset. I would like to create 1 extra row, which is the date, after the last date of the stock in the dataset. So for example, my dataset contains prices for stock IBM from 20190102 until 20190630. I want to create 1 extra row for the date 20190701. One extra requirement is that it cannot be a holiday, i.e. It has to be a business date.

I have a dataset called TRADINGDAYS that has 1 column of dates that are business dates. it looks like this. The dataset is attached

Begin 
20190102
20190103
......
20191230

Re: How to create an extra row after the last row of each BY group?

PeterClemmensen — Mon, 09 Nov 2020 09:00:52 GMT

The easy part can be solved like this.

Post your tradingdays data if you want that included as well

data want;
   do until (last.stock);
      set sashelp.stocks;
      by stock;
      output;
   end;
   date + 1;
   output;
run;

Re: How to create an extra row after the last row of each BY group?

somebody — Mon, 09 Nov 2020 09:39:26 GMT

Thanks. I edited my question and attached the tradingdays dataset. can you please have a look and incorporate that ?

Re: How to create an extra row after the last row of each BY group?

PeterClemmensen — Mon, 09 Nov 2020 09:55:14 GMT

Ok, just needed the structure. Here, I created a very simple replica of your tradingdays data set. It is just the day after the last date for each atock in sashelp.stocks.

See if it works for you

data tradingdays;
input begin :ddmmyy10.;
format begin ddmmyy10.;
datalines;
02/08/1986
run;


data want(drop=begin);

   dcl hash h(dataset : "tradingdays(rename=begin=date)");
   h.definekey("date");
   h.definedone();

   do until (last.stock);
      set sashelp.stocks;
      by stock;
      output;
   end;

   do until (h.check());
      date + 1;
   end;
   
   output;
run;