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

helloagainoh2_1-1611775644586.png

 

Hi I am having trouble searching the Marco variable before the name, in this case I have a marco named ParkCode with the value OSE. I want to be able to search for %OSE% by using the marco in the where statement, but as you can see in the log I am only getting back "%&ParkCode%", instead of "%OSE%".

 

What could be the reason for this?

 

 

 

helloagainoh2_2-1611775670905.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

Hi @helloagainoh2,

 

Just duplicate the first percent sign:

where Species_ID like "%%&ParkCode%" and ...

View solution in original post

3 REPLIES 3
PhilC
Rhodochrosite | Level 12

That's a dilemma.  I don't think there a less messy way about this.  

 

proc sql;
  select model
    from SAShelp.cars 
    where model like "%ac%"
;
quit;

%let macvar=ac;

proc sql;
  select model
    from SAShelp.cars 
    where model like cat('%',"&macvar",'%')
;
quit;

proc sql;
  select model
    from SAShelp.cars 
    where model like '%'||"&macvar"||'%'
;
quit;

 

ballardw
Super User

PLEASE post code as text not a picture.

Copy from the editor (or log) and paste into a text box opened on the forum with either of the </> or "running man" icons.

 

It is much easier to copy/paste and edit for us to make suggestions to code than to retype everything from scratch.

 

The FIND or INDEX functions will work in a where condition:

%let pattern= AN;

proc print data=sashelp.class;
   where find(name,"&pattern.",'i')>0;
run;

Find will return the starting character position number of the 'pattern', or to find value, in the string variable or value as the first argument to the function. the modifier 'i' says to ignore case, which may or may not be desired. If case sensitive then omit the ,'i' bit.

FreelanceReinh
Jade | Level 19

Hi @helloagainoh2,

 

Just duplicate the first percent sign:

where Species_ID like "%%&ParkCode%" and ...

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 549 views
  • 4 likes
  • 4 in conversation