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
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
Why do you have 3 steps? combine them to one first and see how it runs.
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
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;
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;
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
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
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.