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

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?

--
Paige Miller
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

13 REPLIES 13
Astounding
PROC Star

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
Diamond | Level 26

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?

--
Paige Miller
data_null__
Jade | Level 19

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

Reeza
Super User

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
Super User

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

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
forumsguy
Fluorite | Level 6

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
PROC Star

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
PROC Star

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
Barite | Level 11

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

---->-- ja karman --<-----
Astounding
PROC Star

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
Barite | Level 11

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

---->-- ja karman --<-----

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
  • 13 replies
  • 2274 views
  • 3 likes
  • 8 in conversation