07-16-2013 09:25 AM
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
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?
07-16-2013 09:33 AM
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.
07-16-2013 09:42 AM
A extract of code as follows (all the missing lines contain exactly the same logic as pattern below):
create table work.W2LCRDG as
(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
07-16-2013 10:35 AM
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
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);
drop %do i=1 %to 50; avail&i %end; ;
07-16-2013 10:49 AM
That looks like the job for an array in a data step.
Same solution as above, just no macro code.
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));
drop avail: ;