topic Re: how I can convert the below sql code which has nvl an decode function in sas in SAS Procedures

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

venkata_peddisetty — Tue, 15 Mar 2016 14:40:22 GMT

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

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

Reeza — Tue, 15 Mar 2016 14:57:27 GMT

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>

END

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

ballardw — Tue, 15 Mar 2016 15:00:23 GMT

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?

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

venkata_peddisetty — Tue, 15 Mar 2016 15:08:31 GMT

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...

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

DanZ — Tue, 15 Mar 2016 15:26:06 GMT

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.

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

Reeza — Tue, 15 Mar 2016 15:30:58 GMT

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

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

venkata_peddisetty — Tue, 15 Mar 2016 15:47:56 GMT

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))

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

Kurt_Bremser — Tue, 15 Mar 2016 15:55:41 GMT

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.