BookmarkSubscribeRSS Feed
qwererty
Calcite | Level 5

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

4 REPLIES 4
Doc_Duke
Rhodochrosite | Level 12

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

qwererty
Calcite | Level 5

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

Vince28_Statcan
Quartz | Level 8

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

Reeza
Super User

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;

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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