Help using Base SAS procedures

Using macro for proc sql with single and double quotes,error

Reply
Regular Contributor
Posts: 190

Using macro for proc sql with single and double quotes,error

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?

SAS Super FREQ
Posts: 709

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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

Regular Contributor
Posts: 190

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to Bruno_SAS

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.

SAS Super FREQ
Posts: 709

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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

Regular Contributor
Posts: 190

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to Bruno_SAS

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.

Super User
Super User
Posts: 7,997

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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.

Regular Contributor
Posts: 190

Re: Using macro for proc sql with single and double quotes,error

(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.

Super User
Super User
Posts: 7,997

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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.

Regular Contributor
Posts: 190

Re: Using macro for proc sql with single and double quotes,error

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.

Trusted Advisor
Posts: 1,933

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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

Regular Contributor
Posts: 222

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to PaigeMiller
Super User
Super User
Posts: 7,077

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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.

Regular Contributor
Posts: 190

Re: Using macro for proc sql with single and double quotes,error

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

Super User
Super User
Posts: 7,077

Re: Using macro for proc sql with single and double quotes,error

Posted in reply to munitech4u

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%" ;

Regular Contributor
Posts: 190

Re: Using macro for proc sql with single and double quotes,error

I am not sure how view is defined, but

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

Ask a Question
Discussion stats
  • 18 replies
  • 1051 views
  • 3 likes
  • 8 in conversation