SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Macro Loop for Forecast Years

Reply
Occasional Contributor
Posts: 15

Macro Loop for Forecast Years

have a SAS program that has a base year of data and five forecast years. All the forecast years have the same calculations, but just use the prior year's data as the base year for calculations. I want to avoid copying the data 5 times and just changing the suffix of my variable names. It would be great to have just one forecast year and have a do loop or an array to change the suffix of the variable names.  Here is my current code and I attached my Log:

 

 *Start macro loop - takes two parameters, the first year of the dataset to be created and the last year;
Options Mprint Symbolgen;
%macro loop(year_start, year_end);

*loop over years;

%do year=&year_start %to &year_end;
*create macro variable for previous years;
%let prev_year=%eval(&year_start-1);

DATA SchoolAidFY&year_start.;
Set work.forecastinputs;


* COST PER PUPIL AMOUNTS;
L201_FY&start_year.= L203_FY&prev_year.;
L202_FY&start_year.= Round(SCPP*SPG_FY&start_year.,1);
L203_FY&start_year.= Sum(L201_FY&start_year.,L202_FY&start_year.);
L204_FY&start_year.= L206_FY&prev_year.;
L205_FY&start_year.= Round(TS_SCPP*SPG_FY&start_year.,0.01);
L206_FY&start_year.= Sum(L204_FY&start_year.,L205_FY&start_year.);
L207_FY&start_year.= L209_FY&prev_year.;
L208_FY&start_year.= Round(PD_SCPP*SPG_FY&start_year.,0.01);
L209_FY&start_year.= Sum(L207_FY&start_year.,L208_FY&start_year.);
L210_FY&start_year.= L212_FY&prev_year.;
L211_FY&start_year.= Round(EI_SCPP*SPG_FY&start_year.,0.01);
L212_FY&start_year.= Sum(L210_FY&start_year.,L211_FY&start_year.);
L213_FY&start_year.= L215_FY&prev_year.;
L214_FY&start_year.= Round(TL_SCPP*SPG_FY&start_year.,0.01);
L215_FY&start_year.= Sum(L213_FY&start_year.,L214_FY&start_year.);

Run;


%end;
*end of do loop;

%mend;
*end of macro;

%loop(year_start=17, year_end=20);


 

Super User
Posts: 17,912

Re: Macro Loop for Forecast Years

[ Edited ]

The macro parameter is year_start but you're using start_year (or maybe I did in my sample code Smiley Wink)

 

Make sure they're all the same, copy and paste is your friend...sometimes!

Occasional Contributor
Posts: 15

Re: Macro Loop for Forecast Years

Excellent!!! Good catch, I should have been looking closer.  That fixed it so i have an output now for FY17.  However, the other years are not being added to the dataset.  

Super User
Posts: 17,912

Re: Macro Loop for Forecast Years

Your question here is different than your original question. Do you want a single data set output or multiple output datasets?
Occasional Contributor
Posts: 15

Re: Macro Loop for Forecast Years

Correct, originally i thought i would have to have separate data set outputs, but if it is easier I am fine with everything being in a single data set output.

Super User
Posts: 5,093

Re: Macro Loop for Forecast Years

It looks like your loop is in the wrong place.  Each iteration of the loop takes FORECASTINPUTS as the incoming data.  To get everything in one output data set, try moving the DATA and SET statements to just before the %DO statement.

Occasional Contributor
Posts: 15

Re: Macro Loop for Forecast Years

This gives me an error that my statements are not valid or used out of proper order.  I attached the Log.

Super User
Super User
Posts: 7,413

Re: Macro Loop for Forecast Years

Can I ask you post some sample data in the form of a datastep and what the output should look like.  Seems to be a simple loop to my mind, create a dataset which is normalised, e.g. parameter, value - output.  Oy alternatively arrays, but you look like yuo could end up with quite a few columns which tends to be messy.  Without seeing data I can't tell.

Occasional Contributor
Posts: 15

Re: Macro Loop for Forecast Years

