DATA Step, Macro, Functions and more

SAS Date Prompt in Proc SQL Statement

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

SAS Date Prompt in Proc SQL Statement

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



Accepted Solutions
Solution
‎06-03-2016 12:57 PM
Contributor
Posts: 33

Re: SAS Date Prompt in Proc SQL Statement

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


All Replies
Super User
Posts: 5,257

Re: SAS Date Prompt in Proc SQL Statement

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
Contributor
Posts: 33

Re: SAS Date Prompt in Proc SQL Statement

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.

PROC Star
Posts: 1,233

Re: SAS Date Prompt in Proc SQL Statement

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

Contributor
Posts: 33

Re: SAS Date Prompt in Proc SQL Statement

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.

PROC Star
Posts: 1,233

Re: SAS Date Prompt in Proc SQL Statement

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

PROC Star
Posts: 1,233

Re: SAS Date Prompt in Proc SQL Statement

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

Respected Advisor
Posts: 3,896

Re: SAS Date Prompt in Proc SQL Statement

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'

Super User
Posts: 10,516

Re: SAS Date Prompt in Proc SQL Statement

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

Valued Guide
Posts: 3,208

Re: SAS Date Prompt in Proc SQL Statement

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 --<-----
Solution
‎06-03-2016 12:57 PM
Contributor
Posts: 33

Re: SAS Date Prompt in Proc SQL Statement

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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