topic Re: SAS Macro Do Loops in SAS Programming

SAS Macro Do Loops

finans_sas — Thu, 15 May 2014 13:10:31 GMT

Dear Community Members,

I have been trying to make the program below run, but so far I have failed. I have a dataset with multiple periods, and for each period, I want to run a bunch of models. The loops keep getting stock at per=1 (it won't go to the next one), and it will also give an error saying that model1 (and others) cannot be found.

I attached a sample of my dataset (2 periods and 2 models). In the full version, I have 5 periods and 24 models, and I would have to manually run this program 120 times. I would really appreciate your help with this issue.

%macro twostep;

%do per=1 %to 2;

%do m=1 %to 2;

%let model1= vret_msa mktrf100; *model specification;

%let model2= vret_msa mktrf100 ind_ret;

data perret&per; set 14_samplesas;

if period=&per;

proc sort data=perret&per; by permno myear; run;

/*Regressions*/

ods listing close;

ods output parameterestimates=pe&per&m;

proc reg data=perret&per;

by permno;

model ereturn_i =model&m;

run;

quit;

ods listing;

ods rtf file="C:\SAS\results\period&per model&m.rtf" style=journal;

proc means data=pe&per&m mean std t probt;

var estimate; class variable;

run;

*By variable by variable, it calculates the mean, standard deviation, t-statistics and p-value of the

coefficient estimates;

proc sort data=pe&per&m; by variable; run;

ods rtf close;

%end;

%mend;

%twostep;

Re: SAS Macro Do Loops

RW9 — Thu, 15 May 2014 13:22:20 GMT

Hi,

Well first issue I see is this line:

model ereturn_i =model&m;

Which is the one giving you that model1 not found. I think what you are attempting to do is to use the macro variable you defined earlier model1 which is vret_msa mktrf100. This isn't resolving, so the data code is resolving to try to find a variable called model1 which doesn't exist. Let me have a further look and suggest some code.

Re: SAS Macro Do Loops

kbohle — Thu, 15 May 2014 13:29:12 GMT

Hi,

I think you want to use the content of the macro variable 'model1' and 'model2' in the regression.

proc reg data=perret&per;

by permno;

model ereturn_i =model&m;

run;

Therefore please try to change it like the following:

proc reg data=perret&per;

by permno;

model ereturn_i =&model&&m;

run;

Re: SAS Macro Do Loops

RW9 — Thu, 15 May 2014 13:32:56 GMT

In addition to the issue, some suggestions:

The below uses call execute to directly generate the required code.

data _null_;
array models{2} $200. ("vret_msa mktrf100","vret_msa mktrf100 ind_ret");
do i=1 to 2;
    call execute('proc sql;
                    create table WORK.TMP as
                    select *
                    from    A.A14
                    where   PERIOD='||put(i,1.)||';
                  quit;

                  ods _all_ close;
                  ods output parameterestimates=pe'||put(i,1.)||strip(models{i})';
                  proc reg data=work.tmp;
                    by permno;
                    model ereturn_i='||strip(models{i})||';
                  run;
                  quit;
                  ods rtf file="s:\temp\rob\period_'||put(i,1.)||'_model_'||strip(models{i})||' style=journal;
                  proc means data=pe'||put(i,1.)||strip(models{i})';
                    var estimate;
                    class variable;
                  run;
                  ods rtf close;');
end;
run;

You could also use call execute to generate the code to call you macro like:

%macro twostep (period=,model=);

...
%mend twostep;

data _null_;
array models{2} $200. ("vret_msa mktrf100","vret_msa mktrf100 ind_ret");
do i=1 to 2;
call execute('%twostep (period='||put(i,1.)||',model='||strip(models{i})');');
end;
run;

Re: SAS Macro Do Loops

overmar — Thu, 15 May 2014 16:23:54 GMT

Change it to &&model&m having the second & at the m doesn't resolve it because there isn't a macro variable called &model, putting the second & in front of the model statement resolves it to &model1 or &model2 which is what finaans_sas was looking for.

Re: SAS Macro Do Loops

kbohle — Thu, 15 May 2014 16:44:17 GMT

Your right - sorry was a typo during entring the code!

Re: SAS Macro Do Loops

finans_sas — Thu, 15 May 2014 18:27:15 GMT

Thank you all for your help with this problem. After changing model name to &&model&m, the program started running smoothly. I just have one more question: This program generates multiple RTF documents. Is there a way to use something like "proc append" in ODS output inside the loop and create a single RTF?

Re: SAS Macro Do Loops

overmar — Thu, 15 May 2014 18:38:55 GMT

put the ods rtf close after the second %end statement so that the macro does everything before it closes out the the rtf. One word of caution however is you may want to add a title statement to your proc reg statement to output some kind of information so you at least know what each of the outputed tables are. But I can't remember if proc reg will take a title statement, so you may actually need to put the title statement outside of a proc and have it changed, but otherwise that should do what you want it to.

Re: SAS Macro Do Loops

jwillis — Thu, 15 May 2014 18:38:58 GMT

Finans,

Move your current ODS RTF statements outside the macro and add "ODS RTF startpage=now" where you have 'ODS RTF FILE...' in the macro.

Re: SAS Macro Do Loops

finans_sas — Thu, 15 May 2014 22:43:52 GMT

Thank you all once again for your help. I am going to start a new discussion on this ODS output issue as my original question was answered.