Accepted Solutions
@KRHE It appears you're interfacing with SQL Server. The issue you encounter is on the DB side with DB SQL. SAS has nothing to do with it. You need to investigate the issue on the DB side ideally using a DB client like MS SQL Server Management Studio or the like.
The Alphabetic List of Variables and Attributes you show us is to what SAS maps the DB table columns to but these are not the actual DB column types. You need to retrieve this information directly on the DB.
Using SAS code like below could work.
proc sql;
connect to oledb as NBP_data (provider=SQLNCLI11 properties=(
"Data Source" = "xxxx"
"Initial Catalog" = "xxx"
"Integrated Security" = "SSPI"
"Persist Security" = "False") ;
select * from connection to NBP_data
(
SELECT
ORDINAL_POSITION
, COLUMN_NAME
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog='DDD' and table_schema='DD' and TABLE_NAME = 'PV_APPLICANT'
;
);
disconnect from NBP_data;
quit;
From what SAS maps the columns to it looks like the DB columns are both of type Datetime. But then your syntax should work...
Below executed via MS SQL Server Management Studio.
I suggest for debugging purposes just run your where clause against the one column/table where things appear to return an error. If using SAS you could start with something like below - but ideally you're doing this without SAS using a DB client and eventually with some support from a DBA at your site.
proc sql outobs=5;
connect to oledb as NBP_data (....);
select * from connection to NBP_data
(
select RowTimestamp
from [DDD].[DD].[PV_APPLICANT]
where RowTimestamp > '2024-04-10'
;
);
disconnect from NBP_data;
quit;
You show us an error message but not the code and log that you executed that created this error.
Can you please try and execute the SQL I've shared already and let us know if this also causes such an error? Below the code once more:
proc sql outobs=5;
connect to oledb as NBP_data (....);
select * from connection to NBP_data
(
select RowTimestamp
from [DDD].[DD].[PV_APPLICANT]
where RowTimestamp > '2024-04-10'
;
);
disconnect from NBP_data;
quit;There is no need to explicitly convert the datetime string to a SQL datetime value. The SQL Server compiler is smart enough to do such a conversion implicitly if you use it as comparison value with a column of type datetime or datetime2. Which format of date/datetime string works is fully documented under Supported string literal formats for datetime2
With the SQL Server 2019 version I'm using below code works for all the syntax variations for the Where clause.
The one Where clause that's not in comment uses the datetime string in the ISO 8601 format as per documentation referenced above.
Which version are you using and can you please also confirm that your database is MS SQL Server.
@KRHE Great that you've got now working code.
Based on what you shared I still can't understand why the syntax with a hardcoded date string doesn't work....
...after you confirmed that below code does work.
What I did experience in the past is that macro quoting can create issues when used in explicit pass-through SQL.
I can see in your code:
where cast(asap.RowTimestamp as date) = %nrbquote(&igar.)
To avoid potential issues I suggest you amend the code to:
where cast(asap.RowTimestamp as date) = %unquote(%nrbquote(&igar))
Alternatively: I've made good experiences with using the SAS supplied %tslit() macro for wrapping single quotes around a resolved macro variable.
You could generate the date string without quotes and then use %tslit() at the places where you need the string embedded in single quotes.
%let igar=%sysfunc(putn(%eval(%sysfunc(today())-1),yymmdd10.));
%put &igar.; /* output 202404-23 */
proc sql;
....
where cast(asap.RowTimestamp as date) = %tslit(&igar)
quit;
And just as a personal preference: I normally try to avoid nesting of more than one %sysfunc() just because I find it hard to read.
%let igar=%sysfunc(putn(%eval(%sysfunc(today())-1),yymmdd10));I consider below alternative code much easier to read and maintain.
data _null_;
dt=today()-1;
call symputx('igar',put(dt,yymmdd10.));
run;
%put &=igar;
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
651 views
-
3 likes
-
-
