Do loop over proc step - How to store the results of all the iterations

[Sabeth]

Dear SAS Community,

I would like to calculate the mean of a variable for each year and month in a specific time period. I programed a do loop over a proc summary step to do so. 

The problem is that I only get back the results for the last iteration of the loop. But I need the results of all the iteration.

I suppose that the loop overwrites the results of former iterations. Do you know a possibility to store the results of each iteration? Or would you suggest a data step with an output statement?

The dataset includes some numeric variables that represent the date as well as a duration variable. For example: 

ID eyearmax emonthmax eyear eyear duration

1  2007      6         2007     3      10

1  2007      6         2007     1      15

1  2007      6         2007     6      28

2  2008      5         2007     5      10

3  2007     11         2006     7      11

3  2007     11         2007    11      19

4  2008      1         2008     1       7

%macro test (eyear=, emonth=,);

%DO eyear = 2007 %TO 2009;
%DO emonth = 1 %TO 12;

proc summary data=work.want (where=(eyearmax<=&eyear. and emonthmax<=&emonth.)) nway sum;
class id;
output out=work.duration
sum(duration)=;
run;

proc summary data=work.duration mean;
output out=work.meanduration (drop=_freq_ _type_)
mean (duration)=;
run;

%end;
%end;

%mend test;
%test;

Thank you very much for your help!

Best regards,

Sabeth

[PaigeMiller]

It's not clear to me what you want, or if this line is correct:

(where=(eyearmax<=&eyear. and emonthmax<=&emonth.))

because then when &eyear is 2009 and &emonth is 1, you get the mean of all January data where year is less than 2009; in other words the mean of many different Januaries. When &emonth is 2, you get the mean of January and February over many months and many years. And so on. If that's what you want, you may need a macro.

But if you want what you said, which is "the mean of a variable for each year and month", that sounds like something different, and no macros are needed.

So which is it? What do you really want to do?

[Sabeth]

Thank you for your comment, Paige Miller.

My intention was to set a date (with the do Loop) and select all the objects with a prior date (not only January and February). Therefore, the where clause was not correct.

I converted the numeric variables eyear and emonth (eyearmax and eyearmonth) into  date variables edate (edatemax). But now I have no idea how to integrate them in the where clause as macrovariables. I have some problems with the format.   

%let edate=01/12/2008;

proc summary data=work.have (where=(edatemax < ("&edate",ddmmyy10.))) nway sum;

You mentioned that no macros are needed for my "Iteration Problem". Could you please explain how the problem could be solved without a macro?

Thank you in advance!

Sabeth

[PaigeMiller]

---

@Sabeth wrote:

Thank you for your44 comment, Paige Miller.

My intention was to set a date (with the do Loop) and select all the objects with a prior date (not only January and February). Therefore, the where clause was not correct.

I converted the numeric variables eyear and emonth (eyearmax and eyearmonth) into  date variables edate (edatemax). But now I have no idea how to integrate them in the where clause as macrovariables. I have some problems with the format.   

%let edate=01/12/2008;

proc summary data=work.have (where=(edatemax < ("&edate",ddmmyy10.))) nway sum;

---

So you want a macro to store all of these results in a dataset.

Something like this:

%macro do_all;
    %let date=%sysfunc(mdy(12,1,2006));
    %do %while(&date<%sysfunc(mdy(12,1,2009)));
         %let date=%sysfunc(intnx(month,&date,1,b));
         proc summary data=have(where=(edatemax<=&date)) nway;
             class id;
             var duration edatemax;
             output out=work.duration sum(duration)= max(edatemax)=;
         run;
         proc append base=all_duration new=duration;
         run;
    %end;
%mend;

proc datasets library=work nolist;
    delete all_duration;
run; quit;
%do_all

         

[Sabeth]

Thank you! The date macro variables and the proc append step solved my problem.

[ballardw]

Maybe

proc summary data=work.want nway ;
class id eyear emonth;
var duration  ;
output out=work.duration  sum= mean= /autoname;

run;

Which will create variables duration_sum and duration_mean to hold the statistics. The /autoname option suffixes a variable with the statistic to name the resulting output variable within the 32 character name limit.

If you want summaries by eyear and emonth without considering Id then remove the NWAY and use the desired values for the _type_ variable or add a types statement.

[Reeza]

These are moving averages, is that what you want? 

If so, I would recommend PROC EXPAND instead, if you have SAS/ETS. You can check using:

proc product_status;run;

If not, an example of wrapping this in a macro is here: 

https://gist.github.com/statgeek/e5e43ff45a4ba1f64d0873ff3bc35974

You can also look at the ODS OUTPUT option along with the PERSIST option. 

---

@Sabeth wrote:

Dear SAS Community,

I would like to calculate the mean of a variable for each year and month in a specific time period. I programed a do loop over a proc summary step to do so. 

The problem is that I only get back the results for the last iteration of the loop. But I need the results of all the iteration.

I suppose that the loop overwrites the results of former iterations. Do you know a possibility to store the results of each iteration? Or would you suggest a data step with an output statement?

The dataset includes some numeric variables that represent the date as well as a duration variable. For example: 

ID eyearmax emonthmax eyear eyear duration

1  2007      6         2007     3      10

1  2007      6         2007     1      15

1  2007      6         2007     6      28

2  2008      5         2007     5      10

3  2007     11         2006     7      11

3  2007     11         2007    11      19

4  2008      1         2008     1       7

%macro test (eyear=, emonth=,);

%DO eyear = 2007 %TO 2009;
%DO emonth = 1 %TO 12;

proc summary data=work.want (where=(eyearmax<=&eyear. and emonthmax<=&emonth.)) nway sum;
class id;
output out=work.duration
sum(duration)=;
run;

proc summary data=work.duration mean;
output out=work.meanduration (drop=_freq_ _type_)
mean (duration)=;
run;

%end;
%end;

%mend test;
%test;

Thank you very much for your help!

Best regards,

Sabeth

---

[Sabeth]

Thank you all for your support!