BookmarkSubscribeRSS Feed
munitech4u
Quartz | Level 8

I am trying to do following in macro:

proc sql;

select * from table1 where col1 like 'x%'

quit;

%macro temp(val=x);

proc sql;

select * from table1 where col1 like '&val%';

quit;

%mend;

The problem is that to resolve the value of val, it has to be in double quotes(" ") , but sql statement works only with single quotes(' '), as it throws the error with double quotes: Invalid column name.

Any suggestion how to resolve this?

18 REPLIES 18
BrunoMueller
SAS Super FREQ

The double quotes should work just fine in Proc SQL, since SAS accepts either single or double quote. You are missing a semicolon at the end of the SELECT statement.

If you want to go with the single quotes, you can use the %TSLIT() autocall macro like below:

%macro temp(val=A);

  proc sql;
    select * from sashelp.class where name like %
tslit(&val%);
  quit;

%mend;

%
temp(val=A)

Bruno

munitech4u
Quartz | Level 8

Thanks, the semi-colon was missing by mistake here. Seems like %tslit is doing the job as expected. But not sure why double quotes throws the error: Invalid column name, even if i directly pull the data, using double quotes. I am using SQL server 2008, and query is in SAS.

BrunoMueller
SAS Super FREQ

If you post the SAS log it is easier to see what is going on

munitech4u
Quartz | Level 8

proc sql;

SELECT scope_id,scope_desc from ops_scope where scope_desc like "Lialda%";

quit;

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name

       'Lialda%'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

The above statement works fine with single quotes.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

It looks like you are accessing and SQL Server database using pass-through.  Why not use one SQL to extract all the data into SAS, then have your code filtering the data as it appears in SAS, otherwise you are calling the ODBC interface each time.

munitech4u
Quartz | Level 8

(You mean this statement creating problem:

proc sql;

connect to odbc as mycon

   (datasrc=cdom3_ops);

create table scope_table as

select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope where scope_desc like "Lialda%");

quit;

I am not much familiar with database interfacing, but this is what we usually do to connect to SQL server.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, the point I was trying to make was to extract the data into SAS without any restrictions:

proc sql;

connect to odbc as mycon

   (datasrc=cdom3_ops);

create table scope_table as

select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope);

quit;

Now you can do one of two things, either in the SAS part of the query put the where - this would be if thats the only data you want to work on in SAS:

proc sql;

connect to odbc as mycon

   (datasrc=cdom3_ops);

create table scope_table as

select * from connection to mycon(SELECT scope_id,scope_desc from ops_scope)

where scope_desc like "Lialda%";

quit;

The where is then executed in SAS.

Alternatively, just keep the full dataset, and where off into a separate table in SAS, that way if you wanted other data, you wouldn't have to do the query again as all the data is in SAS.  It really depends on if you need the other data.

munitech4u
Quartz | Level 8

I got your point. But if it happens to be large data, it would be waste of space and even take lot time, while i need only specific matching rows.

PaigeMiller
Diamond | Level 26

I just did a search for documentation on the %tslit macro and didn't find any. Someone point out where this is documented. Thanks!

--
Paige Miller
Tom
Super User Tom
Super User

The query you posted appears to be reading from the SAS dataset WORK.OPS_SCOPE.  So why are you getting an error message from the SQL Server Driver?

Is WORK.OPS_SCOPE a view?  How is the view defined?

Most likely what you actually ran was a passthu  query more like:

connect to odbc (....). as X ;

select * from connection to X (select scopeid from ops_scope where scope_desc like "XXX") ;

In that case the code that you send to the SQL Server needs to use SQL Server syntax and not SAS syntax.

munitech4u
Quartz | Level 8

OPS_SCOPE is a view. So, i think thats why its SQL server syntax.

Tom
Super User Tom
Super User

So how is the view defined?

I just tried making a view to a table in Teradata server and calling it with a view and SAS happily translated my double quotes to single quotes for me.

libname td teradata ..... ;

proc sql ;

  create view myview as select * from td.mytable ;

  select * from myview where myvar like "mystr%" ;

munitech4u
Quartz | Level 8

I am not sure how view is defined, but

%tslit macro function stated above, seems to be doing the job for me.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 18 replies
  • 4388 views
  • 3 likes
  • 8 in conversation