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
Proc SQL;
create table temp_A
as
select distinct
a.number, b.type, c.country, d.city
from
R1.dbo.Tbl_Numbers a
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)
where
dateDTTM >= '01Jun2016' and dateDTTM < '01Jun2017' and
(
upper(b.Comments) like '%HOURS%' or
upper(b.History) like '%HOURS%' or
upper(b.Group) like '%HOURS%'
) and
c.TCode = 3 and
d.ACode in ('F', 'C')
;
Thanks
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.
EDIT (links):
seems like joins are not supported by sas. if not, how can I achieve my desired results
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;
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;
@tparvaiz wrote:
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: