topic Re: code improvement to run fast in SAS Programming

code improvement to run fast

JJP1 — Mon, 01 Jun 2020 15:34:22 GMT

Hi team,

Currently i am having a code which starts at 02:49:17 and ends at 07:04:57 time.because of this the sas dataset is over running and users are trying to access the datasets from their end which is causing failure and chances datasets will not be updated with correct data.

i can not change the schedule as it is dependent on other datasets also.

so i thought of trying to improve the performance of the code so that it will be running fast.this is just my thought.

i am having a very low knowledge so i am requesting to please provide your thoughts as i believe that will be more excellent and efficinet options please.

iam having so many proc sorts in code- please suggest can i do something on this example : tagsort option or any other advice please

Re: code improvement to run fast

ballardw — Mon, 01 Jun 2020 20:35:46 GMT

Sorting is one of the most common and time intensive activities.

Are you using indexed data sets? Using indexes instead of sorts can speed some activities. Are you rebuilding the same data every day? Appending to existing data?

Does the data reside in an external DBMS?

What exactly are you doing that requires multiple sorts? Can you provide specific examples of the code using the different sorts?

Sometimes a careful sort order selection can allow reuse of the same data without resorting. And if you are doing multiple summaries of data then perhaps use of class variables in a procedure like Proc Summary could do multiple summaries and select the needed one by using a selection of the created _type_ variable.

Some details may help.

Re: code improvement to run fast

Reeza — Mon, 01 Jun 2020 16:09:37 GMT

Given what you've posted, my suggestion would be to search "efficient coding strategies" or "make code faster" on lexjansen.com and read through the paper there.

50 Ways to Make Your SAS® Code Execute More Efficiently

https://www.google.com/url?client=internal-element-cse&cx=011240857950991443104:hsqcj3nokh0&q=http://support.sas.com/resources/papers/proceedings12/257-2012.pdf&sa=U&ved=2ahUKEwiS8bzi_-DpAhVRFjQIHaQPClYQFjAAegQIARAB&usg=AOvVaw3RyhkTab5UrlRuh5er_pav

Twenty Ways to Run Your SAS® Program Faster and Use Less
Space

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3684-2019.pdf

Re: code improvement to run fast

JJP1 — Mon, 01 Jun 2020 16:33:30 GMT

Thanks, i will check and try.

May i know is there any other approach you can see and suggest as i am from basic level of knowledge on this issue please

Re: code improvement to run fast

Reeza — Mon, 01 Jun 2020 16:41:13 GMT

As BallardW indicates you need to provide more details. Vague questions get vague answers.

Re: code improvement to run fast

DavePrinsloo — Mon, 01 Jun 2020 17:06:08 GMT

Look into hashing. The most common reason that sorts are done in SAS is to merge in following steps. If you use hashing, then you dont need to sort either table. Hashing is really useful when working with huge tables and you need to look up things in other "smaller" tables. The proviso is that the smaller table need to comfortably fit in memory. In an optimization consulting project where a table of 100 million records was had to be summarised after being enriched from multiple other sources, I managed to avoid all sorts and the large table was only parsed. The final output was a table with only 20,000 records and maybe 20 columns. The summary was done into a hash table and all enrichment was done in the same step. It was possible to reduce a run time of 1 hour into 1 minute.
But this may not fit your problem: indeed, maybe you could simply adjust the memsize and sortsize options to get a huge boost in performance, without changing any code.

Re: code improvement to run fast

JJP1 — Mon, 01 Jun 2020 17:07:10 GMT

ok but code is very large. but the code is written in a efficient way only as i just checked and compared the code with the documnet on "Twenty Ways to Run Your SAS® Program Faster and Use Less
Space"

Re: code improvement to run fast

Kurt_Bremser — Mon, 01 Jun 2020 18:40:40 GMT

To make suggestions on possible improvements for your code, we need to see it. Since the log contains performance-relevant information, we need to see it too.

If you can identify single steps that take up most of the time, these need to be worked on first.

Undertaking a large job for production use with large data while having only basic knowledge is, of course, the wrong way to go. You build your skills with small tasks first, then apply the experience you gained to the bigger issues later.

If time is of the essence, I suggest you hire a consultant and then learn from the methods used by said consultant. I had some of my important early "aha" moments like this.

Re: code improvement to run fast

JJP1 — Mon, 01 Jun 2020 17:54:16 GMT

