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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20

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.

Data never sleeps
DangIT
Fluorite | Level 6

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
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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
Fluorite | Level 6

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
Quartz | Level 8 hbi
Quartz | Level 8

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;

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
  • 6 replies
  • 7679 views
  • 3 likes
  • 4 in conversation