turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- Macro Loop for Forecast Years

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2015 06:11 PM

I 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);

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-28-2015 06:17 PM - edited 09-28-2015 06:18 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-28-2015 06:33 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-28-2015 07:01 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-29-2015 08:50 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-28-2015 06:52 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

09-29-2015 08:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-29-2015 08:13 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-29-2015 09:51 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-29-2015 10:19 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-29-2015 10:24 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-29-2015 11:03 AM

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);
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-29-2015 12:11 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Parker1

09-29-2015 11:31 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

09-29-2015 02:03 PM

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;
```