Hello good people. I am very new to SAS. I have the following sample dataset.
data have;
input Permno$ date : mmddyy10. excess_ret mkt_rf SMB HML RMW CMA;
format date mmddyy10.;
cards;
10375 03/22/04 -0.013158 -1.42 -0.6 -0.22 0.67 -0.7
10375 03/23/04 0.000108 -0.09 0.4 0.02 0.46 -0.19
10375 03/24/04 -0.011773 -0.27 -0.18 -0.12 -0.23 0.14
10375 03/25/04 0.010224 1.68 0.56 -0.19 -1.14 0.76
10375 03/26/04 -0.014569 0 0.36 0.36 -0.12 0.2
10375 03/29/04 0.028457 1.34 0.52 -0.22 -0.27 0.27
10375 03/30/04 -0.00022 0.47 0.49 0.15 -0.12 0.13
10375 03/31/04 0.008289 0 0.22 0.22 0.4 0.02
10375 04/01/04 -0.00165 0.64 0.19 0.04 -0.45 0.14
10397 03/22/04 0.00705 -1.42 -0.6 -0.22 0.67 -0.7
10397 03/23/04 0.007475 -0.09 0.4 0.02 0.46 -0.19
10397 03/24/04 0.014368 -0.27 -0.18 -0.12 -0.23 0.14
10397 03/25/04 0.025178 1.68 0.56 -0.19 -1.14 0.76
10397 03/26/04 0.002701 0 0.36 0.36 -0.12 0.2
10397 03/29/04 -0.012705 1.34 0.52 -0.22 -0.27 0.27
10397 03/30/04 -0.020012 0.47 0.49 0.15 -0.12 0.13
10397 03/31/04 -0.009249 0 0.22 0.22 0.4 0.02
10397 04/01/04 -0.001889 0.64 0.19 0.04 -0.45 0.14
11618 03/22/04 -0.008181 -1.42 -0.6 -0.22 0.67 -0.7
11618 03/23/04 -0.001691 -0.09 0.4 0.02 0.46 -0.19
11618 03/24/04 0.002283 -0.27 -0.18 -0.12 -0.23 0.14
11618 03/25/04 0.037623 1.68 0.56 -0.19 -1.14 0.76
11618 03/26/04 -0.008995 0 0.36 0.36 -0.12 0.2
11618 03/29/04 0.020096 1.34 0.52 -0.22 -0.27 0.27
11618 03/30/04 0.008157 0.47 0.49 0.15 -0.12 0.13
11618 03/31/04 0.036101 0 0.22 0.22 0.4 0.02
11618 04/01/04 -0.031007 0.64 0.19 0.04 -0.45 0.14
;
run;
This is a small subset of my dataset. As you can see, I have multiple Permno and each Permno has multiple and equal numbers of days of observation. I am trying to run a loop in which:
1) all observations for each Permno will be extracted.
2) Then I want to run a regression for extracted Permno observations and store the coefficients in a separate table.
3) Repeat this process for the next Permno and store the coefficients in the same table.
Currently, I can extract rows and run a regression using the following codes:
data test2;
set have;
where Permno = "11618";
run;
proc reg data=test2 noprint
outest= PE2; /* save parameter estimates */
model excess_ret = MKT_RF SMB HML RMW CMA;
quit;
But I want to do this in a loop/macro because I have thousands of Permno.
The output for the sample dataset would look something like this:
I have made some edits to the output by myself; however, I would like it to look like the picture.
I truly appreciate your kindness and support as I am going through a tough time. Thank you.
Hello @Ata_Rabbi and welcome to the SAS Support Communities!
Use BY-group processing, not macro loops, to apply a procedure to observations which are grouped together and identified by the values of one or more variables.
proc reg data=have noprint outest= PE2(drop=_model_); /* save parameter estimates */ by permno; model excess_ret = MKT_RF SMB HML RMW CMA; quit;
If dataset HAVE is not sorted, but only grouped by PERMNO, add the NOTSORTED option to the BY statement:
by permno notsorted;
Hello @Ata_Rabbi and welcome to the SAS Support Communities!
Use BY-group processing, not macro loops, to apply a procedure to observations which are grouped together and identified by the values of one or more variables.
proc reg data=have noprint outest= PE2(drop=_model_); /* save parameter estimates */ by permno; model excess_ret = MKT_RF SMB HML RMW CMA; quit;
If dataset HAVE is not sorted, but only grouped by PERMNO, add the NOTSORTED option to the BY statement:
by permno notsorted;
Thank you @FreelanceReinh . It was so simple that I now feel silly. But thank you so much for figuring out for me. Truly appreciate your support and time.
Hello @Ata_Rabbi it is not correct in this case to mark your answer correct, as you did not provide an answer. You need to mark the answer from @FreelanceReinh as correct, because it is the correct answer.
Thank you @PaigeMiller for pointing that out. I have corrected the error and accepted @FreelanceReinh 's answer as the solution. Please excuse my mistake as I am new to this forum.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.