topic Re: How to speed up a program ? in SAS Programming

How to speed up a program ?

Hugo_B — Mon, 20 May 2019 14:07:38 GMT

Hello I built a program that perform two nested loop in a pretty large table; the program takes a lot of time to be run.

At some point SAS EG alerted me that the log was to big, and that the log will not be printed. After the message appeared the program started to run faster.

Can I speed up my program by not printing the log ? If yes was is the option to do that ?

Is there any other way to speed up a program?

Regards

Re: How to speed up a program ?

PaigeMiller — Mon, 20 May 2019 14:15:46 GMT

You can use PROC PRINTTO to send the log to a file. Here is an example of doing this if you are using Windows:

proc printto log = "c:\data\users\pmiller\temp.log" new; run;
/* Your SAS code goes here */
proc printto; run;

I don't know if this will speed things up enough for you, but you won't get the message that the log window is full.

There are other reasons why your code might take a long time, and the two nested loops may or may not be part of the problem. If you can replace the loops with BY statements, that would speed things up a lot. (But sometimes that is not possible)

In addition, you should examine the log to determine which steps are taking up the most time, and see if there are ways to speed up that particular step.

Re: How to speed up a program ?

VDD — Mon, 20 May 2019 14:13:24 GMT

this code helps me a lot with logging.

ods html close; 
DM log "OUT;CLEAR;LOG;CLEAR;" log continue ;
DM log 'next results; clear; cancel;' whostedit continue ;
ods html newfile=none;

Re: How to speed up a program ?

Hugo_B — Mon, 20 May 2019 14:24:47 GMT

Hello
Can you explain what it does ?
Thank you

Re: How to speed up a program ?

PaigeMiller — Mon, 20 May 2019 14:27:19 GMT

Another thing that can speed up a program (not all programs, but many)

ods graphics off;

This assumes that you don't need ODS GRAPHICS in your program.

Re: How to speed up a program ?

andreas_lds — Mon, 20 May 2019 15:04:46 GMT

There could be techniques to speed up your program, but without seeing the code, it is difficult to suggest something useful.

Re: How to speed up a program ?

smijoss1 — Mon, 20 May 2019 15:08:56 GMT

1) are you using a lot of put statements ? (i generally comment most of them out when promoting)

2) is your Large table a SAS Table OR residing on some database server like oracle. if you do a federated join your performance will be bad.

3) if your dataset is on SAS and program is calculation intensive, then you must check out Proc DS2 for multi-thread processing.

Re: How to speed up a program ?

Hugo_B — Mon, 20 May 2019 15:55:08 GMT

Here is a simplified version of what I do :

%MACRO MacroProgram;

%let i=1;

data TableWant;
Lenght Variable1 $200;
run;

%DO %WHILE (&i<150);

data _null_;
set TableA;
if _N_=&i then 
call symput('MacroVariable',SomeVariableFromTableA)
run;

data TEMP;
VariableTableTemp=&Macrovariable;
run;

Proc Append base=TableWant data=TEMP;

%let i=%eval(&i+1);

%mend;

There is a 45000+ notes in the log saying:

-that some variables are uninitialized;

-some numeric values have been converted ro characters values;

Maybe this is the reason why it takes time.

Re: How to speed up a program ?

smijoss1 — Mon, 20 May 2019 16:08:09 GMT

Are you checking if the variable exists on your source dataset.

the uninitialized is because the variable does not exist.

and the numeric to char warning is because Symput is converting it to char.

is your TableA that is pretty large ? ( i feel using IF is inefficient there thus asking)

Looking at you code are you trying to fetch the first value of some column for the first 150 rows ? (OR a range of rows ?)

Re: How to speed up a program ?

Hugo_B — Mon, 20 May 2019 16:15:05 GMT

What can I change in this code to avoid unitialized variables :

DATA TEMP;
LENGTH VARIABLE1-VARIABLE10 $200;
OUTPUT;
RUN;

By doing this i want to "initialize" the TEMP TABLE before adding values later in the macro program, but I feel that it's wrong.

What do you think ?

Re: How to speed up a program ?

