how I can convert the below sql code which has nvl an decode function in sas

[venkata_peddisetty]

sum(

nvl(decode(JOB_TYPE,'B',0,

decode(TASK_IND,'Y',

decode(sign((90-( _ARRIVE_TSK_UPD/60 ))),-1,0,

decode(( Case

                                when TASK_IND = 'N' or Completion_Code = '00'

                                then 'N'

                                else 'Y'

                end ),'Y',1,0)),0))

,0)),

thanks in advance.... venkat

[Reeza]

Are you intending to continue with a SQL query, ie PROC SQL or a data step?

If SQL a CASE statement will be equivalent, though more typing.

The NVL is equivalent to the COALESCE function I think. 

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473682.htm

CASE <case-operand>

WHEN when-condition THEN result-expression

<...WHEN when-condition THEN result-expression>

<ELSE result-expression>

END

[ballardw]

Have to assume you are translating ORACLE sql to SAS.

The SAS Coalesce would be the SAS function to use inplace of NLV.

But the quesiton might be are you attempting to use SAS Passthrough to Oracle or accomplish the same affects within SAS?

[venkata_peddisetty]

no, I am not using pass through, i am just trying to conver oracle code into sas.

tried with coalesce and case but it ended with errors...

[Reeza]

If something doesn't work, please post your code and log.

[DanZ]

I haven't tested this, but stepping through it with something like this might

data output;
set input;
if job_type = 'B' then rc1=0;
if sign((90-(_arrive_tsk_upd/60))) = -1 then rc2=0;
if task_ind = 'N' or completion_code = '00' then rc3=0;
	else rc3=1;
field = coalesce(rc1,rc2,rc3);
run;

proc sql;
create table want as
sum(field) as field
from output
;
quit;

help with troubleshooting.

[venkata_peddisetty]

Thank you very much to all and especially to Danz,

can I execute the same as below:

Sum(COALESCE (case
When JOB_TYPE ='B' then 0 end,
case When TASK_IND='Y' then 1 end,
case when (sign(90- _ARRIVE_TASK_UPD )) =-1 then 0 end,
case when FEEDBACK_IND='N' then 0
else 1
end))

[Kurt_Bremser]

I'd say, try it and see if it works. Even if it doesn't work, you either get error messages or unexpected results that can be used for further investigation.

Make use of the fact that SAS, as a mostly interpreted language, lends itself well to quick testing.