I am running SAS EG at my new job and we pull data off of a server. The following code runs very slow, roughly 224 observations per second. I was researching and found that about 5,000 observations per second is considered normal. Below is the basic format of the code. Is the problem with the code or could it be just a server issue? I do know the master table we pull from the server is indexed, but I am not sure how it is indexed.
DATA new_table (RENAME =(var1=newvar1 var2=newvar2 .....));
SET database.Old_Table ( Keep = [77 var names]
WHERE = (date between xxxxxx and xxxxxx AND type = 'A' AND field < 7));
RUN;
NOTE: The data set WORK.New_TABLE has 73874 observations and 77 variables.
NOTE: DATA statement used (Total process time):
real time 5:28.49
cpu time 0.15 seconds
Notice the cpu time is almost zero, while the clock time is over 5 minutes. So the bottleneck of your program is either
Re run your program preceded by
options fullstimer;
which will report resource usage such as disk data transfers, memory usage, and memory page swaps. That may help diagnose your problem.
Also, you could also use a "DATA _NULL_;" statement (instead of "data new_table;") to see how much time consumption is due to disk data set writing.
How big is old_table and new_table? How many rows are in old_table. Does specifying new_table (compress = yes) make any difference?
You say "server", your libanme is called "database", and your CPU is close to zero.
Could it be that you are fetching data from an external RDBMS?
If so, add the following to our program:
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
Using that output, you can talk to your DBA to see if you together can optimize the code/storage.
If your data is in SAS, the other suggestions in the thread may apply. Consider creating an index. And to get better control on what is happening, add:
options fullstimer msglevel=i;
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.