BookmarkSubscribeRSS Feed
leok31
Calcite | Level 5

I'm new SAS and still learning my way around.

I've connected to SQL Server via SAS using ODBC connection. 

I used PROC SQL statement to create a table, this took around 10 second.

But then, when I try to query that table it takes a long time. So far I waited 8 min before stopping the process.

Similar thing happens when I try to JOIN the newly created table to another table from SQL Server.

 

Is there anything I can do to speed up the process?

 

Thank you

 

Here is the code that I'm using

 

proc sql;
 CREATE TABLE ActiveIB as 
SELECT distinct col1
      ,col2
      ,col3
      
  FROM CAM.FactTable
  where Col4= 'Active'
  and (DateCol is  NULL or  DateCol <= '01DEC2017'd)
  ;
  
  quit;

proc sql outobs=10;
select *
from ActiveIB
;
quit;

 

 

 

6 REPLIES 6
SASKiwi
PROC Star

I suspect your second SQL query is reading all of your data. Try this:

proc print data = ActiveIB (obs = 10);
run;
Reeza
Super User

Work shouldn’t be the issue for your second query. 

 

Hoeever when you’re pulling data from different sources, ie WORK and a server somewhere else, SAS first brings it all in, which is what slows things down. So the fact that your join is slow Is not surprising. The fact that a plain select takes long doesn’t make sense however. 

 


@leok31 wrote:

I'm new SAS and still learning my way around.

I've connected to SQL Server via SAS using ODBC connection. 

I used PROC SQL statement to create a table, this took around 10 second.

But then, when I try to query that table it takes a long time. So far I waited 8 min before stopping the process.

Similar thing happens when I try to JOIN the newly created table to another table from SQL Server.

 

Is there anything I can do to speed up the process?

 

Thank you

 

Here is the code that I'm using

 

proc sql;
 CREATE TABLE ActiveIB as 
SELECT distinct col1
      ,col2
      ,col3
      
  FROM CAM.FactTable
  where Col4= 'Active'
  and (DateCol is  NULL or  DateCol <= '01DEC2017'd)
  ;
  
  quit;

proc sql outobs=10;
select *
from ActiveIB
;
quit;

 

 

 


 

leok31
Calcite | Level 5

For some reason, selecting just the top 10 is now working fine.

Selecting all the rows still takes a very long time, it's only 300k rows. Why would it take so long if the data is stored in Work library and not on an external server?

 

SASKiwi
PROC Star

Post the SAS log of your program including the notes on how long it took so we can provide further advice.

leok31
Calcite | Level 5

The query has been running for over 30 min. 

I'm running SAS Studio, so I had to cancel it and it results in termination of the session. I can't get any log data for it.

 

ChrisNZ
Tourmaline | Level 20

1- Just checking: your CAM library uses the ODBC engine to point to SQL Server?

 

2- Use

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

to see what query is sent to SQL server.

 

3- Use dataset option(obs=10) to limit the number of rows so we can get a log.

 

4- Run the query and post the log.

 

 

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
  • 840 views
  • 0 likes
  • 4 in conversation