## How to automate my calculation

Frequent Contributor
Posts: 130

# How to automate my calculation

[ Edited ]

For the attached sample data, I would like to do the following calculations. I tried to modified the code with "hash" from answer for my prior post, but I didn't make it work. Thanks a lot for your kindly help!

``````*merge sub_200709 with sub_199001, sub_199002 .... sub_200708 separately, run regression and then combine beta together;
*merge sub_200710 with sub_199001, sub_199002 .... sub_200709 separately, run regression and then combine beta together;
*merge sub_200711 with sub_199001, sub_199002 .... sub_200710 separately, run regression and then combine beta together;
*repeat the process until reach the following;
*merge sub_200909 with sub_199001, sub_199002 .... sub_200908 separately, run regression and then combine beta together;

*example calculation is as following;

*200709 with 199001;
data sub_200709;
set sample;
if date=200709;
rename date=y_date;
rename value=y_value;
rename order=y_order;
proc sort;
by id;
run;

data sub_199001;
set sample;
if date=199001;
rename date=x_date;
rename value=x_value;
rename order=x_order;
proc sort;
by id;
run;

data sub_200709_199001;
length order1 \$ 13 order2 \$ 7;
merge sub_200709 sub_199001;
by id;
order1=cats(y_date, '_', x_date);
order2=cats(y_order, '_', x_order);
run;

proc reg data=sub_200709_199001 outest=beta_200709_199001 tableout ADJRSQ edf noprint;
model1: model y_value = x_value/white;
run;
quit;

data beta_200709_199001;
length order1 \$ 13 order2 \$ 7;
set beta_200709_199001;
order1="200709_199001";
order2="213_1";
run;

*200709 with 199002;
data sub_200709;
set sample;
if date=200709;
rename date=y_date;
rename value=y_value;
rename order=y_order;
proc sort;
by id;
run;

data sub_199002;
set sample;
if date=199002;
rename date=x_date;
rename value=x_value;
rename order=y_order;
proc sort;
by id;
run;

data sub_200709_199002;
length order1 \$ 13 order2 \$ 7;
merge sub_200709 sub_199002;
by id;
order1=cats(y_date, '_', x_date);
order2=cats(y_order, '_', x_order);
run;

proc reg data=sub_200709_199002 outest=beta_200709_199002 tableout ADJRSQ edf noprint;
model1: model y_value = x_value/white;
run;
quit;

data beta_200709_199002;
length order1 \$ 13 order2 \$ 7;
set beta_200709_199002;
order1="200709_199002";
order2="213_2";
run;

*merge all beta results for 200709;
data beta_200709;
set beta_200709_199001 beta_200709_199002;*the complete result for beta_200709 should include beta_200709_199001 until beta_200709_200708;````run;````

Super User
Posts: 23,778

## Re: How to automate my calculation

You can look at the pages here to figure out how to start creating a macro program.

You may want to add an append at the end of the program to append your beta's.

proc append base=beta data=beta_&Years force;
run;

Though I have to ask why can't you do the merge with your full dataset and then use proc reg with a BY statement?

Frequent Contributor
Posts: 130

## Re: How to automate my calculation

Thank you Reeza for your helpful inputs! I was thinking that proc reg by group does not help to solve the problem, since I regress one period data on another prior period. I have to found another way to solve the problem.

Super User
Posts: 23,778

## Re: How to automate my calculation

I don't see how that's an issue. Merge, most likely using PROC SQL instead, on any period that's less than the period you're analyzing. You'll get multiple records, which is what you expect and group by for regressions.

Frequent Contributor
Posts: 130