topic Re: Resolve Macro variable value in the where clause of proc sql in SAS Programming

Resolve Macro variable value in the where clause of proc sql

arpitsharma27 — Thu, 16 Aug 2018 14:04:29 GMT

Team,

I have this dataset:

    data have1;
    input name $23.;
    datalines;
    &temp.alpha
    &temp.beta
    ;
    run;

This is what I want:

%let temp=var1;
proc sql;
	create table want1 as
		select upcase(resolve(name)) as new_var
			from have1
				where upcase(resolve(name))=upcase("var1alpha")
	;
quit;

I am able to get what I want using resolve().

But with this, I have to make change in all the existing code.

I am reaching out to you to find , if any, alternative solution.

I tried having the following dataset but it does NOT work.

data have2;
input name $23.;
datalines;
resolve(&temp.alpha)
resolve(&temp.beta)
;
run;

Any alternative you would suggest?

Thanks

Re: Resolve Macro variable value in the where clause of proc sql

Kurt_Bremser — Thu, 16 Aug 2018 14:16:19 GMT

And what for would all this be? I have an inkling that you are aiming a big shotgun in the direction of your foot.

Re: Resolve Macro variable value in the where clause of proc sql

RW9 — Thu, 16 Aug 2018 14:18:12 GMT

An example of what you have, what you want, and an explanation of the process would help. Its unlikely that this is the best approach.

Re: Resolve Macro variable value in the where clause of proc sql

gamotte — Thu, 16 Aug 2018 14:19:55 GMT

Hello,

You can resolve the macrovariables in a data step prior to the proc sql :

data have2;
    set have1;
    name=resolve(name);
run;

proc sql;
	create table want1 as
	select upcase(name) as new_var
	from have2
	where upcase(name)=upcase("var1alpha")
	;
quit;

Re: Resolve Macro variable value in the where clause of proc sql

arpitsharma27 — Thu, 16 Aug 2018 14:39:54 GMT

Sir,

With all due respect, it has been clearly laid out.
You will have to run the code and read my comments in there.

Re: Resolve Macro variable value in the where clause of proc sql

Kurt_Bremser — Thu, 16 Aug 2018 14:54:32 GMT

I only see a dataset with one variable and one observation. Since this is no good on its own, I was asking for the bigger picture. But maybe you don't have one.

To suggest an alternative (as you wanted in your first post), we need to know what you're aiming at.

SAS will not resolve anything (no macro references, no function call, nothing) in a datalines block. And your SQL approach works, as demonstrated. But we have no idea why it would cause such a big change to existing code, as we don't see anything of that.