Here is the Sample data.  It will have many columns in the end.

Attachment
Super User
Super User
Posts: 7,413

Re: Macro Loop for Forecast Years

Well, that data is missing some of the used variables.  However I can provide an example:

data inter;
  set tmp.exampleforecast;
  array l201{7} l201_fy16-l201_fy22;
  array l202{7} l202_fy16-l202_fy22;
  array l203{7} l203_fy16-l203_fy22;
  array l204{7} l204_fy16-l204_fy22;

/* the other 8 here */
  do i=2 to 7; /* as 1 does not have previous */
    1201{i}=1203{i-1};
    1202{i}=round(scpp*spg_fy...); /* these variables are not in the data you supplied */
    1203{i}=...
  end;
run;

 

Above I create an array for each of the 12 sections.  For these groups I then loop over and perform the given calculation using the incrementor i and point the calculation at i-1, or the previous year. Hence I start from 2 rather than 1 as there is no previous from the first year.  You can play around with the above, its quite simple when you understand the mechanics of the arrays.

Super User
Posts: 17,912

Re: Macro Loop for Forecast Years

Like I stated above, your question here is being phrased different that it was originally stated.

@ballardw is correct regarding moving the loop to inside the data step, but @rw9 is more correct (?) in that an array is a better solution if you want a single data set. If you had wanted multiple data sets, as in your original question, then the macro was required. But you've edited that question so I can't even link back to it....

 

 

 

%macro loop(year_start, year_end);

*loop over years;



DATA SchoolAidFY&year_start.;
Set work.forecastinputs;

%do year=&year_start %to &year_end;
*create macro variable for previous years;
%let prev_year=%eval(&year_start-1);

* COST PER PUPIL AMOUNTS;
L201_FY&start_year.= L203_FY&prev_year.;
L202_FY&start_year.= Round(SCPP*SPG_FY&start_year.,1);
L203_FY&start_year.= Sum(L201_FY&start_year.,L202_FY&start_year.);
L204_FY&start_year.= L206_FY&prev_year.;
L205_FY&start_year.= Round(TS_SCPP*SPG_FY&start_year.,0.01);
L206_FY&start_year.= Sum(L204_FY&start_year.,L205_FY&start_year.);
L207_FY&start_year.= L209_FY&prev_year.;
L208_FY&start_year.= Round(PD_SCPP*SPG_FY&start_year.,0.01);
L209_FY&start_year.= Sum(L207_FY&start_year.,L208_FY&start_year.);
L210_FY&start_year.= L212_FY&prev_year.;
L211_FY&start_year.= Round(EI_SCPP*SPG_FY&start_year.,0.01);
L212_FY&start_year.= Sum(L210_FY&start_year.,L211_FY&start_year.);
L213_FY&start_year.= L215_FY&prev_year.;
L214_FY&start_year.= Round(TL_SCPP*SPG_FY&start_year.,0.01);
L215_FY&start_year.= Sum(L213_FY&start_year.,L214_FY&start_year.);

%end;
Run;


%end;
*end of do loop;

%mend;
*end of macro;

 

Occasional Contributor
Posts: 15

Re: Macro Loop for Forecast Years

I did not realized the change from single to multiple data sets.  It would be best if i have multiple data sets because my code has over 750 lines on calculations.  I am still getting errors in my log.  So here is my new code based on @Reeza comments and moving the Data step to before the %do:  

%macro loop(year_start, year_end);


*loop over years;



DATA SchoolAidFY&year_start.;
Set work.SchoolAidFY&prev_year.;

  %do year=&year_start %to &year_end;
  *create macro variable for previous years;
  %let prev_year=%eval(&year_start-1);

/*DATA SchoolAidFY&year_start.;*/
/*Set work.forecastinputs;*/




