Proc SQL in macro

[Vahe_Mar]

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

[andreas_lds]

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.

[Vahe_Mar]

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. 

[ErikLund_Jensen]

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

[PaigeMiller]

---

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

[Vahe_Mar]

"why they are wrong?" what you mean?

[PaigeMiller]

---

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

[Vahe_Mar]

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.

[PaigeMiller]

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

[Vahe_Mar]

WARNING: Apparent symbolic reference X11 not resolved.

[Vahe_Mar]

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

[Kurt_Bremser]

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

[PaigeMiller]

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.

[ErikLund_Jensen]

Sorry Vahe_Mar

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

[Astounding]

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