topic Re: Looping with Variable Names in SAS Programming

Looping with Variable Names

BCNAV — Wed, 07 Jun 2017 17:45:34 GMT

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.

Re: Looping with Variable Names

PaigeMiller — Wed, 07 Jun 2017 17:43:04 GMT

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.

Re: Looping with Variable Names

Astounding — Wed, 07 Jun 2017 18:03:44 GMT

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.

Re: Looping with Variable Names

BCNAV — Wed, 07 Jun 2017 18:26:11 GMT

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

-Bill

Re: Looping with Variable Names

PaigeMiller — Wed, 07 Jun 2017 18:32:03 GMT

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

Re: Looping with Variable Names

Astounding — Wed, 07 Jun 2017 18:31:40 GMT

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.

Re: Looping with Variable Names

Reeza — Wed, 07 Jun 2017 19:07:53 GMT

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.

Re: Looping with Variable Names

mkeintz — Wed, 07 Jun 2017 20:22:38 GMT

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

Re: Looping with Variable Names

BCNAV — Wed, 07 Jun 2017 22:39:30 GMT

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

Re: Looping with Variable Names

Reeza — Thu, 08 Jun 2017 00:59:08 GMT

Explanation:

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-run-multiple-regressions/ta-p/223663

Example:

https://communities.sas.com/t5/SAS-Procedures/Macro-for-multiple-GARCH-Regressions/m-p/184279/highlight/true#M46872

Re: Looping with Variable Names

ballardw — Thu, 08 Jun 2017 15:21:04 GMT

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