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;
I suspect your second SQL query is reading all of your data. Try this:
proc print data = ActiveIB (obs = 10);
run;
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;
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?
Post the SAS log of your program including the notes on how long it took so we can provide further advice.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.