BookmarkSubscribeRSS Feed
Jonate_H
Quartz | Level 8

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;

 

4 REPLIES 4
Reeza
Super User
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?

Jonate_H
Quartz | Level 8

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.

Reeza
Super User
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.

Jonate_H
Quartz | Level 8

I have made some progress, thank you for your hint.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 834 views
  • 0 likes
  • 2 in conversation