topic Re: Rolling regression using macro in SAS Programming

Rolling regression using macro

ZZB — Sun, 12 Nov 2017 13:57:21 GMT

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;

Re: Rolling regression using macro

PaigeMiller — Sun, 12 Nov 2017 11:55:43 GMT

@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:

select the proper 6 months of data from original data set
perform regression
store results
repeat with next six month period

Re: Rolling regression using macro

ZZB — Sun, 12 Nov 2017 13:56:33 GMT

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;

Re: Rolling regression using macro

PaigeMiller — Sun, 12 Nov 2017 14:24:25 GMT

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.

Re: Rolling regression using macro

ZZB — Sun, 12 Nov 2017 16:14:41 GMT

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;

Re: Rolling regression using macro

PaigeMiller — Sun, 12 Nov 2017 17:30:26 GMT

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

Re: Rolling regression using macro

ZZB — Sun, 12 Nov 2017 17:44:36 GMT

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?

Re: Rolling regression using macro

PaigeMiller — Sun, 12 Nov 2017 22:28:16 GMT

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.

Re: Rolling regression using macro

Reeza — Mon, 13 Nov 2017 00:36:45 GMT

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