Help using Base SAS procedures

How could I make this code efficient?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

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

Business conditions

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

Attachment

Accepted Solutions
Solution
‎01-29-2013 02:59 PM
Trusted Advisor
Posts: 2,116

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

View solution in original post


All Replies
Super User
Posts: 19,869

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
Trusted Advisor
Posts: 2,116

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;

Smiley Happy

 

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

Trusted Advisor
Posts: 2,116

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?

Posted in reply to AncaTilea

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 413 views
  • 7 likes
  • 5 in conversation