BookmarkSubscribeRSS Feed
tparvaiz
Obsidian | Level 7

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

5 REPLIES 5
Reeza
Super User

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):

https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n1hwhalvtejwi2n1t...

tparvaiz
Obsidian | Level 7

seems like joins are not supported by sas. if not, how can I achieve my desired results

SASKiwi
PROC Star

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;
kiranv_
Rhodochrosite | Level 12

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;

 

Reeza
Super User

@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.

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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
  • 5 replies
  • 3076 views
  • 2 likes
  • 4 in conversation