s I am connecting via ODBC connection to a SQL database using a SAS PROC SQL statement. My goal is to use a SAS date prompt within the PROC SQL statement to limit the amount of data I am extracting from the database and decrease my run time. The SAS date prompt code looks like this:
Date_Column between convert(datetime,"&Date_Placed_min") AND convert(datetime,"&Date_Placed_max")
The SAS date prompt is represented by the &Date_Placed indicator within the code.
The issue I am having is with getting the SAS date to interact with the SQL date format. I get this error:
ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '24Nov2014'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '30Dec2014'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
As you can see, it isn't converting the date to the SQL standard YYYY-MM-DD format. What type of conversion either within the SQL code or a SAS macro must I do in order to get the SAS date to successfully interact with the SQL code?
Thanks,
Nate
SAS tech support created this macro code to help inject a date prompt into my SQL code:
%let new_min=%str(%')%sysfunc(inputn(&Date_Range_min,date9.),yymmdd10.)%str(%');
%let new_max=%str(%')%sysfunc(inputn(&Date_Range_max,date9.),yymmdd10.)%str(%');
I use the new_min value for the start date, and the new_max value for the end date.
Look like explicit SQL pass-thru code...?
If you could use implicit SQL pass-thru instead, and let SAS/ACCESS do the date conversion for you...
Unfortunately, implicit pass-thru may make the process more complicated as the process requires multi-table joins and nested queries to gather the necessary data.
You're connecting to SQL server?
Looks to me like it can handle without the convert function:
Date_Column between 'yyyymmdd' and 'yyyymmdd'
So I would take a look at what you have in &date_placed_min. Is it '24Nov2014'd now? and convert it to YYYYMMDD.
Once you know what you have in the macro variable, you can use PUTN() to convert it, something like (untested):
%let new_date_placed_min=%sysfunc(putn(&date_placed_min,yymmddn8.)) ;
HTH
I am connecting to SQL server.
I have actually experimented with converting it via a macro:
%let LDmin = %sysfunc(putn("&Date_Placed_min"d,YYMMDD10.));
%let LDmax = %sysfunc(putn("&Date_Placed_max"d,YYMMDD10.));
I then reference the new variable in the WHERE statement:
cast(d.turnover_date as date) between cast(convert(datetime,"&LDmin") as date) and cast(convert(datetime,"&LDmax") as date)
I get an error with the correct date format, but SAS still doesn't like it:
ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2014-11-24'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '2014-12-30'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
Looks to me like you don't have the SQL syntax right.
suggest you u write the code without any macro variables so you can confirm the syntax. From what I read, you shouldn't need convert() or even cast() if you have a date literal SQL recognizes, being compared to a date variable.
Then once you have the SQL syntax correct, come back to macro land to generate the needed date string.
HTH
My memory is oracle likes single quotes around literals but does not like double quotes. Perhaps SQL server is the same??
I suggest you first try and get the SQL working by running it with hard coded date strings, eg. something like: cast(d.turnover_date as date) between '2014-01-01' and '2014-12-30'
Once your code works this way add the variable bits. Below some code how you could test that the correct code gets generated and sent to the SQL server (generating the hard coded string you know already that it will be working).
%let Date_Placed_min=01jan2014;
%let Date_Placed_max=30dec2014;
%let LDmin = %sysfunc(putn("&Date_Placed_min"d,YYMMDD10.));
%let LDmax = %sysfunc(putn("&Date_Placed_max"d,YYMMDD10.));
%put LDmin: &LDmin;
%put LDmax: &LDmax;
%let min=%str(%')&LDmin%str(%');
%let max=%str(%')&LDmax%str(%');
%put min: &min;
%put max: &max;
%put %bquote( cast(d.turnover_date as date) between &min and &max );
Log:
cast(d.turnover_date as date) between '2014-01-01' and '2014-12-30'
It looks like the SQL server thinks the date values you are passing are variable names not values.
Understand what is happening with all the involved conversions.
Within a SAS environment a data indication is eg: "&Date_Placed_min"d
This will convert the date to a number. That number is not translated to native SQL format in an easy way.
The more easy approach is defining the macros as a native string the ODBC is expecting, in explicit pass thru.
See: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition
Type matching for date(time) can be adjusted set: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition and SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition string/numeric
What you should do is defining the sas-trace for SQL so you can see and follow what is generated and send as SQL.
SAS tech support created this macro code to help inject a date prompt into my SQL code:
%let new_min=%str(%')%sysfunc(inputn(&Date_Range_min,date9.),yymmdd10.)%str(%');
%let new_max=%str(%')%sysfunc(inputn(&Date_Range_max,date9.),yymmdd10.)%str(%');
I use the new_min value for the start date, and the new_max value for the end date.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.