* COST PER PUPIL AMOUNTS;						
L201_FY&year_start.=	L203_FY&prev_year.;								
L202_FY&year_start.=		Round(SCPP*SPG_FY&year_start.,1);			
L203_FY&year_start.=		Sum(L201_FY&year_start.,L202_FY&year_start.);					
L204_FY&year_start.=	L206_FY&prev_year.;						
L205_FY&year_start.=		Round(TS_SCPP*SPG_FY&year_start.,0.01);		
L206_FY&year_start.=		Sum(L204_FY&year_start.,L205_FY&year_start.);				
L207_FY&year_start.=	L209_FY&prev_year.;						
L208_FY&year_start.=		Round(PD_SCPP*SPG_FY&year_start.,0.01);		
L209_FY&year_start.=		Sum(L207_FY&year_start.,L208_FY&year_start.);			
L210_FY&year_start.=	L212_FY&prev_year.;						
L211_FY&year_start.=		Round(EI_SCPP*SPG_FY&year_start.,0.01);		
L212_FY&year_start.=		Sum(L210_FY&year_start.,L211_FY&year_start.);				
L213_FY&year_start.=	L215_FY&prev_year.;						
L214_FY&year_start.=		Round(TL_SCPP*SPG_FY&year_start.,0.01);		
L215_FY&year_start.=		Sum(L213_FY&year_start.,L214_FY&year_start.);					

 Run;


  %end;
  *end of do loop;

  %mend;
  *end of macro;   


 %loop(year_start=17, year_end=22);
Super User
Posts: 17,912

Re: Macro Loop for Forecast Years

If you've changed your SET statement to take the previous years data, use my original code with the do loop OUTSIDE of the data step. You've switched back and that change is no longer valid.
Super User
Posts: 5,093

Re: Macro Loop for Forecast Years

Your RUN; statement also needs to be moved, to later in the program.  Right now it is part of the macro loop, so the program generates multiple RUN; statements.  Move it outside of the loop, to right after the program calls the looping macro.

Occasional Contributor
Posts: 15

Re: Macro Loop for Forecast Years

Excellent.  Seems to be working now, but still getting only the Start Year Data Set:

 

*Start macro loop - takes two parameters, the first year of the dataset to be created and the last year;

%macro loop(year_start, year_end);


*loop over years;

  %do year=&year_start %to &year_end;
  *create macro variable for previous years;
  %let prev_year=%eval(&year_start-1);

DATA SchoolAidFY&year_start.;
Set work.schoolaidfy&prev_year.;




* COST PER PUPIL AMOUNTS;						
L201_FY&year_start.=	L203_FY&prev_year.;								
L202_FY&year_start.=		Round(SCPP*SPG_FY&year_start.,1);			
L203_FY&year_start.=		Sum(L201_FY&year_start.,L202_FY&year_start.);					
L204_FY&year_start.=	L206_FY&prev_year.;						
L205_FY&year_start.=		Round(TS_SCPP*SPG_FY&year_start.,0.01);		
L206_FY&year_start.=		Sum(L204_FY&year_start.,L205_FY&year_start.);				
L207_FY&year_start.=	L209_FY&prev_year.;						
L208_FY&year_start.=		Round(PD_SCPP*SPG_FY&year_start.,0.01);		
L209_FY&year_start.=		Sum(L207_FY&year_start.,L208_FY&year_start.);			
L210_FY&year_start.=	L212_FY&prev_year.;						
L211_FY&year_start.=		Round(EI_SCPP*SPG_FY&year_start.,0.01);		
L212_FY&year_start.=		Sum(L210_FY&year_start.,L211_FY&year_start.);				
L213_FY&year_start.=	L215_FY&prev_year.;						
L214_FY&year_start.=		Round(TL_SCPP*SPG_FY&year_start.,0.01);		
L215_FY&year_start.=		Sum(L213_FY&year_start.,L214_FY&year_start.);					




  %end;
  *end of do loop;

  %mend;
  *end of macro;   


 %loop(year_start=17,year_end=19);

Run;
Ask a Question
Discussion stats
  • 16 replies
  • 1344 views
  • 1 like
  • 4 in conversation