Help using Base SAS procedures

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

Reply
Contributor
Posts: 22

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

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

Super User
Posts: 19,771

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

Posted in reply to venkata_peddisetty

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

 

 

Super User
Posts: 11,343

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

Posted in reply to venkata_peddisetty

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?

Contributor
Posts: 22

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

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

Super User
Posts: 19,771

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

Posted in reply to venkata_peddisetty

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

 

 

Contributor
Posts: 38

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

Posted in reply to venkata_peddisetty

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.

 

 

Contributor
Posts: 22

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

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

Super User
Posts: 7,762

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

Posted in reply to venkata_peddisetty

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 7 replies
  • 2601 views
  • 1 like
  • 5 in conversation