topic Proc SQL in macro in SAS Programming

Proc SQL in macro

Vahe_Mar — Fri, 11 Jan 2019 09:19:17 GMT

%macro sq (var1=, var2=, var3=, var4=);
proc sql;
select count (distinct USUBJID) INTO:&var1 from ADAE where EYE="A" and &var4;
select count (distinct USUBJID) INTO:&var2 from ADAE where EYE="B" and &var4;
select count (distinct USUBJID) INTO:&var3 from ADAE where &var4;
quit;

%put &var1 &var2 &var3
%mend sq;

%sq( var1=x11, var2=x21, var3=x31, var4=%str(AETERM^=""));

log is clear but program doesn't work .

Re: Proc SQL in macro

andreas_lds — Fri, 11 Jan 2019 10:50:25 GMT

Add

options mprint mlogic symbolgen;

before the macro is called, examine the log or post it here, but please post code and log using the {i} or running man icon to preserve formatting and increase readability.

You could add

%put _local_;

as first command in an macro to write all parameters with their values to the log.

Re: Proc SQL in macro

Vahe_Mar — Fri, 11 Jan 2019 11:33:37 GMT

Dear Andreas

about readability will do next time thanks.

There are log like this.

SYMBOLGEN: Macro variable VAR1 resolves to x11
MLOGIC(SQ): %LET (variable name is x11)
SYMBOLGEN: Macro variable V1 resolves to 11
SYMBOLGEN: Macro variable VAR2 resolves to x21
MLOGIC(SQ): %LET (variable name is x21)
SYMBOLGEN: Macro variable V2 resolves to 5
SYMBOLGEN: Macro variable VAR3 resolves to x31
MLOGIC(SQ): %LET (variable name is x31)
SYMBOLGEN: Macro variable V3 resolves to 14
MLOGIC(SQ): %PUT _local_
SQ V1 11
SQ V2 5
SQ V3 14
SQ VAR1 x11
SQ VAR2 x21
SQ VAR3 x31
SQ VAR4 AETERM""
SQ X11 11
SQ X21 5
SQ X31 14

but when I want to use like

&x11 and get 11 its not working.

Re: Proc SQL in macro

PaigeMiller — Fri, 11 Jan 2019 11:42:25 GMT

@Vahe_Mar wrote:

log is clear but program doesn't work .

What about it doesn't work? Show us the SASLOG. Show us the results you are getting and describe why they are wrong and what you expect.

Re: Proc SQL in macro

Vahe_Mar — Fri, 11 Jan 2019 11:46:54 GMT

"why they are wrong?" what you mean?

Re: Proc SQL in macro

ErikLund_Jensen — Fri, 11 Jan 2019 12:05:40 GMT

Hi Andreas_IDS

Yout code works as intended, except for two problems with the %put statement.

1. a missing semicolon in the %put statement.

2. When you put &var1, you get the content of &var1, which is X31. You must use &&&var1 to resolve further and get the value of

X31.

The noprint option on the proc sql statement is to prevent Proc SQL from generating procedure output in the output window for the 3 selects.

data adae;
	USUBJID = 1; EYE = 'A'; AETERM = ''; output;
	USUBJID = 1; EYE = 'A'; AETERM = 'X'; output;
	USUBJID = 2; EYE = 'B'; AETERM = ''; output;
	USUBJID = 2; EYE = 'B'; AETERM = 'X'; output;
	USUBJID = 2; EYE = 'B'; AETERM = 'Y'; output;
run;

%macro sq (var1=, var2=, var3=, var4=);
	proc sql noprint;
		select count (distinct USUBJID) INTO:&var1 from ADAE where EYE="A" and &var4;
		select count (distinct USUBJID) INTO:&var2 from ADAE where EYE="B" and &var4;
		select count (distinct USUBJID) INTO:&var3 from ADAE where &var4;
	quit;

	%put &&&var1 &&&var2 &&&var3;
%mend sq;
%sq( var1=x11, var2=x21, var3=x31, var4=%str(AETERM^=""));

Result:

1 1 2

Re: Proc SQL in macro

ErikLund_Jensen — Fri, 11 Jan 2019 12:11:00 GMT

Sorry Vahe_Mar

I put a wong name in my post. But the code is for you, not Andreas_IDS.

Re: Proc SQL in macro

PaigeMiller — Fri, 11 Jan 2019 13:18:17 GMT

@Vahe_Mar wrote:
"why they are wrong?" what you mean?

I am asking for a more detailed explanation. All you said was "doesn't work" and "not working" which tells us nothing.

Re: Proc SQL in macro

Vahe_Mar — Fri, 11 Jan 2019 13:25:28 GMT

after macro there are no issues, but when I want to use &x11 I have warning like " Apparent symbolic reference X11 not resolved" Actually I confused how to let &VAR1 ( X11) to var1 (macro derived in sql). And I want to use &X11 and have a value assigned in macro.

Re: Proc SQL in macro

PaigeMiller — Fri, 11 Jan 2019 13:28:43 GMT

Show us the entire SAS log. Click on {i} and paste the SAS log into the window that appears.

Re: Proc SQL in macro

Vahe_Mar — Fri, 11 Jan 2019 13:30:23 GMT

WARNING: Apparent symbolic reference X11 not resolved.

Re: Proc SQL in macro

Vahe_Mar — Fri, 11 Jan 2019 13:31:38 GMT

MLOGIC(SQ): %PUT &&&var1 &&&var2 &&&var3
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable VAR1 resolves to x11
SYMBOLGEN: Macro variable X11 resolves to 11
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable VAR2 resolves to x21
SYMBOLGEN: Macro variable X21 resolves to 5
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable VAR3 resolves to x31
SYMBOLGEN: Macro variable X31 resolves to 14
11 5 14
MLOGIC(SQ): Ending execution.
WARNING: Apparent symbolic reference X31 not resolved.
4471 %put &x31;
&x31

Re: Proc SQL in macro

Kurt_Bremser — Fri, 11 Jan 2019 13:39:18 GMT

PLEASE use these buttons for posting logs and code, respectively.

Re: Proc SQL in macro

PaigeMiller — Fri, 11 Jan 2019 13:39:15 GMT

So, let's go back to message 2 of this thread, where @andreas_lds requested:

Add
options mprint mlogic symbolgen;
before the macro is called, examine the log or post it here, but please post code and log using the {i} or running man icon to preserve formatting and increase readability.

Please follow the instructions given by @andreas_lds. We're trying to help you, but you have to help us as well.

Re: Proc SQL in macro

Astounding — Fri, 11 Jan 2019 13:43:13 GMT

Macro variables created by SQL are local, not global. To make them global, add a line to your macro definition:

%macro sq (var1=, var2=, var3=, var4=);

%global &var1 &var2 &var3;
proc sql;
select count (distinct USUBJID) INTO:&var1 from ADAE where EYE="A" and &var4;
select count (distinct USUBJID) INTO:&var2 from ADAE where EYE="B" and &var4;
select count (distinct USUBJID) INTO:&var3 from ADAE where &var4;
quit;

%put &var1 &var2 &var3
%mend sq;

And if &var4 gives you any trouble (and it might), make the following additional change:

where %unquote(&var4);