smijoss1 — Mon, 20 May 2019 16:51:56 GMT

I am assuming you need a table without any rows for initialization

DATA TEMP;
LENGTH VARIABLE1-VARIABLE10 $200;

call missing(of VARIABLE1-VARIABLE10);

STOP;
RUN;

I still feel the log isnt reason for your performance. but give it a shot.

Re: How to speed up a program ?

Hugo_B — Mon, 20 May 2019 16:55:51 GMT

%MACRO MYPROGRAM;

%LET I=1;

%DO %WHILE (&i<200)

DATA _NULL_;
SET TABLEA;
IF _N_=&i THEN CALL SYMPUT('MACROVARIABLE',VarThatINeed);
RUN;

DATA TEMP;
SET TEMP;
VARIABLE=SYMGET('MACROVARIABLE');
RUN;

PROC APPEND BASE=TABLEWANT DATA=TEMP;

%LET &I=%EVAL(&i+1)
%MEND;

I am pretty sure the IF in the data step is not efficient, is there a way to point directly to i th observation without using " if _n_=&i " ?

Re: How to speed up a program ?

smijoss1 — Mon, 20 May 2019 17:20:47 GMT

pointing to Nth observation - you can use POINTOBS

But going back to my earlier question - are you just trying to fetch the values of certain columns for first 150 or 200 rows
(or a range like between row# 100 and 200 )

Re: How to speed up a program ?

Hugo_B — Mon, 20 May 2019 17:29:16 GMT

All rows, but one row at a time, one single row at each stage of the loop and using 5 columns/variables for every row.

I am using SAS EG GRID running on server, can it be a reason for slowness ?

Re: How to speed up a program ?

Hugo_B — Mon, 20 May 2019 17:31:59 GMT

The values of those 5 columns/variables are stored in macro variables, and used in the TEMP TABLE and append step.

Re: How to speed up a program ?

smijoss1 — Mon, 20 May 2019 17:38:56 GMT

I dont think SAS EG is the reason. I use SAS EG on Grid and it works fine for me ..

I am curious as to why you are processing 1 Row at a time and how many Rows do you have in the table ...

Depending on your answer i cud tell if it could be written better way.

i do similar macro based fetch when i have to fetch from Oracle, But i always do it in sets of 1000 at a time

Re: How to speed up a program ?

Astounding — Mon, 20 May 2019 20:37:00 GMT

It's curious that you are using 600 steps instead of 2 steps, and then wondering about why the program takes so long.

You only posted the idea of what the program should do, so here's the idea of how to fix it:

data subset_a;
   set tablea (obs=200 keep=VarThatINeed);
   ordervar = _n_;
run;

proc sql;
   create table want as select a.*, b.* from subset_a a, temp b
   order by ordervar;
quit;

Re: How to speed up a program ?

ChrisNZ — Mon, 20 May 2019 22:25:49 GMT

DATA TEMP;
LENGTH VARIABLE1-VARIABLE10 $200;
OUTPUT;
RUN;

is normally better coded as

data TEMP;
  if 0 then set SOURCETAB (keep=VARIABLE1-VARIABLE10);
run;

as this copies the original structure.

Also, $200 variables create long records, and SAS is not efficient dealing with these. Shorten the variables if you can. And compress the tables.

Re: How to speed up a program ?

ChrisNZ — Mon, 20 May 2019 22:28:39 GMT

This whole macro

%MACRO MacroProgram;

%let i=1;

data TableWant;Lenght Variable1 $200;run;

%DO %WHILE (&i<150);

data _null_; set TableA; if _N_=&i then call symput('MacroVariable',SomeVariableFromTableA) run; data TEMP; VariableTableTemp=&Macrovariable; run; Proc Append base=TableWant data=TEMP;

is identical to this step

data TableWant ; 
  set TableA (obs=150 keep=SomeVariableFromTableA);
  VariableTableTemp=SomeVariableFromTableA;
run;

This does not represent your processing I suppose, but do reduce the number of steps to speed up the process.

Re: How to speed up a program ?

VDD — Mon, 20 May 2019 22:30:23 GMT

new user don't really understand KISS. Keep It Simple Stupid.