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
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
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
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.