topic Re: How to create event month? in SAS Programming

How to create event month?

bd_user_10 — Sat, 15 Sep 2018 13:58:17 GMT

Hi Everyone,

I have following dataset:

Date rel_day

2006-03-01 -1842

2006-03-02 -1841

2006-03-03 -1840

. .

2011-03-07 0

. .

2014-04-01 1111

2014-04-02 1112

I would like to create a column event_month where every 30 days from the event day would be a either +month or a -month. I have used the following codes to create +month or -month but only for a year.

sample code:

if relday>=-30 and relday<=-1 then event_month =-1;

if relday = 0 then event_month = 0;

if relday>=1 and relday<=30 then event_month = 1;

I need to create event_month for all dates in the dataset. Can someone please help? I have got many ISIN in the dataset.

Thanks.

Re: How to create event month?

data_null__ — Sat, 15 Sep 2018 15:17:26 GMT

data temp;
   input Date:yymmdd10. rel_day;
   cards;
2006-03-01     -1842
2006-03-02     -1841
2006-03-03     -1840
2011-03-07        0
2014-04-01     1111
2014-04-02     1112
;;;;
   run;
data temp;
   set temp(where=(rel_day eq 0));
   event=date;
   do while(not eof);
      set temp end=eof;
      relchk = event - date;
      relmon = intck('MONTH',event,date);
      output;
      end;
   stop;
   format event date yymmdd10.;
   run;
proc print;
   run;

Re: How to create event month?

bd_user_10 — Sat, 15 Sep 2018 15:48:20 GMT

Hi data_null_,
Thanks for the code. However, there is a little issue. Your code is creating the relmon based on the end of the month from the event day. For example, if there is an event on 31st of March then from 1st April there is a new month (relmon=1). Similarly, if the event is on 1st of March then the whole month of March is relmon=0. I need to start relmon from +1 or -1 day from the event and it has to continue for next 30 days. Basically, in every thirty days from the event there will be a new month. Please let me know if you need any more clarification. Thanks again.

Re: How to create event month?

MarkWik — Sat, 15 Sep 2018 16:20:45 GMT

Your input sample and the required output sample is not clear. You can post a better and clearer one, and of course explaining the logic along with it

Re: How to create event month?

bd_user_10 — Sat, 15 Sep 2018 17:47:30 GMT

Hi Mark, as I indicated in my post, I want to create 30 days event window from +1 and -1 event day for all rel_day. Here is the sample conditions
if rel_day>=-60 and rel_day<=-31 then event_month =-2;
if rel_day>=-30 and rel_day<=-1 then event_month =-1;
if rel_day = 0 then event_month = 0;
if rel_day>=1 and rel_day<=30 then event_month = 1;
if rel_day>=31 and rel_day<=60 then event_month = 2;

Re: How to create event month?

Reeza — Sat, 15 Sep 2018 17:57:29 GMT

It may help if you take the sample data from data _null_ and show the expected output from that small sample data set.