06-22-2017 03:53 PM
Hello, I am pulling data from SQL server.
it takes about 1 hour when I use sas and about 3 mints using SQL server Management Studio. data comes back but takes ages to pull it using SAS
here is a sample code that I used to query... please advise
create table temp_A
a.number, b.type, c.country, d.city
left join R1.dbo.Tbl_Type b on (a.ID = b.AcID)
left join R1.dbo.Tbl_Country c on (a.CID = c.CID)
left join R1.dbo.Tbl_City d on (a.ID = d.AcID)
dateDTTM >= '01Jun2016' and dateDTTM < '01Jun2017' and
upper(b.Comments) like '%HOURS%' or
upper(b.History) like '%HOURS%' or
upper(b.Group) like '%HOURS%'
c.TCode = 3 and
d.ACode in ('F', 'C')
06-22-2017 03:59 PM - edited 06-22-2017 04:17 PM
How big is the data?
Did you run this with FULLSTIMER option? If so, please show the log.
My first guess, it's a big table and the time is spent transferring the data from the server to SAS not actually doing the join.
You should check which functions are passed to the DB as well versus which need to execute locally. See the DB specific version in the SAS docs to understand how functions are processed when using a server.
06-22-2017 05:18 PM
It looks like your query uses SQL Server-specific syntax. To run unchanged in SAS put it into an SQL passthru query which will look like the following - you will need to supply your own connection string:
proc sql; connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"); create table Want as select * from connection to odbc (---- Put your SQL Server query here ) ; disconnect from odbc; quit;
06-22-2017 05:18 PM - edited 06-22-2017 05:23 PM
Can you show where the join fails. If your query fails it might be on dates. Can you add this and try, whether it will help with your query times.
I have seen moving data from sql server to SAS for nearly 3 million records takes around 30 minutes with 100 columns, so you can estimate an approximate time.
options dbidirectexec sastraceloc=saslog;
06-22-2017 06:02 PM
seems like joins are not supported by sas. if not, how can I achieve my desired results
That doesn't sound correct.
What makes you think that?
Joins work the same, EXCEPT if you're using datasets both in SAS and on the server. In that case SAS has to bring things down to SAS first to merge.