Regression using a Macro

[Ata_Rabbi]

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.

[FreelanceReinh · Accepted solution]

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;

[Ata_Rabbi]

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.

[PaigeMiller]

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.

[Ata_Rabbi]

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.