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

[munitech4u]

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?

[BrunoMueller]

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]

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]

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

[munitech4u]

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]

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]

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

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]

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]

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

[AhmedAl_Attar]

It's a SAS 9.4 autocall macro, 25076 - Resolve a macro variable within single quotation marks

[Tom]

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]

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

[Tom]

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]

I am not sure how view is defined, but

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