topic SAS date parameter in pass through SQL statement in SAS Programming

SAS date parameter in pass through SQL statement

DangIT — Thu, 27 Aug 2015 12:56:16 GMT

Hi,

I have a SAS date parameter that I am trying to figure out how to use in a pass through SQL statement.

%let Cur_month='30Apr2013'd ;

proc sql ;

connect to SQLSVR as user1 (Server=Test Database=TestData );

Create table RawData as select * from connection to user1

(Select * From tblTest where ServiceDate >= &Cur_month and ODB_NO is not NULL) ;

quit;

The above doesnt work because it will read in '30Apr2013'd which SQL server doesnt understand.

How would i be able to convert the above to '2013-04-30' so that SQL server run the query.

Thank you for your help!

Re: SAS date parameter in pass through SQL statement

LinusH — Thu, 27 Aug 2015 13:09:09 GMT

Since you have a %let I see no problem using your preferred format.

If you this in an automated way jsut use data step function to convert the format (input - put using appropriate formats).

But, why use explicit pass through? I see nothing in the query that the libname access can't translate.

Re: SAS date parameter in pass through SQL statement

DangIT — Thu, 27 Aug 2015 13:26:10 GMT

It's a large amount of data i'm pulling through, so I believe pass through runs it much faster than if i were to use Libname.

I took your approached and changed the %let Cur_Month='2013-04-30' and converted to dates in my other statements.

Do you know how I could code a parameter from the Cur_month - 180 days? that i can feed through the pass through statement?

%let Cur_Month='2013-04-30'

proc sql ;

connect to SQLSVR as user1 (Server=Test Database=TestData );

Create table RawData as select * from connection to user1

(Select * From tblTest where ServiceDate >= (&Cur_month-180) and ODB_NO is not NULL) ;

quit;

Re: SAS date parameter in pass through SQL statement

DBailey — Thu, 27 Aug 2015 13:26:52 GMT

try this:

%let Cur_month='30Apr2013'd;

%let sqlval = %sysfunc(putn(&Cur_month,yymmddd10.));

%put sqlval=&sqlval;

and I believe the syntax for the date math would be:

dateadd(dd,-180,%str(%')&sqlval%str(%'))

sorry..had to fix the sql syntax

Re: SAS date parameter in pass through SQL statement

DangIT — Thu, 27 Aug 2015 14:55:23 GMT

can you translate the syntax if i used %let Cur_Month='2013-04-30' rather than the SAS date format?

i'm having troubles with conversion especially with the single quotes

Re: SAS date parameter in pass through SQL statement

DangIT — Thu, 27 Aug 2015 15:13:26 GMT

I got it to run with the following script:

%let LBDays=180;

%let Cur_month='30Apr2013'd;

%let sqlval = %sysfunc(putn(&Cur_month,yymmddd10.));

%put sqlval=&sqlval;

proc sql ;

connect to SQLSVR as user1 (Server=test Database=TestData );

Create table Raw_Data as select * from connection to user1

(Select * From tblTest where ServiceDate >= dateadd(dd,-&LBDays,%str(%')&sqlval%str(%')) and ServiceDate <= %str(%')&sqlval%str(%') and ODB_NO is not NULL) ;

quit;

Re: SAS date parameter in pass through SQL statement

hbi — Thu, 27 Aug 2015 21:06:34 GMT

Hello,

Below is another way to tackle the problem that slightly improves upon readability. I guess it is a matter of personal preference, but having a whole lot of %SYSFUNC() and %STR(%') statements might be a strain on one's eyes.

Calculating the lower date boundary up front (within SAS instead of invoking DATEADD in SQL Server), might improve performance by a small amount.

Enjoy!

%let LBDays=180;

%let LBMonths=6;

%let Cur_month='30Apr2013'd;

DATA _NULL_;

dt_180_days_ago = INTNX('day', &Cur_month, -&LBDays);

dt_6mo_ago = INTNX('month', &Cur_month, -&LBMonths, 'end');

CALL SYMPUTX("sqlval", CATS("'", PUT(&Cur_month,yymmddd10.), "'"));

CALL SYMPUTX("sqlval_minus_180", CATS("'", PUT(dt_180_days_ago,yymmddd10.), "'"));

/* a nice variant if you want 6 months ago instead ... */

CALL SYMPUTX("sqlval_minus_6mo", CATS("'", PUT(dt_6mo_ago,yymmddd10.), "'"));

RUN;

%PUT &sqlval ... &sqlval_minus_180 ... &sqlval_minus_6mo;

PROC SQL;

connect to sqlsvr as user1 (server=test database=testdata);

create table raw_data as

SELECT * FROM connection to user1

(SELECT * FROM tblTest

WHERE ServiceDate BETWEEN &sqlval AND &sqlval_minus_180

/* as an aside, and not in your original question, beware that MSSQL treats

empty strings and NULLs differently; so this may be more suitable ... */

AND COALESCE(ODB_NO, '') <> '');

QUIT;