topic Re: How to dynamically add macro variables to %LOCAL statement in SAS Programming

How to dynamically add macro variables to %LOCAL statement

PaigeMiller — Tue, 09 Jul 2013 18:47:35 GMT

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?

Re: How to dynamically add macro variables to %LOCAL statement

Astounding — Tue, 09 Jul 2013 19:05:56 GMT

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.

Re: How to dynamically add macro variables to %LOCAL statement

data_null__ — Tue, 09 Jul 2013 20:09:32 GMT

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

Re: How to dynamically add macro variables to %LOCAL statement

Reeza — Tue, 09 Jul 2013 20:24:51 GMT

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.

Re: How to dynamically add macro variables to %LOCAL statement

Ksharp — Wed, 10 Jul 2013 01:13:40 GMT

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

Re: How to dynamically add macro variables to %LOCAL statement

Jagadishkatam — Wed, 10 Jul 2013 04:42:03 GMT

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

Re: How to dynamically add macro variables to %LOCAL statement

PaigeMiller — Wed, 10 Jul 2013 12:18:16 GMT

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?

Re: How to dynamically add macro variables to %LOCAL statement

jakarman — Thu, 11 Jul 2013 06:07:26 GMT

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.

Re: How to dynamically add macro variables to %LOCAL statement

forumsguy — Thu, 11 Jul 2013 16:50:43 GMT

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;

Re: How to dynamically add macro variables to %LOCAL statement

Astounding — Thu, 11 Jul 2013 19:50:45 GMT

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.

Re: How to dynamically add macro variables to %LOCAL statement

Astounding — Thu, 11 Jul 2013 19:53:35 GMT

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.

Re: How to dynamically add macro variables to %LOCAL statement

jakarman — Thu, 11 Jul 2013 20:13:14 GMT

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

Re: How to dynamically add macro variables to %LOCAL statement

Astounding — Thu, 11 Jul 2013 20:25:25 GMT

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.

Re: How to dynamically add macro variables to %LOCAL statement

jakarman — Thu, 11 Jul 2013 20:45:33 GMT

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 workspace server is an sample how surprising wrong it can be (experienced by hitting).