turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- A more efficent way of achieving same result as "c...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2013 09:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to qwererty

07-16-2013 09:33 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Doc_Duke

07-16-2013 09:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to qwererty

07-16-2013 10:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to qwererty

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.

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;