Rolling regression using macro

Reply
Contributor ZZB
Contributor
Posts: 43

Rolling regression using macro

[ Edited ]

The format of the data set: ID  Date Year Month Day  Variable1  Variable2

The dataset has the daily observations for each ID. 

For each ID each month, I want to run a regression of Variable 1 on variable 2 based on recent 6-month daily observations.

How to use the macro to run this regression? It is more complicated than the monthly rolling regression. 

-------------------------------------------------------------------------

If I do some manipulation and get the following format:   IDNAME   Count   Variable1   Variable2

Here 'Count' measures the date from 1 to a number based on date of each ID (for example, 120). Each ID has different number of count.

Now I run a regression of Variable1 on Variable2 using only 6 counts, i.e., 1-6 for the first regression, 2-7 for the second, 3-8,....

How to write this macro for the regression. I am new to the macro. Thanks.

 

proc reg data=one noprint outest=results;

model variable1=variable2;

by IDNAME count;

run;

 

 

Respected Advisor
Posts: 3,251

Re: Rolling regression using macro


ZZB wrote:

The format of the data set: ID  Date Year Month Day  Variable1  Variable2

The dataset has the daily observations for each ID. 

For each ID each month, I want to run a regression of Variable 1 on variable 2 based on recent 6-month daily observations.

How to use the macro to run this regression? It is more complicated than the monthly rolling regression. 

Thanks very much! 

 

 


I doubt someone here will write such a macro for you.

 

The general steps, for you to try, are:

 

  1. select the proper 6 months of data from original data set
  2. perform regression
  3. store results
  4. repeat with next six month period
--
Paige Miller
Contributor ZZB
Contributor
Posts: 43

Re: Rolling regression using macro

Posted in reply to PaigeMiller

If I do some manipulation and get the following format:   IDNAME   Count   Variable1   Variable2

Here 'Count' measures the date from 1 to a number based on date of each ID (for example, 120). Each ID has different number of count.

Now I run a regression of Variable1 on Variable2 using only 6 counts, i.e., 1-6 for the first regression, 2-7 for the second, 3-8,....

How to write this macro for the regression. I am new to the macro. Thanks.

 

proc reg data=one noprint outest=results;

model variable1=variable2;

by IDNAME count;

run;

Respected Advisor
Posts: 3,251

Re: Rolling regression using macro

I don't see where you have asked a specific question.

 

You have asked for help writing a macro, but we don't know at what point in the process you are having trouble. Please be specific.

--
Paige Miller
Contributor ZZB
Contributor
Posts: 43

Re: Rolling regression using macro

Posted in reply to PaigeMiller

I totally have no idea on writing the macro in this case.

For example, in the second case, I do not know what is wrong with the following code

 

%macro estim

%do x=6 to %to count;  /* count varies for each IDNAME, some ID has a number of 100, some ID is 156 */

data temp; 

set one;

if &x-5<=count<=&x;

per=&x;

 

proc reg data=temp noprint outest=results;

model variable1=variable2;

by IDNAME per;

run;

 

 

Respected Advisor
Posts: 3,251

Re: Rolling regression using macro

So it would help if you showed the SASLOG as well as the code.

 

Nevertheless, a few things stand out.

 

semicolon is needed at the end of

 

%macro estim;

You need to use macro constructs in the %do statement

 

%do x=6 %to &count;

In order for this to work, you have to define what value &count has, I assume this is based upon the number of data points you have.

 

After the last line of code, you need to end the macro with %mend; and then call the macro.

 

%mend;
%estim
--
Paige Miller
Contributor ZZB
Contributor
Posts: 43

Re: Rolling regression using macro

Posted in reply to PaigeMiller

Thanks! But the problem is how to define the "count", which varies for each ID. Could you give an example of code in this case?

Respected Advisor
Posts: 3,251

Re: Rolling regression using macro

Yet you have written code that does not include the variable ID.

 

You can use PROC SUMMARY to determine the maximum value of count for each ID. You can use the PROC SUMMARY results to determine the value of the macro variable &COUNT. Or you could do this via PROC SQL.

 

The more I think about it, the more I think you should be doing this without macros, using data step code, PROC SORT and a BY statement in PROC REG.

 

 

--
Paige Miller
Super User
Posts: 23,951

Re: Rolling regression using macro

Posted in reply to PaigeMiller

1. Start by getting regression running for a single model

2. Expand your model and run it for three iterations. 

3. Look at what's changed between 1 & 2 and that will tell you what you need to change for your model. 

 

You can see some examples of this here: https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

 

And this is a commonly asked question, so if you search you should find results here or on lexjansen.com

 

 

Ask a Question
Discussion stats
  • 8 replies
  • 372 views
  • 0 likes
  • 3 in conversation