How to create this macro?

[bd_user_10]

Hi everyone,

I am doing an event study where I have over 5500 events. I would like to apply these events to all US firms from 1990 to 2019 period. The return data file for that period is about 1.3 gigabytes. As you can understand, this creating a massive problem with the file size. Therefore, I would like to create a macro that can do the following commands for one-event at a time and store the data.

proc sql;

create table ret_data1 as select * from ret_data,eventdate;   /* it should select one event at a time */

quit;

data ret_data1;

set ret_data1;

before= DATE< EVTDATE;

proc sort data= ret_data1;

by PERMNO EVTDATE DATE;

proc means data= ret_data1 noprint;

by PERMNO EVTDATE;

output out=norets (drop=_type_ _freq_) sum(before)=_bef_sum;

data ret_data2;

merge ret_data1(drop=before) norets;

by PERMNO EVTDATE;

if first.EVTDATE then calday=-_bef_sum-1;

calday+1;

run;

data ret_data2;

set ret_data2;

drop _bef_sum;

run;

data ret_data_11;

set ret_data2;

if -1<=calday<=1;

run;

proc sql;

drop table ret_data1 ret_data2 norets;

quit;

Once all events are done, I would like to compile them in one file. Can someone please help me with this? Thanks in advance for your help.

I have attached the sample data to test the results.

[PaigeMiller]

So, are you saying that you actually tried to run this code with BY statements, and your computer could not handle it because of the size of the data set? If so, what is the exact error you are getting, and which PROC or DATA step causes the error?

Or are you saying you didn't try it?

[bd_user_10]

Hi PaigeMiller, I have tried in my computer but my file size is becoming gigantic. Since for each event, data is repeating for all firms and all observations, it's becoming a super big file. That's why i think macro would be a good way to go because it will do the job for one event at a time and the file size would be around 1.4gig. I would appreciate your help on this.

[PaigeMiller]

I'm not sure this answers my question. Yes, I understand the file size is gigantic. But does this cause an ERROR in SAS? If so, be specific, tell me what the error is and what step causes the error.

Again, I don't see a direct answer to my question "are you saying you didn't try it?"

[ballardw]

Hint: provide very small example data sets and what you expect the result for that example data to be. (one reason to use small sets). The example data needs to behave like your full data as far as the variables of interest are concerned and should provide any special cases that may arise.

One thing that leaps out is of these three data steps only one is needed:

data ret_data2;
merge ret_data1(drop=before) norets;
by PERMNO EVTDATE;
if first.EVTDATE then calday=-_bef_sum-1;
calday+1;
run;
 
data ret_data2;
set ret_data2;
drop _bef_sum;
run;
 
data ret_data_11;
set ret_data2;
if -1<=calday<=1;
run;

Could be replaced with

data ret_data_11;
   merge ret_data1(drop=before) norets;
   by PERMNO EVTDATE;
   if first.EVTDATE then calday=-_bef_sum-1;
   calday+1;
   drop _bef_sum;
   if -1<=calday<=1;
run;

I am not really sure how this is "applying" events.

One of your file size issues may be coming from

proc sql;
create table ret_data1 as select * from ret_data,eventdate;   /* it should select one event at a time */
quit;

because that proc sql creates a cartesian product of the data. That means each record from each data set is matched up with each record from the other data set.

It appears that perhaps a more controlled JOIN using the date and eventdate variables would be in order but I'm not going to try to guess what the rule(s) might be.

BTW, if you are going to delve into macro programming you want to get into the habit of always using the proper statement to end a procedure or data step. Macro coding and branches could with poor logic result in having statements appear to be part of an unclosed procedure or data step and you can spend a lot of time tracing down causes of some very peculiar at first look error messages.