A more efficent way of achieving same result as "calculated" proc SQL

Occasional Contributor
Posts: 10

A more efficent way of achieving same result as "calculated" proc SQL

Hi,

I currently am creating a table using proc SQL. The table has 50 columns representing days 1 - 50. All the columns use the calcualted function to get the following logic:

Result of column 2 is based on calculation in column 1

Result of column 3 is based on calculation in column 2

Result of column 4 is based on calculation in column 3

Result of column 5 is based on calculation in column 4

etc

Result of column 50 is based on calculation in column 49

This is incredibly slow to run and no doubt in efficient.  Is there a better way of doing this?

Thanks

Tom

Posts: 2,125

Re: A more efficent way of achieving same result as "calculated" proc SQL

Tom,

Tell us more, and perhaps provide an example.  You may, or may not, be able to improve it depending on what you are trying to do.  If your calculation is a simple arithmetic operation, it can readily be improved.  If it involves summary statistics, it may be much more difficult.

Doc Muhlbaier

Duke

Occasional Contributor
Posts: 10

Re: A more efficent way of achieving same result as "calculated" proc SQL

Hi,

A extract of code as follows (all the missing lines contain exactly the same logic as pattern below):

Proc SQL;

create table work.W2LCRDG as

select

domai,

carry0,

(Case whe carry0 - Avail1 < 0 the 0 else carry0 - Avail1 end) as carry1 legth = 8,

(Case whe calculated carry1 - Avail2 < 0 the 0 else calculated carry1 - Avail2 end) as carry2 legth = 8,

(Case whe calculated carry2 - Avail3 < 0 the 0 else calculated carry2 - Avail3 end) as carry3 legth = 8,

(Case whe calculated carry3 - Avail4 < 0 the 0 else calculated carry3 - Avail4 end) as carry3 legth = 8

etc

from &SYSLAST;

quit;

Thanks

Tom

Super Contributor
Posts: 339

Re: A more efficent way of achieving same result as "calculated" proc SQL

Hi,

Using max(calculated carry(n-1)-avail(n), 0) as carry(n) length=8 should already improve efficiency a lot but it may require nesting if calculated is not supported in the aggregate function.

I suppose you can kill calculated by nesting the 50 calculations but if I recall, sql only supports 32 nesting level and it would be quite dense to write the syntax, even with the use of macros. Instead, I propose the data step approach below.

If you are not bound to SQL, data step will allow you to save from typing calculated everywhere

%macro simpleloop;

data w2lcrdg;

set &syslast;

length     %do i=1 %to 50;   carry&i 8.  %end;     ;

%do i=1 %to 50;

carry&i = max(0, carry%eval(&i-1) - avail&i);

%end;

drop %do i=1 %to 50; avail&i  %end;          ;

run;

%mend;

Vincent

Super User
Posts: 23,771

Re: A more efficent way of achieving same result as "calculated" proc SQL

That looks like the job for an array in a data step.

Same solution as above, just no macro code.

data w2lcrdg;

set &syslast;

length carry1-carry50 8.;

array  carry (50) carry1-carry50   ;

array avail(50) avail1-avail50;

do i=1 to 50;

carry(i) = max(0, carry(i-1) - avail(i));

end;

drop avail:  ;

run;

Discussion stats
• 4 replies
• 182 views
• 1 like
• 4 in conversation