Below is the SAS code which I need to convert into an SQL query:

data table2 (keep = Date id Differential);
   set table1 
   Differential = ReturnsGross-ReturnsNet;
   where ((ReturnsGross ^=. and ReturnsNet ^=.) as Differential 
run;

Corresponding SQL code: I have imported the input table(table1) in dataiku platform

select Date, id,
CASE WHEN(ReturnsGross !='' AND ReturnsNet !='')
THEN (CAST(ReturnsGross AS INT)-CAST(ReturnsNet AS INT)) END AS Differential
FROM table1 

[ReturnsGross, ReturnsNet are string columns of numeric values inside brackets]

Here I am getting the Error as Numeric value (5.25%) is not recognized

The input dataset value is like below:

CodePudding user response：

Hi use REPLACE in the column having the % sign and then do the mathematical operation, it may work

select replace('1.2345%','%') ;

CodePudding user response：

so a series of steps we can transform the text input into correct numbers

SELECT 
    column1 as id
    ,column2 as returns_gross_txt
    ,column3 as returns_net_txt
    ,trim(returns_gross_txt, '()% ') as t_returns_gross
    ,trim(returns_net_txt, '()% ') as t_returns_net
    ,try_to_double(t_returns_gross)/100 as returns_gross
    ,try_to_double(t_returns_net)/100 as returns_net
FROM VALUES 
(1, '( 0.00%)', '( 0.22%)'),
(1, '( 0.00%)', '( 0.52%)'),
(1, '( 0.00%)', '( -0.82%)')
;

gives:

and then we can compact that up a little

SELECT 
    column1 as id
    ,try_to_double(trim(column2, '()% '))/100 as returns_gross
    ,try_to_double(trim(column3, '()% '))/100 as returns_net
FROM VALUES 
(1, '( 0.00%)', '( 0.22%)'),
(1, '( 0.00%)', '( 0.52%)'),
(1, '( 0.00%)', '( -0.82%)')
;

once you have you values as numbers, you can now do subtraction:

SELECT 
    column1 as id
    ,try_to_double(trim(column2, '()% '))/100 as returns_gross
    ,try_to_double(trim(column3, '()% '))/100 as returns_net
    ,returns_gross - returns_net as Differential
FROM VALUES 
(1, '( 1.00%)', '( 0.22%)'),
(2, '( 2.00%)', '( 0.52%)'),
(3, '( 3.00%)', '( -0.82%)')
;

giving: