BookmarkSubscribeRSS Feed
venkata_peddisetty
Calcite | Level 5

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

7 REPLIES 7
Reeza
Super User

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
Super User

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
Calcite | Level 5

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
Super User

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

 

 

DanZ
Obsidian | Level 7

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
Calcite | Level 5

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 11685 views
  • 1 like
  • 5 in conversation