DATA Step, Macro, Functions and more

Declaring multiple global macro variables within a %macro with proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Declaring multiple global macro variables within a %macro with proc sql

Hello, 

 

I wrote this macro to create multiple macro variables that I will use later in my program. The code works juste fine, however if I want to call these macro variables later in open code, I need to make them global if I understand correctly. Is there a way to implement this easily in my code so that all these macro variables can be called later?

 

Thank you so much!

 

 

Here's my code:

 

 

%MACRO INPUTS_PARAMETERS 	();

		proc sql ;
			select Intercept
			into :Intercept
			from EXTLIB.COEFF_REFERENCE
		;

	%do i=1 %to %sysfunc(countw(&INPUTS_EF.));

		proc sql ;
			select T_%scan(&INPUTS_EF.,&i.)_M801010
			into :Coeff_%scan(&INPUTS_EF.,&i.)
			from EXTLIB.COEFF_REFERENCE
		;
			proc sql ;
				select Slope
				into :Slope_%scan(&INPUTS_EF.,&i.)
				from EXTLIB.TRANSFO_PARAMS_GLOBAL
				where Variable = "%scan(&INPUTS_EF.,&i.)_M801010"
			;
				proc sql ;
					select Midpoint
					into :Midpoint_%scan(&INPUTS_EF.,&i.)
					from EXTLIB.TRANSFO_PARAMS_GLOBAL
					where Variable = "%scan(&INPUTS_EF.,&i.)_M801010"
				;
	
	%end;

	%do i=1 %to %sysfunc(countw(&INPUTS_SEC.));

		proc sql ;
			select %scan(&INPUTS_SEC.,&i.)
			into :Coeff_%scan(&INPUTS_SEC.,&i.)
			from EXTLIB.COEFF_REFERENCE
		;
		quit;
	%end;
		


%MEND INPUTS_PARAMETERS;

%INPUTS_PARAMETERS ();

Accepted Solutions
Solution
‎11-20-2017 10:37 AM
Super User
Posts: 6,939

Re: Declaring multiple global macro variables within a %macro with proc sql

Have your macro generate a %GLOBAL statement, before the related PROC SQL executes.  (PROC SQL can populate global variables, but it cannot create global variables when used inside of a macro.)  For example:

 

%global Coeff_%scan(&INPUTS_EF.,&i.);

%global Slope_%scan(&INPUTS_EF.&i.);

 

There are a few places to insert such statements, but they'll work just fine as long as they precede the SQL INTO : that populates the variable.

View solution in original post


All Replies
Super User
Posts: 10,623

Re: Declaring multiple global macro variables within a %macro with proc sql

The first thing that comes to mind is using data steps with call symputx('name','value','g'); instead of the SQL's.

 

OTOH, creating lists of macro variables is something I tend to avoid. I rather create code with call execute from datasets, so I don't have to keep counts and so on.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,866

Re: Declaring multiple global macro variables within a %macro with proc sql

I would tend to keep data in datasets as much as possible, that is really the point of datasets, and having a large and complex data processing language such as Base SAS to be able to work with the data.  Macro has none of that and is just a text replacement tool.  As I can't see any of the code really, or what most of the macro variables resolve to its hard to say, but create a dataset:
PARAM RES

 

Then you can simply call symputx(param,res,'g') from a datastep, real easy.  Or just merge the dataset onto your data and avoid the whole macro part at all - remember its only useful for generating Base SAS code, if you can do it in Base SAS, no need to use macro.

Solution
‎11-20-2017 10:37 AM
Super User
Posts: 6,939

Re: Declaring multiple global macro variables within a %macro with proc sql

Have your macro generate a %GLOBAL statement, before the related PROC SQL executes.  (PROC SQL can populate global variables, but it cannot create global variables when used inside of a macro.)  For example:

 

%global Coeff_%scan(&INPUTS_EF.,&i.);

%global Slope_%scan(&INPUTS_EF.&i.);

 

There are a few places to insert such statements, but they'll work just fine as long as they precede the SQL INTO : that populates the variable.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 208 views
  • 0 likes
  • 4 in conversation