topic Re: macro variable in sql statement in SAS Programming

macro variable in sql statement

HB — Tue, 14 Jun 2022 23:13:17 GMT

I am working inside a macro. Everything is running nicely and I am proud of myself for making this thing. Going to save me some time. I have one little thing.

I do an SQL statement and I select some stuff along with

'&myvariable._myfilename only' as location like

proc sql;
	title 'differences';
	select foo, '&myvariable._myfilename only' as location
	from bar 
etc

The &myvariable. won't resolve to the value inside the quoted SQL.

If when I ran the macro myvariable were ABC, I would want location to have the value 'ABC_myfilename only' in the output.

I've tried CATS and 'stuff'n and some other silly stuff.

I'm guessing it's a little piece of syntax I don't know and can't find.

Can you help?

Re: macro variable in sql statement

Quentin — Wed, 15 Jun 2022 01:20:11 GMT

Congrats, you should be proud of yourself! The macro language is hard.

The little bit of syntax you're missing is the fact that macro variables don't resolve inside of single quotes. If you change to double quotes, it should work fine.

proc sql;
	title 'differences';
	select foo, "&myvariable._myfilename only" as location
	from bar 
etc

Re: macro variable in sql statement

HB — Wed, 15 Jun 2022 19:53:50 GMT

Really SAS? Really? You're going to do that to me? Single vs double quotes. LOL.

Thanks a ton, works a treat.

Re: macro variable in sql statement

Tom — Wed, 15 Jun 2022 20:35:01 GMT

@HB wrote:
Really SAS? Really? You're going to do that to me? Single vs double quotes. LOL.

Thanks a ton, works a treat.

Think it through. How else so you make a string that actually has the value &X ? By having the macro processor ignore strings in single quotes it is easy to prevent the macro process from messing up strings that just happen to look like macro code.

Re: macro variable in sql statement

HB — Thu, 16 Jun 2022 21:04:16 GMT

In many languages/programs, either double and single quotes can be used interchangeably, so as a solution double quotes didn't jump off the screen at me. I appreciated the response that actually solved my problem.