topic Re: A more efficent way of achieving same result as "calculated" proc SQL in SAS Procedures

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

qwererty — Tue, 16 Jul 2013 13:25:54 GMT

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

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

Doc_Duke — Tue, 16 Jul 2013 13:33:32 GMT

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

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

qwererty — Tue, 16 Jul 2013 13:42:38 GMT

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

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

Vince28_Statcan — Tue, 16 Jul 2013 14:35:32 GMT

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

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

Reeza — Tue, 16 Jul 2013 14:49:29 GMT

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;