SAS date parameter in pass through SQL statement

[DangIT]

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!

[LinusH]

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.

[DangIT]

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;

[DBailey · Accepted solution]

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

[DangIT]

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

[DangIT]

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;

[hbi]

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;