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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.