BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Doc_Duke
Rhodochrosite | Level 12

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

8 REPLIES 8
Reeza
Super User

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

Doc_Duke
Rhodochrosite | Level 12

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

AncaTilea
Pyrite | Level 9

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

 

DBailey
Lapis Lazuli | Level 10

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;

Mirisage
Obsidian | Level 7

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

Doc_Duke
Rhodochrosite | Level 12

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

AncaTilea
Pyrite | Level 9

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!

 

Mirisage
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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