How to dynamically add macro variables to %LOCAL statement

[PaigeMiller]

Suppose I am creating a bunch of macro variables, and the number of macro variables can change depending on the input data set.

Example:

proc sql;

    select count(distinct salesman) into :nsales from dataset;

    select distinct salesman into :sales1-:sales%left(&nsales) from dataset;

quit;

If there are 4 salesmen in the dataset, I get 4 macro variables. If there are 5 salesmen in the dataset, I get 5 macro variables. And so on.

I would like all the macro variables thus created to be in a %LOCAL statement. Is there an easy way to do this?

[Astounding · Accepted solution]

Not too difficult ...

You're allowed to specify a macro variable name that will resolve, such as:

%local sales&i;

So you could run with this code:

%local i;

%do i=1 %to &nsales;

   %local sales&i;

%end;

The tricky part is that you can actually insert this code between your SELECT statements.  Each SELECT statement runs immediately, and the %DO loop also runs immediately.  So the statements would all run in the proper order.

Good luck.

[PaigeMiller]

Astounding wrote:

Not too difficult ...

You're allowed to specify a macro variable name that will resolve, such as:

%local sales&i;

So you could run with this code:

%local i;
%do i=1 %to &nsales;
   %local sales&i;
%end;

The tricky part is that you can actually insert this code between your SELECT statements.  Each SELECT statement runs immediately, and the %DO loop also runs immediately.  So the statements would all run in the proper order.

Good luck.

Now why didn't I think of that?

[data_null__]

It might be easier to use CALL SYMPUTX and specify the scope than to do all that looping.

[Reeza]

Or to create a single macro variable and loop through it in your macro list as I imagine you're doing a %do loop somewhere else using the counter and the macro variables created.

[Ksharp]

Those macro variables would be local, if you keep it in a macro, and those macro vaiables are not defined before.

[Jagadishkatam]

There is a automatic macro variable called sysmaxlong which has a values of 2147483647, this can be used to automatically generate n number of macro variables. And since we are generating the macro variables within the macro, they all will be local macro variables.

Please try the below code;

%macro test();

proc sql;

    select name into :name1-:name&sysmaxlong from sashelp.class;

quit;

%mend;

%test;

The above code will generate following local macro variables name1,name2,name3,name4 etc., till name19.

Thanks,

Jagadish

[jakarman]

I am sorry but the statement the that the "%do loop would run at the same time as the proc select" is incorrect.

SAS(R) 9.3 Macro Language: Reference (how the macro Processor compiles a macro defnition)

Be happy, the default creation scope is local.
The problem will arise when you need them to be global or have a conflicting global name. 

SAS(R) 9.3 Macro Language: Reference  (forcing a macro variable to be local - Examples of macro variablescopes)

These are just te doc-refs to underpin what Ksharp stated.

[forumsguy]

Is there a way to make these variables global. Why following code doesn't work

% macro m1;

%global sales&i;

%local i;

%do i=1 %to 5;

   %let sales&i=&I;

%end;

% mend;

% m1

% put &sales1;

[Astounding]

forumsguy,

Your code isn't working because &i doesn't exist at the time that the %GLOBAL statement attempts to execute.  Move the %GLOBAL statement inside the loop, before the %LET statement.

Good luck.

[Astounding]

Jaap Karman,

There must be another way to interpret the documentation.  Here's a test program that you can run (once leaving the comment statement in place, and once uncommenting it) to confirm that the program works as posted:

%macro test;

   data temp; do i=21 to 25; output; end; run;

proc sql noprint;

   select count(distinct i) into : n_vars from temp;

   %local i;

   %do i=1 %to &n_vars;

       %local sales&i;

   %end;

   %let n_vars = &n_vars;

  *select distinct i into : sales1 -: sales&n_vars from temp;

quit;

%put _user_;

%mend test;

options mprint;

%test

The %DO loop can be compiled ahead of time (at least partially since &n_vars is not yet known).  But it doesn't execute ahead of time.  That's a feature of PROC SQL ... the SELECT statements run right away without looking for a QUIT (or a RUN) statement.

[jakarman]

@Astounding,
I just has given you to the documentational links a I have done solving this kind of issue for many years...  Nearly about some 30 of them,
I was supporting SAS in a former big organization connecting Windows Unix and Mainframe together freeing the users/analists of this kind of issues.

All kind of macro issues are burned into my fingers. I referred KSharp as recognizing the value of his statement.  

As said and documented the local definition is not needed/ This is tested and used many times. You can test it by removing it, and it still will run.     

Aside the scope also macro quiting can be very challenging. Wanting to combine it with SAS/connect or SQL pass-thru is giving a lot other nice things. As a nice  sample I put on my personal site: Sample Securing Business keys and passwords (%keypsw)
It solves the issue of eliminating key/psw in code. By the way this macro knowledge is part of SAS -advanced. After all years I started to collect some of those certifications. You can find me on linkedin as you probably wish.     

[Astounding]

Jaap Karman,

You're absolutely right that INTO : creates %LOCAL variables automatically.  However, INTO : doesn't always have to create a new macro variable.  What if the %GLOBAL environment already contains a macro variable &SALES1 and you want the INTO : version of &SALES1 to appear in the %LOCAL table?  Then the %LOCAL statements are necessary.  It's never a bad idea to add %LOCAL statements to guard against this possibility.

[jakarman]

Astounding,

SAS is often using an approach like Lazy initialization - Wikipedia, the free encyclopedia By that you are right being more formal.

in the TTA glossary of istqb.org you can find the same approach of quality testing of technical software (code review).
As the macro-code defining the scope must run before they are used the number and must be known in advance.

The nice thing with proc SQL is it will fill them with naming conventions numbers added etc dependent of it contens. There is the contradiction.

And SAS is not Always correct in their handling. This one 31887 - Problems with macro variables Created in an AUTOEXEC.SAS file when executing SAS on a worksp... is an sample how surprising wrong it can be (experienced by hitting).