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

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:

 
 

  
  
%macro decode /PARMBUFF;  
  
  
%local i count ifn valuen countall currfeild valueelse;  
  
  
%let countall=%sysfunc(countw(&SYSPBUFF,%quote(,)));  
  
  
%let count=%eval((&countall-1)/2);  
  
  
%let currfeild=%scan(%quote(&SYSPBUFF),1);  
  
  
case &currfeild   
  
  
%do i=1 %to &count;  
  
  
%let ifn=%scan(%quote(&SYSPBUFF),%eval(&i*2));  
  
  
%let valuen=%scan(%quote(&SYSPBUFF),%eval(&i*2+1));  
  
  
when &ifn then &valuen  
  
  
%end;  
  
  
%if %eval(&countall>(&count+1)) %then %do;  
  
  
%let valueelse=%scan(%quote(&SYSPBUFF),&countall);  
  
  
else &valueelse  
  
  
%end;  
  
  
end  
  
  
%mend; 
 
 

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

 
 

  
  
Proc SQL;
  
  
Create table test
  
  
Select
  
  
% Decode (sex, "male", 0, "female", 1, 2)
  
  
As abc
  
  
From sashelp. class;
  
  
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!