BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
NathanOch
Obsidian | Level 7

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


1 ACCEPTED SOLUTION

Accepted Solutions
NathanOch
Obsidian | Level 7

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.

View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

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

Data never sleeps
NathanOch
Obsidian | Level 7

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.

Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
NathanOch
Obsidian | Level 7

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.

Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Quentin
Super User

My memory is oracle likes single quotes around literals but does not like double quotes. Perhaps SQL server is the same??

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Patrick
Opal | Level 21

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'

ballardw
Super User

It looks like the SQL server thinks the date values you are passing are variable names not values.

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
NathanOch
Obsidian | Level 7

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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 webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 8429 views
  • 0 likes
  • 6 in conversation