1

I have the following query which runs in SAS using proc sql where I have an automated variable which contains the month end date but it results in the following error 

ERROR: Prepare error: ICommandPrepare::Prepare failed. : ERROR:  Attribute '2017-02-28' not found

Query:

proc sql;
 connect to oledb (datasource='10.1.0.105'  provider=nzoledb 
    user=&user_id password=&pwd properties=('initial catalog'=ODS));
 create table &user..Pers_test as select * from connection to oledb
 (SELECT  a.ID from  DBO.Table1 
   where a.SOURCE_SYSTEM_CREATED_DTM <= "&monthend."
Group by a.SWID order by a.SWID
 );
%let _sql_xrc=&sqlxrc;
disconnect from oledb;
quit;

However the query runs when the timestamp is hardcoded. 

proc sql;
 connect to oledb (datasource='10.1.0.105'  provider=nzoledb 
    user=&user_id password=&pwd properties=('initial catalog'=ODS));
 create table &user..Pers_test as select * from connection to oledb
 (SELECT  a.ID from  DBO.Table1 
   where a.SOURCE_SYSTEM_CREATED_DTM <= '2017-02-28 00:00:00'
Group by a.SWID order by a.SWID
 );
%let _sql_xrc=&sqlxrc;
disconnect from oledb;
quit;

I have tried casting, substring but it all results in the same error. Any help is appreciated to work around with the automated variable.

Karan Pappala
  • 581
  • 2
  • 6
  • 18
  • In your hard coded query of you use double quotes around the time stamp instead of single quotes, do you get the same error? – Quentin Mar 21 '17 at 09:54
  • @Quentin: In the hard coded query, it is single quotes. In the automated query, the single quotes do not convert the variable and so I have to use double quotes. . – Karan Pappala Mar 21 '17 at 09:59
  • I think the double quotes may be the problem. To test if they are the problem, try running your hard coded query with the double quotes. If you get the same error, then you know the double quotes are the cause of the error. If so, there are plenty of examples on SO and elsewhere of different ways to solve the problem of macro vars not resolving inside single quotes. – Quentin Mar 21 '17 at 10:06
  • @Quentin You're right! I used cast(%unquote(%str(%')&monthend.%str(%')) as datetime) and it worked. Thank you! – Karan Pappala Mar 21 '17 at 11:24
  • Glad it worked. You should add that as an answer. – Quentin Mar 21 '17 at 11:27

2 Answers2

0

The variable was not getting resolved under single quotes and hence double quotes was being used. But being double quotes, the column could not identify with the value and the error got thrown up. So, the variable had to be resolved under single quotes.

The code to resolve the variable under single quote is as follows 

cast(%unquote(%str(%')&monthend.%str(%')) as datetime)
Karan Pappala
  • 581
  • 2
  • 6
  • 18
-1

I modified Karan Pappala's answer to make it work for me:

%unquote(%str(%')&execution_method.%str(%'))
double-beep
  • 5,031
  • 17
  • 33
  • 41
prashant
  • 3
  • 2