topic Re: How could I make this code efficient? in SAS Procedures

How could I make this code efficient?

Mirisage — Tue, 29 Jan 2013 19:53:19 GMT

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

Re: How could I make this code efficient?

Reeza — Tue, 29 Jan 2013 19:57:52 GMT

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

Re: How could I make this code efficient?

Doc_Duke — Tue, 29 Jan 2013 19:59:39 GMT

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

Re: How could I make this code efficient?

AncaTilea — Tue, 29 Jan 2013 20:00:06 GMT

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;

Re: How could I make this code efficient?

DBailey — Tue, 29 Jan 2013 20:10:04 GMT

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)

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

;

quit;

Re: How could I make this code efficient?

Mirisage — Thu, 31 Jan 2013 13:34:38 GMT

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

Re: How could I make this code efficient?

Doc_Duke — Thu, 31 Jan 2013 14:57:41 GMT

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

Re: How could I make this code efficient?

AncaTilea — Fri, 01 Feb 2013 15:19:40 GMT

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!

Re: How could I make this code efficient?

Mirisage — Mon, 04 Feb 2013 13:28:52 GMT

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