BookmarkSubscribeRSS Feed
Parker1
Fluorite | Level 6

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


 

16 REPLIES 16
Reeza
Super User

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!

Parker1
Fluorite | Level 6

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.  

Reeza
Super User
Your question here is different than your original question. Do you want a single data set output or multiple output datasets?
Parker1
Fluorite | Level 6

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.

Astounding
PROC Star

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.

Parker1
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Parker1
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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;

 

Parker1
Fluorite | Level 6

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);
Reeza
Super User
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.
Astounding
PROC Star

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.

Parker1
Fluorite | Level 6

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 16 replies
  • 3455 views
  • 1 like
  • 4 in conversation