Hi @Kurt_Bremser ,

below code is taking more time(24:39.42) compared with the rest of all code and other sections of code is of seconds only maximum 7 minutes. would you please suggest any option to improve the below code

data 2a (drop=ty amt);
set ay;
by num no no1;
if ty      = 'AA' then ccb  = amt;
else if ty = 'BB'  then cles = amt;
else if ty = 'CCC' then nut  = amt;
else if ty = 'DDD' then amp   = amt;
else if ty = 'EE' then ss = amt;
else if ty = 'FFF' then rat = amt;
else if ty = 'GGG' then xcv  = amt;
else if ty = 'HHH' then wee    = amt;
else if ty = 'II' then ler  = amt;
else if ty = 'JJJ' then mit  = amt;
else if ty = 'KK' and hhhoi in ('SETL' 'SET1' 'SET2' 'SET3') then do;
if gjhrt = '16' then ghfcv = 'SD';
else ghfcv = 'ED';
end;
 
retain ccb amp cles nut ler mit
ss rat xcv wee 0 ghfcv " ";
 
if last.no1 then do;
output;
ccb  = 0;
cles = 0;
nut  = 0;
amp   = 0;
ss = 0;
rat = 0;
xcv  = 0;
wee    = 0;
ler  = 0;
mit  = 0;
ghfcv    = " ";
end;
run;
 
proc datasets lib=work memtype=data;
delete ay;
run;
quit;

Re: code improvement to run fast

Tom — Mon, 01 Jun 2020 18:31:27 GMT

Looks sort of like a transpose. Perhaps you don't need to do the transpose at all?

How do you use the dataset, 2A? Is it used just once or multiple times?

Re: code improvement to run fast

DavePrinsloo — Mon, 01 Jun 2020 18:57:37 GMT

How many records are in the input table, and how many are in the output table? Showing the log of this excerpt, plus that of the previous proc sort of AY would help a lot.

Re: code improvement to run fast

RichardDeVen — Mon, 01 Jun 2020 19:03:29 GMT

When wetware is insufficient sometimes it is more cost effective and timely to improve the hardware. More RAM, More SSD, More CPU, More Ghz

Re: code improvement to run fast

ballardw — Mon, 01 Jun 2020 20:44:45 GMT

Which value of TY is the most common?

If you have the more common values of the variable TY in that block of If/then/else then fewer ifs have to be evaluated.

For example if the JJJ were the most common value then you have to execute 9 additional statements than if the block started with

If ty = 'JJJ' then ...

Re: code improvement to run fast

RichardDeVen — Mon, 01 Jun 2020 23:09:53 GMT

I am guessing you tweaked the code somewhat because WORK.2A is not a valid data set name as coded, even with OPTIONS VALIDVARNAME=ANY.

Things to check are excessively long variables such as $4096 when $20 is sufficient.

The output data set uses option (DROP=) and could allow un-needed 'tag-along' variables in your per-group data record, an explicit (KEEP=) could be helpful.

As for

by num no no1;

How many by groups do you have ?

Is data set AY constructed elsewhere with a JOIN ? If so, the real world data might be subverting the expectations of the join criteria.

Do you have any log snippets that showing the steps

- number of observations read and written

- system memory and cpu

Get more info by setting

OPTIONS FULLSTIMER;

Log snippet should look like

NOTE: There were #### observations read from the data set ********.********
NOTE: The data set ********.******** has #### observations and #### variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      user cpu time       0.00 seconds
      system cpu time     0.01 seconds
      memory              708.40k
      OS Memory           24584.00k
      Timestamp           06/01/2020 06:56:30 PM
      Step Count                        401  Switch Count  0

Can you show the output from

Proc CONTENTS data=<name of SET data set>
Proc CONTENTS data=<name of OUTPUT data set>

Re: code improvement to run fast

mkeintz — Tue, 02 Jun 2020 02:56:56 GMT

After you create dataset 2A (an erroneous data set name), you delete data set AY.

So I presume AY is created earlier in the program, i.e. it's not a permanent data set. If so, then you might save some resources by declaring AY as a data set VIEW rather than a data set FILE (i.e. DATA AY/ view=AY; instead of DATA AY;).. This can speed things up, because AY is never written to disk. Instead its contents are dynamically generated and passed to the DATA 2A step only when the SET AY statement is encountered. If AY is not used multiple times in your program, this will likely save a lot of resources and time ordinarily needed to write data to disk only to re-read and discard it.

