PROC SQL is too slow!

[MBI]

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

[ChrisNZ]

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.