Pull dates from database and passing back into database: Using macros

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Pull dates from database and passing back into database: Using macros

Hello everyone.  I have two independent databases on two independent servers.  I cannot do cross server queries in Sql Server (the database tool we use).

I need the results of one table to be subset by the time component of another table from a different server.  Basically I need to take the maximum date of one table, and filter the secondary table by only loans that are greater than this number. I can do this in two steps, by pulling in the entire table into sas, and then applying the filter within sas. However this is incredibly inefficient and is not the correct way to go. Can someone help me combine the final two steps into one SQL pass through?

I basically can't figure out how to modify the macro variable to pass it into sql server and get a valid result. My current code is below.

/*Get the max date from the one table / database.   Note that I have tried using format=datetime22.3 but this also does not work   I have it currently set to best32 because I need it that way in order for the secondary datastep to work currently.*/
proc sql;
select max(SpecificDate)  format=best32 into :Maximumruntime

from libref.app_maxdeletedttm;
quit;
run;

/*here i pull all data, which is over 10,000,000 records and takes far too long. my final query after the second datastep is only ~15 records. I need to find a way to apply the date time macro variable into this pass through query.*/

proc sql;
connect to odbc as myodbc (DSN = myodbcname);
create table Anydeletes as
select * from connection to myodbc
(SELECT LoanNumber
,deleteDttm
,deleteReason

FROM coretable.dbo.fr_loan fl);
quit;

/*want this applied above, can't figure out how*/

data Anydeletes
set Anydeletes
where deleteDttm>=&Maximumruntime.;
run;

Thanks and let me know if anyone has any quesitons!


Accepted Solutions
Solution
‎09-24-2014 02:15 PM
Super User
Super User
Posts: 7,077

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

Your first query is using a LIBREF and so SAS is converting your data types for you when it generates the database query.

If you can switch your second query to using a LIBREF an implicit pass through then it might work.

Otherwise you need tell us what format your database for the second step will accept as a date literal.

From reading on-line it looks like MS SQL might what it in the form 'MM-DD-YYYY HH:MMSmiley FrustratedS'.

If so then your could generate that using something like.

data _null_;

   call symputx('MAXTIMESQL',quote(catx(' ',put(datepart(&Maximumruntime),mmddyyd10.),put(timepart(&Maximumruntime),tod8.)),"'"));

run;

View solution in original post


All Replies
Super User
Posts: 5,441

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

Your first query should definitely be sent to the DBMS for processing.

Set

options sastrace=',,,d' sastraceloc=saslog;

before you query to see what is being passed.

If the :into part is the trouble, just store the result in a work table, then create the macro variable from there.

Even query 2 should be sent down to DBMS using implicit pass-thru, just add your WHERE with the macro variable in your implicit SQL query.

Data never sleeps
Super Contributor
Posts: 418

Re: Pull dates from database and passing back into database: Using macros

SO my first query is being sent to the DBMS and is being returned correctly. Maybe I don't understand your statement so could you clarify?

My first query is returning the correct "number" representation of the sql database datetime... If I cast it as a datetime in sas it is 100% correct.

The issue is it returns the numeric value of the datetime field. Which is fine for sas processing, however I can''t pass that number in through the Explicit pass through in the secondary query.. Sql doesn't know what "date> 713951051" means (713951051 is my made up representation of my datetime).

If by implicit pass through you mean a query similar to...

proc sql;

create table anydeletes as

select loannumber ,deletedttm, deletereason

from libref.fr_loan

where deleteddttm>&macrovariable;

quit;

run;

then I would prefer not to go this route.  I've found that implicit pass throughs similar to this query are quite a bit slower than explicit pass throughs, almost consistently, so I really don't like using them.

Super User
Posts: 5,441

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

If you use the sastrace I mentioned you can verify if your query is passed or not. If it's passed, it's not slower.

It's simpler to interact with the SAS syntax processing when you have SAS syntax components like macro variables.

You could upload your date result table to the second server and do an inner join there instead if you like, and have the rights.

Data never sleeps
Super User
Posts: 19,871

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

Or figure out what format your DB is expecting for the date time and convert it to that and include it in your query.

Microsoft SQL Server Knowledge Bank: DATETIME in WHERE Clause

Solution
‎09-24-2014 02:15 PM
Super User
Super User
Posts: 7,077

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

Your first query is using a LIBREF and so SAS is converting your data types for you when it generates the database query.

If you can switch your second query to using a LIBREF an implicit pass through then it might work.

Otherwise you need tell us what format your database for the second step will accept as a date literal.

From reading on-line it looks like MS SQL might what it in the form 'MM-DD-YYYY HH:MMSmiley FrustratedS'.

If so then your could generate that using something like.

data _null_;

   call symputx('MAXTIMESQL',quote(catx(' ',put(datepart(&Maximumruntime),mmddyyd10.),put(timepart(&Maximumruntime),tod8.)),"'"));

run;

Super Contributor
Posts: 418

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

Thanks Linus. The other problem I have with using implicit pass throughs is my in-ability to not lock the sql table using my reads.

Example: If sas crashs on my local machine while running the query, I have now dead-locked the table so that others cannot insert from/ read from / etc.  into this table.  Actually if SAS lags all together while performing the read this also occurs.

By using the explicit pass through I can specify the sql option for an uncommited read.  I can basically specific the transaction isolation level through my query, as changing the transaction isolation level at the database level is beyond my permissions.

I guess this is a secondary question, but I feel like I would need a more exact solution for this problem before using an implicit pass through onto a production database.

Also I did consider uploading a new table into the database, however I need a permissions level that I do not possess within this database to do this. If I had the permissions to do so I actually wouldn't need the table stored on my database!

Tom I will attempt your solution in the explicit pass through.

Thanks very much

Super User
Super User
Posts: 7,077

Re: Pull dates from database and passing back into database: Using macros

Posted in reply to Anotherdream

I don't know about MS SQL but for Teradata we query using views that have the appropriate locking set in the view to prevent that type of problem.  So users do not query the tables directly, just the views.

Super Contributor
Posts: 418

Re: Pull dates from database and passing back into database: Using macros

That's a unique idea Tom. Interesting that you have to build views and then pull from the views instead of directly from the tables. HOwever that seems like a really good solution I will take to the BI team to determine if this is something we can do.

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 917 views
  • 0 likes
  • 4 in conversation