BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BCNAV
Quartz | Level 8

Hi all,

 

I am coming over from EViews so bear with me. I would like to run a loop over variables names that are then used as a forecast variable.

 

Here is what I have. It doesn't work, but it will give you the idea of what I am trying to do (there could be many airports in this example, I have chosen just two here).

 

Thanks in advance

-Bill

 

%macro forecast_hw;

%let airport1 = "CYVR";
%let airport2 = "CYYJ";

%do i = &airport1 %to &airport2;
       proc forecast data=EGTASK.AIRPORT_TS interval=month trend=2 alpha=0.05 method=winters seasons=month lead=12 nstart=max nsstart=max out=FCST_Winters_Auto_&i outest=Est_Winters_Auto_&i;
 
      id date;

      var &i.TCU8;

      where date <= '01Apr2017'd; run;

%end;
%mend;
%forecast_hw;

 

 

Here are the  errors...sorry for getting them:

 

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

&airport1

ERROR: The %FROM value of the %DO I loop is invalid.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:

&airport2

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro FORECAST_HW will stop executing.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Starting out with macro language (when you're not an experienced SAS programmer) is a daunting task, not usually recommended.  But assuming that the SAS code within your PROC works, here are some issues to attend to:

 

  • Macro language does not use quotes around the values of macro variables
  • Macro language %DO loops are limited, and can iterate over a range of numeric values only

Here's one way you might structure your macro:

 

%macro forecast_hw (airport_list=);

%local n i;

%do n = 1 %to %sysfunc(countw(&airport_list));

       %let i = %scan(&airport_list, &n);
       proc forecast data=EGTASK.AIRPORT_TS interval=month trend=2 alpha=0.05 method=winters seasons=month lead=12 nstart=max nsstart=max out=FCST_Winters_Auto_&i outest=Est_Winters_Auto_&i;
 
      id date;

      var &i.TCU8;

      where date <= '01Apr2017'd; run;

%end;
%mend;
%forecast_hw (airport_list=CYVR CYYJ)

 

In practice, you might want to change &i to some other, more meaningful name.  I left it that way only because I was too lazy to change it every place that it appears in the code.

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

Give us the slightest chance of helping you ... what doesn't work? What is the error message? Turn on the MPRINT option, run it again and show us the SASLOG.

--
Paige Miller
Astounding
PROC Star

Starting out with macro language (when you're not an experienced SAS programmer) is a daunting task, not usually recommended.  But assuming that the SAS code within your PROC works, here are some issues to attend to:

 

  • Macro language does not use quotes around the values of macro variables
  • Macro language %DO loops are limited, and can iterate over a range of numeric values only

Here's one way you might structure your macro:

 

%macro forecast_hw (airport_list=);

%local n i;

%do n = 1 %to %sysfunc(countw(&airport_list));

       %let i = %scan(&airport_list, &n);
       proc forecast data=EGTASK.AIRPORT_TS interval=month trend=2 alpha=0.05 method=winters seasons=month lead=12 nstart=max nsstart=max out=FCST_Winters_Auto_&i outest=Est_Winters_Auto_&i;
 
      id date;

      var &i.TCU8;

      where date <= '01Apr2017'd; run;

%end;
%mend;
%forecast_hw (airport_list=CYVR CYYJ)

 

In practice, you might want to change &i to some other, more meaningful name.  I left it that way only because I was too lazy to change it every place that it appears in the code.

BCNAV
Quartz | Level 8

thanks....if macro usage is not recommended, is there another way to accomplish this?

 

-Bill

PaigeMiller
Diamond | Level 26

to do this in PROC FORECAST without a macro, you could use the BY statement and then the analysis will execute for each airport.

--
Paige Miller
Astounding
PROC Star

Macro language is often the right tool for the job.  In this case, it might be best.  It's just that you have to learn two things at the same time when you are just starting out (SAS language and macro language).

 

Another approach would require that you structure your data differently.  You have different variable names for each of the airports.  If you were to create a narrower data set (separate observations for each airport) and add AIRPORT as a variable, you could process your data BY AIRPORT, with the same variable each time.  There could conceivably be just a single PROC, with no macro language.

Reeza
Super User

Rules for writing a macro - first start with working BASE SAS code. 

 

So once you have that code working, you can then start adding macro variables one at a time and testing it to make sure it works. Then you can post components so we can help you debug any issues.

What does your Base code look like and show several variables so we can understand how you want to loop this.

 

Others are correct, in that there are multiple ways to do things. One common 'trick' is to reformat the data so you can use BY processing and skip macro's entirely. 

 

 

mkeintz
PROC Star

Macro %DO loops only index over integer values - i.e.   %do I=1 %to 10;   or %do I= &x %to &y (if &x and &y are integers).

 

Now, in your case you could do this:

 

  %let airport_list= CYVR CYYJ ;

   %do W=1 %to %sysfunc(countw(&airport_list));

      %let I=%scan(&airport_list,&W,%str());

      ... the rest here ...

   %end;

 

But you can also do something not quite as opaque as %macro.  You can use an ordinary do loop inside a DATA _NULL_ step.  Using the PUT statement, write desired statements out to a temporary file, and then %include that file to run the statements.  A lot less macro learning, yet same flexibility for your problem.  In fact, more flexibility since you can loop over character values (as in "DO airport="CYVR","CYYR").

 

filename tmp temporary;

data _null_;
  file tmp;   /* Identify target for PUT statements */
  do airport="CYVR","CYYR";
     put "proc forecast data=egtask_airport_ts inverval=month trend=2 alpha=0.05"
/ " method=winters" easons=month lead=12 nstart=max nsstart=max ";
txt=cats("out=FCST_Winters_Auto_",airport); put txt; txt=cats("outest=Est_Winters_Auto_",airport,';'); put txt; put 'id date'; txt=cats(airport,"TCU8"); put "var " txt; put "where date <= '01Apr2017'd; run;"; end; run; options source2; /* Tell SAS to print out the INCLUDED statements */ %include tmp; /* Read and execute contents of TMP */

 

 

Note the slash in

   PUT "some text"

       /  "some more txt" ;

 

tells sas to advance one line between "some text" and "some more text".

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
BCNAV
Quartz | Level 8

Thanks for the macro help. Believe it or not, the macro usage is the easiest for me to understand, and I was able to explain it to colleagues as it is intuitive. So thanks! A bit more cumbersome than EViews for this type of step, but still fun.

 

It would take more effort to split out the data as the time series database has monthly data on airport activity and charges as separate variables for each airport (e.g., Month CYOW_Activity, CYOW_Charges, CYVR_Activity, CYVR_Charges, etc.). Not sure how it could be broken up easily to use the "by" command in proc forecast. If there are ideas for this let me know; always willing to learn.

 

Thanks to all for your assistance!

-Bill

ballardw
Super User

@BCNAV wrote:

Thanks for the macro help. Believe it or not, the macro usage is the easiest for me to understand, and I was able to explain it to colleagues as it is intuitive. So thanks! A bit more cumbersome than EViews for this type of step, but still fun.

 

It would take more effort to split out the data as the time series database has monthly data on airport activity and charges as separate variables for each airport (e.g., Month CYOW_Activity, CYOW_Charges, CYVR_Activity, CYVR_Charges, etc.). Not sure how it could be broken up easily to use the "by" command in proc forecast. If there are ideas for this let me know; always willing to learn.

 

Thanks to all for your assistance!

-Bill


If CYOW CYVR are airports then the transform to data with a structure like:

Month Airport Activity Charges

would allow use of BY AIRPORT processing after sorting by Airport (and likey Month). This is often referred to transforming from Wide to Long format and you will find dozens if not hundreds of examples on this website in one form or another.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 8901 views
  • 3 likes
  • 6 in conversation