BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nov
Calcite | Level 5 nov
Calcite | Level 5

Is it possible to use a macro variable for proc sql like condition?

Such as :

proc sql;

   create table key as

   select * from c.numkeys

    where upcase(esc) like  '%&&name&i.%' ;

          run;

The name and i are macro variables.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

I think you need triple & if I understand your macro variable structure.

You can use  %QUOTE() to generate a protected percent sign so that SAS doesn't think you want to call macro.

Or you can just use the CONTAINS operator instead of the LIKE operator.

%let i=1;

%let name=value;

%let value1=A ;

proc sql  ;

select * from sashelp.class

where upcase(name) like "%quote(%%)&&&name&i%"

;

%put &sqlobs;

select * from sashelp.class

where upcase(name) contains "&&&name&i"

;

%put &sqlobs;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, I personally would avoid using several macro variables like that, i.e. &name, and &I.  It becomes very messy with all those ampersands and de-referencing.  Never actually found a time when I needed to do that.

As for your question, the like would need to have double quotes:

"%&&name&i.%"

Otherwise the macro variable won't de-reference.  As for &&this and the other...

nov
Calcite | Level 5 nov
Calcite | Level 5

The double quotes were used before. The warning messages are:

WARNING: Apparent invocation of macro  value1 not resolved.

WARNING: Apparent symbolic reference NAME not resolved.

WARNING: Apparent invocation of macro value1 not resolved.

WARNING: Apparent symbolic reference value1value1 not resolved.

The value1 is name's value. Is it a way to get out the warnings?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Exactly, parts of the macro variables are being de-referenced at differing times.  TBH I am not going to try to work out which order they do it as I really don't like lines of ampersands.  Post some example code and test data/output and will see what I can come up with tomorrow.

Tom
Super User Tom
Super User

I think you need triple & if I understand your macro variable structure.

You can use  %QUOTE() to generate a protected percent sign so that SAS doesn't think you want to call macro.

Or you can just use the CONTAINS operator instead of the LIKE operator.

%let i=1;

%let name=value;

%let value1=A ;

proc sql  ;

select * from sashelp.class

where upcase(name) like "%quote(%%)&&&name&i%"

;

%put &sqlobs;

select * from sashelp.class

where upcase(name) contains "&&&name&i"

;

%put &sqlobs;

Ksharp
Super User

Why not use SYMGET() ?

%let value1=A ;
proc sql  ;
select * from sashelp.class
where upcase(name) contains symget('value1');
;
quit;

Xia Keshan

loweowen
Calcite | Level 5
Thanks so much Xia - this is just what I needed
- in particular notice the use of the plain variable rather than the variable with $ prefix didn't work inside the proc sql 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 17993 views
  • 1 like
  • 5 in conversation