topic Re: SAS Date Prompt in Proc SQL Statement in SAS Programming

SAS Date Prompt in Proc SQL Statement

NathanOch — Tue, 30 Dec 2014 15:31:24 GMT

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

Re: SAS Date Prompt in Proc SQL Statement

LinusH — Tue, 30 Dec 2014 15:40:25 GMT

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

Re: SAS Date Prompt in Proc SQL Statement

NathanOch — Tue, 30 Dec 2014 15:46:11 GMT

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.

Re: SAS Date Prompt in Proc SQL Statement

Quentin — Tue, 30 Dec 2014 15:57:12 GMT

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

Re: SAS Date Prompt in Proc SQL Statement

NathanOch — Tue, 30 Dec 2014 16:05:44 GMT

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.

Re: SAS Date Prompt in Proc SQL Statement

ballardw — Tue, 30 Dec 2014 18:24:24 GMT

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

Re: SAS Date Prompt in Proc SQL Statement

jakarman — Tue, 30 Dec 2014 18:52:11 GMT

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.

Re: SAS Date Prompt in Proc SQL Statement

Quentin — Tue, 30 Dec 2014 20:44:16 GMT

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

Re: SAS Date Prompt in Proc SQL Statement

Quentin — Tue, 30 Dec 2014 20:46:46 GMT

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

Re: SAS Date Prompt in Proc SQL Statement

Patrick — Wed, 31 Dec 2014 00:54:25 GMT

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'

Re: SAS Date Prompt in Proc SQL Statement

NathanOch — Fri, 03 Jun 2016 16:56:48 GMT

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.