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;
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.
I have made some progress, thank you for your hint.
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.
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.