## How could I make this code efficient?

Solved
Super Contributor
Posts: 338

# How could I make this code efficient?

Hi SAS Forum,

I have a SAS dataset called “have” (attached).

I reproduced the same dataset below to lead my querry.

 Have Obs BANK_NUMBER ACCOUNT_NUMBER Type_DATE TTT_TYPE Wiiii_okk_Total 1 10 111 25-Jun-09 Aaaaa Bnumb nn Bhrrrr-Hff: 66 1,250 2 10 222 6-Jul-10 Aaaaa Bnumb nn Bhrrrr-Hff: 66 -250 3 10 444 3-Jun-11 Aaaaa Bnumb nn Bhrrrr-Hff: 66 0 4 10 444 3-Jun-11 Aaaaa Bnumb nn Bhrrrr-Hff: 66 -50 5 10 444 3-Jun-11 Khgrgi-lff Peveksel: 3 -300 6 10 888 25-Jun-09 Aaaaa Bnumb nn Bhrrrr-Hff: 66 100 7 10 888 12-Feb-10 Khgrgi-lff Peveksel: 3 200 8 10 888 28-Dec-10 Aaaaa Bnumb nn Bhrrrr-Hff: 66 -25 9 10 55555555 3-Jun-11 Aaaaa Bnumb nn Bhrrrr-Hff: 66 125 10 10 55555555 3-Jun-11 Khgrgi-lff Peveksel: 3 -40

These are the 2 business conditions that all records of this dataset should obey.

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" then Wiiii_okk_Total should be LE 0

if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" then Wiiii_okk_Total should be GE 0

Record 7 should have satisfied out first condition above but it violates the condition. So, I need to make Wiiii_okk_Total of record 7 equals to -200 to obey our condition.

Records 2, 4, 8 should have satisfied our second condition above but they violate the conditions. So, I need to make Wiiii_okk_Total of records 2, 4, and 8 equals respectably to 250, 50 and 25 to obey our condition.

In sum, below is the dataset that I want finally.

Want

 Obs BANK_NUMBER ACCOUNT_NUMBER Type_DATE TTT_TYPE Wiiii_okk_Total 1 10 111 25Jun2009 Aaaaa Bnumb nn Bhrrrr-Hff: 66 1250 2 10 222 06Jul2010 Aaaaa Bnumb nn Bhrrrr-Hff: 66 250 3 10 444 03Jun2011 Aaaaa Bnumb nn Bhrrrr-Hff: 66 0 4 10 444 03Jun2011 Aaaaa Bnumb nn Bhrrrr-Hff: 66 50 5 10 444 03Jun2011 Khgrgi-lff Peveksel: 3 -300 6 10 888 25Jun2009 Aaaaa Bnumb nn Bhrrrr-Hff: 66 100 7 10 888 12Feb2010 Khgrgi-lff Peveksel: 3 -200 8 10 888 28Dec2010 Aaaaa Bnumb nn Bhrrrr-Hff: 66 25 9 10 55555555 03Jun2011 Aaaaa Bnumb nn Bhrrrr-Hff: 66 125 10 10 55555555 03Jun2011 Khgrgi-lff Peveksel: 3 -40

My approach

data zero;

set a.have;

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Temp_Wiiii_okk_Total=  Wiiii_okk_Total;

run;

data first;

set zero ;

if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Temp_Wiiii_okk_Total = Temp_Wiiii_okk_Total;

drop Wiiii_okk_Total;

run;

data want (rename =  (Temp_Wiiii_okk_Total = Wiiii_okk_Total ));

set first;

run;

Question

My code generates the “want” table correctly but my code is very inefficient.

Could any expert suggest me a way to make it efficient?

Thanks

Mirisage

Accepted Solutions
Solution
‎01-29-2013 02:59 PM
Posts: 2,125

## Re: How could I make this code efficient?

Just combine the steps and remove the temporary variables and the else statement.

data want;

set a.have;

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Wiiii_okk_Total =Wiiii_okk_Total * -1;

if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Wiiii_okk_Total =Wiiii_okk_Total * -1;

run;

The "then" clause does a replacement only on the exception condition.

You might change the "* -1" to an infix "-" operator, but I don't know whether that would make it much more efficient.

Doc Muhlbaier

Duke

All Replies
Super User
Posts: 23,747

## Re: How could I make this code efficient?

Why do you have 3 steps? combine them to one first and see how it runs.

Solution
‎01-29-2013 02:59 PM
Posts: 2,125

## Re: How could I make this code efficient?

Just combine the steps and remove the temporary variables and the else statement.

data want;

set a.have;

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Wiiii_okk_Total =Wiiii_okk_Total * -1;

