topic Re: Declaring multiple global macro variables within a %macro with proc sql in SAS Programming

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

x2PSx — Mon, 20 Nov 2017 15:14:17 GMT

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 ();

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

Kurt_Bremser — Mon, 20 Nov 2017 15:19:02 GMT

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.

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

RW9 — Mon, 20 Nov 2017 15:24:27 GMT

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.

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

Astounding — Mon, 20 Nov 2017 15:29:02 GMT

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.