Looping with Variable Names

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Looping with Variable Names

[ Edited ]

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.

 


Accepted Solutions
Solution
‎06-07-2017 06:34 PM
Super User
Posts: 5,518

Re: Looping with Variable Names

[ Edited ]

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


All Replies
Trusted Advisor
Posts: 1,933

Re: Looping with Variable Names

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.

Solution
‎06-07-2017 06:34 PM
Super User
Posts: 5,518

Re: Looping with Variable Names

[ Edited ]

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.

Contributor
Posts: 60

Re: Looping with Variable Names

Posted in reply to Astounding

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

 

-Bill

Trusted Advisor
Posts: 1,933

Re: Looping with Variable Names

[ Edited ]

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

Super User
Posts: 5,518

Re: Looping with Variable Names

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.

Super User
Posts: 19,872

Re: Looping with Variable Names

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. 

 

 

Trusted Advisor
Posts: 1,022

Re: Looping with Variable Names

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".

Contributor
Posts: 60

Re: Looping with Variable Names

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

Super User
Posts: 11,343

Re: Looping with Variable Names


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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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