if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Wiiii_okk_Total =Wiiii_okk_Total * -1;

run;

The "then" clause does a replacement only on the exception condition.

You might change the "* -1" to an infix "-" operator, but I don't know whether that would make it much more efficient.

Doc Muhlbaier

Duke

Super Contributor
Posts: 543

## Re: How could I make this code efficient?

One simple way would be: as Fareeza suggested

data zero(rename =  (Temp_Wiiii_okk_Total = Wiiii_okk_Total ));

set a.have;

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Temp_Wiiii_okk_Total=  Wiiii_okk_Total;

if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Temp_Wiiii_okk_Total = Temp_Wiiii_okk_Total;

drop Wiiii_okk_Total;

run;

Super Contributor
Posts: 578

## Re: How could I make this code efficient?

or...if you didn't want to have a different dataset at the end:

proc sql;

update have

set Wiiii_okk_Total = Wiiii_okk_Total * -1

where

(TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0)

or

(TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0)

;

quit;

Super Contributor
Posts: 338

## Re: How could I make this code efficient?

Hi Everyone,

Really appreciate every one of your help.

Hi Doc@Duke,

Thank you very much, your code works correctly.

I am asking this for my curiosity.

I have just tweaked your code in places highlighted in yellow.

Still the code produced correct results.

I am not too sure if the correct results of the tweaked code are due to the correctness of the logic or due to the limited data set I have presented.

Could you shed some light?

data want;

set a.have;

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Wiiii_okk_Total =Wiiii_okk_Total * -1;

else  if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Wiiii_okk_Total =Wiiii_okk_Total;

run;

Hi AncaTilea,

Thank you very much for the code which works correctly for the dataset I provided.

I have changed your code by adding “else” phrase (see yellow), and then code did not work.

Could you please elaborate a bit why it should have to be “if” but not “else if”?

Also, the green highlighted place should be “Wiiii_okk_Total”, isn’t it?

data zero(rename =  (Temp_Wiiii_okk_Total = Wiiii_okk_Total ));

set a.have;

if TTT_TYPE  = "Khgrgi-lff Peveksel: 3" and Wiiii_okk_Total > 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Temp_Wiiii_okk_Total=  Wiiii_okk_Total;

else if TTT_TYPE  = "Aaaaa Bnumb nn Bhrrrr-Hff: 66" and Wiiii_okk_Total < 0 then Temp_Wiiii_okk_Total =Wiiii_okk_Total * -1;

else Temp_Wiiii_okk_Total = Temp_Wiiii_okk_Total;

drop Wiiii_okk_Total;

run;

Hi DBailey,

Thank you very much; I now came to know the how “update” functions. Your code works nicely.

Mirisage

Posts: 2,125

## Re: How could I make this code efficient?

Mirisage,

In your update, the first "else" does make the code more efficient as the second if statement is only executed when the first one fails.  The second "ELSE" statement is unnecessary (as it is assigning the total to itself).

If you want to make it even more efficient, you could test the condition that is more likely to be true first as that will minimize the total number of statements executed.  In general, that sort of thing is only noticeable if there are millions of records to process.  I generally prefer clarity of code over raw CPU efficiency.

Doc Muhlbaier

Duke

Super Contributor
Posts: 543

## Re: How could I make this code efficient?

Hi Mirisage.

You are correct, the green highlighted place should be “Wiiii_okk_Total",that was a typo.

For the IF/THEN-IF vs. IF/THEN - ELSE IF/THEN:

...

if type = "a" and wii > 0 then do this;

if type = "b" and wii < 0 then do this;

VS.

if type = "a" and wii > 0 then do this;

else if type = "b" and wii < 0 then do this;

I think that this (may) be the reason things work or not:

The first scenario (no else-if) tells SAS that

if this condition is true, then do this.

after that, independent of the previous statement being true or not, check this new condition (we use the second if statement).

OK, for the second scenario:

we tell SAS if this statement is true then do something or other.

ELSE IF do something....this forces SAS to only check this new IF statement if the prior IF (the one above) is not true.

SO...

if type = "a" and wii > 0 then do this; * may or may not be true;

if type = "b" and wii < 0 then do this;* may or may not be true...However, SAS executes both;

VS.

if type = "a" and wii > 0 then do this; * may or may not be true;

else if type = "b" and wii < 0 then do this;*this will only get executed when the line above is FALSE.

The end.

Good luck!

Super Contributor
Posts: 338

## Re: How could I make this code efficient?

Hi Doc Muhlbaier,

Thank you very much for sharing this additional knowledge on the subject.

Hi Anca tilea,

Thank you very much for these details.

Regards

Mirisage

🔒 This topic is solved and locked.