Re: code improvement to run fast

Patrick — Tue, 02 Jun 2020 03:22:15 GMT

My suspicion is that disk or network I/O is the bottleneck. If this data step runs for 24+ minutes AND source ay is a table and not a view then it must be high volume - really a lot of rows and variables and/or some character variables with a big length.

- Turn on option FULLSTIMER and share the log with us.

- Run a Proc Contents on ay and share the result with us.

- Drop all variables you can from input data set ay - in the set statement something like: set ay(keep=....);

- Set option COMPRESS=YES at the start of your program and test if this improves runtimes

Below some tweaks to the code you've posted. Not sure if this will improve execution time.

data _2a (drop=ty amt _ind);
  set ay;
  by num no no1;

  array a_vars {*} ccb cles nut amp ss rat xcv wee ler mit;
  _ind=WHICHC(ty, 'AA','BB','CCC','DDD','EE','FFF','GGG','HHH','II','JJJ');
  if _ind>0 then a_vars(_ind)=amt;
  else if ty = 'KK' and hhhoi in ('SETL' 'SET1' 'SET2' 'SET3') then
    do;
      if gjhrt = '16' then ghfcv = 'SD';
      else ghfcv = 'ED';
    end;

  retain ccb amp cles nut ler mit ss rat xcv wee 0 ghfcv " ";
  if last.no1 then
    do;
      output;
      ccb  = 0;
      cles = 0;
      nut  = 0;
      amp   = 0;
      ss = 0;
      rat = 0;
      xcv  = 0;
      wee    = 0;
      ler  = 0;
      mit  = 0;
      ghfcv    = " ";
    end;
run;

proc datasets lib=work memtype=data nolist;
  delete ay;
run;

Re: code improvement to run fast

ChrisNZ — Tue, 02 Jun 2020 06:38:33 GMT

1. All the comments made before are entirely valid.

The most valid one is: show us more.

If you call a garage and state that your car runs poorly and don't give more details, the chances of a useful diagnostic are zero.

2. You are showing 2 steps here. How long do they take?
To repeat the message you seem to stubbornly ignore: where's the log?

3. This code hardly does anything. Show us the log please. It looks like you must have slow disks if this simple logic takes time.

3. If you have slow disks, large (and small) tables are best stored in SPDE format with binary compression turned on. This lowers disk access, and also allows on-the-fly sorting, which is rather efficient.
Since you mention sorting as an issue (but then you show us a data step as an example of a slow step for some reason) this could be a double-win.

4. If you have data steps doing so little processing, it is possible (and common) that the code multiplies time-consuming baby steps instead of having fewer smarter steps.

6. How long does the proc datasets take? If you have slow libraries (read RDBMS, or very sadly SPDE), this deletion could take a while for no good reason. Replace the proc datasets step with:

proc delete data=AY; run;

Re: code improvement to run fast

JJP1 — Tue, 02 Jun 2020 08:18:41 GMT

HI @ChrisNZ ,

Previously i thought proc sort with out really seeing the full code.

and then i followed as @kurt suggested and found that the pasted data step taking more time compared with others sections of code please. thanks for the practical advise which i can easily understand

log part has PII info. how can i share please. how can i mask. the logs is very big please adivse

NO RDMS pure SAS tables.

Actualy i can share whole code but this has PII info.to mask i think manually it will take more time. please suggest if you have any options for me so that i can share log along with the code and to get the best help from you please

Re: code improvement to run fast

Ksharp — Tue, 02 Jun 2020 11:53:14 GMT

As ChrisNZ said, try option and spde engine(which have some drawback ) .

1)
options bufno=100 bufsize=128k compress=yes threads cpucount=4 ;

2)
libname x spde 'c:\temp';

Re: code improvement to run fast

Kurt_Bremser — Tue, 02 Jun 2020 11:57:16 GMT

If your data step throws lots of messages to the log, this can be part of the problem. If that is the case, sanitize your data steps(s) so that only the essential NOTEs are displayed:

 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds

A data step that comes back with more than these needs to be fixed. No "invalid data", "numeric converted to character", "character converted to numeric", "missing values because of ..." allowed.

If in doubt, show us the summary lines of your long-running data step; these should not contain critical information (you may want to mask the dataset names).