Help using Base SAS procedures

PROC SQL is too slow!

Reply
Occasional Contributor MBI
Occasional Contributor
Posts: 19

PROC SQL is too slow!

Hi everyone,

I'm reading and writing values to a table using the PROC SQL procedure and it is much slower than I had hoped. I'm using code like the following:

*read a value into a variable;
proc sql;
SELECT var1 INTO :check FROM table1 WHERE row = 7;
run;

*write a value to a table;
proc sql;
UPDATE table1 SET var1 = 8 WHERE row = 10;
run;

Each of these is taking around 0.5s to execute. Am I doing this wrong? Is there a more efficient way to read/write like this? Should I read the tables into memory using something like SASFILE for faster access? PROC SQL is pretty new to me so I'm open to all suggestions.

I'm running EG4.1 on XP.

Thanks!

-MBI
PROC Star
Posts: 1,564

Re: PROC SQL is too slow!

No sure what you want to achieve, so my reply will be incomplete, but here are a few starting points:
- are the datasets indexed by variable ROW?
- calling proc sql over and over is slower than calling it once and then doing all the SELECTs or UPDATEs
- yes, sasfile might help, you should test it.

To step back a bit though, it is much slower to update tables one value at a time than to update in bulk by joining, so try and see if you can modify the logic to work in bulk.
Ask a Question
Discussion stats
  • 1 reply
  • 181 views
  • 0 likes
  • 2 in conversation