BookmarkSubscribeRSS Feed
MBI
Calcite | Level 5 MBI
Calcite | Level 5
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
1 REPLY 1
ChrisNZ
Tourmaline | Level 20
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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 1 reply
  • 968 views
  • 0 likes
  • 2 in conversation