How to Use SAS macro to implement the decode function in Oracle?

Source: Internet
Author: User

OracleThe decode function in the database is one of the common functions of Oracle PL/SQL. What is its purpose? Next we will introduce this process and how to use it.SAS macroTo implement the decode function in the Oracle database.

First, let's construct an example. If we want to add a salary to a staff member of zhixing, the standard is: the salary is less than 8000 RMB plus 20%; the salary is more than 8000 RMB plus 15%, usually, select the salary field value in the record first? Select salary into var-salary from employee, and then use if-then-else or choose case to judge the variable var-salary. If the decode function is used, we can omit these flow control statements and directly complete them through SQL statements.

SAS macro implementation method:

 
 
  1. %macro decode /PARMBUFF;  
  2. %local i count ifn valuen countall currfeild valueelse;  
  3. %let countall=%sysfunc(countw(&SYSPBUFF,%quote(,)));  
  4. %let count=%eval((&countall-1)/2);  
  5. %let currfeild=%scan(%quote(&SYSPBUFF),1);  
  6. case &currfeild   
  7. %do i=1 %to &count;  
  8. %let ifn=%scan(%quote(&SYSPBUFF),%eval(&i*2));  
  9. %let valuen=%scan(%quote(&SYSPBUFF),%eval(&i*2+1));  
  10. when &ifn then &valuen  
  11. %end;  
  12. %if %eval(&countall>(&count+1)) %then %do;  
  13. %let valueelse=%scan(%quote(&SYSPBUFF),&countall);  
  14. else &valueelse  
  15. %end;  
  16. end  
  17. %mend; 

The calling code is as follows, which is basically the same as the calling method and function implementation of oracle.

 
 
  1. Proc SQL;
  2. Create table test
  3. Select
  4. % Decode (sex, "male", 0, "female", 1, 2)
  5. As abc
  6. From sashelp. class;
  7. Quit;

The above is the usage of the decode function in the Oracle database and the method for implementing the decode function using the SAS macro. We will introduce it here. I hope this introduction will be helpful to you!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.