Use SAS macro to implement the decode function in Oracle

Function Introduction

The DECODE function is one of the powerful functions of Oracle PL/SQL. Currently, only oracle SQL provides this function, and other database vendors do not yet implement this function. What is the purpose of DECODE? 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.

Let's take a look at how SAS can be implemented. It's such a small macro that can't be implemented by other databases,

% Macro decode/PARMBUFF;
% Local I count FN 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 fn = % scan (% quote (& SYSPBUFF), % eval (& I * 2 ));
% Let valuen = % scan (% quote (& SYSPBUFF), % eval (& I * 2 + 1 ));

When & interferon 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;