Coalesce

[dkassis]

Hi,

Getting an error when using the coalesce function below that works with standard ANSI SQL. Any ideas how to modify?

proc sql;
create table xxxxxxxxxxx as

select lp.epoch_interval_time,
divide ( mod ( lp.epoch_interval_time - 7*3600 , 86400 ), 3600 ) as intv_hr,
divide ( mod ( lp.epoch_interval_time - 7*3600 , 86400 ), 3600 )+ 1 as intrvl_24hr_ending,
lp.local_interval_time,
lp.utc_interval_time,
lp.lp_value,
lp.validation_status,
lp.lp_interval_length,
sdps.udc_id as sp
from xxxxxxxxxxxxxxx
join xxxxxxxxxxxxxxxxxx on lp.channel_id = chn.channel_id
and chn.channel_type = 'Interval Data'
and chn.physical_channel_no in (1,11,21)

join xxxxxxxxxxxxx on chn.sdp_id = sdps.sdp_id
where lp.utc_interval_yr_mth in (201910,201909)
AND (lp.utc_interval_time >= chn.start_date AND lp.utc_interval_time <= COALESCE(chn.end_date, CAST('2099-01-01 01:00:00' as TIMESTAMP)))
AND sdps.meter_id = chn.meter_id
AND (lp.utc_interval_time >= sdps.start_date AND lp.utc_interval_time <= COALESCE(sdps.end_date, CAST('2099-01-01 01:00:00' as TIMESTAMP)))

Thanks,

Dan

[Reeza]

Is it COALESCE or CAST() that's causing the issue? SAS doesn't have a CAST function, you would use PUT() or INPUT() there depending on what you're trying to do. The actual error would indicate where the issue is.

[ChrisNZ]

cast('2099-01-01 01:00:00' as TIMESTAMP)

is written

'01jan2099:01:00:00'dt

in SAS.

Please look at your log messages more carefully, I suspect SAS complained loudly about your calling a non-existing function.

[dkassis]

I suspect this is a cast issue.  Here is the error I am getting:

__
22
202
ERROR 22-322: Syntax error, expecting one of the following: !,
!!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >,
>=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET,
GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET,
NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and
will be ignored.

47 AND sdps.meter_id = chn.meter_id
48 AND (lp.utc_interval_time >= sdps.start_date AND
48 ! lp.utc_interval_time <=
48 ! COALESCE(sdps.end_date,'01jan2099:01:00:00'dt
48 ! /*CAST('2099-01-01 01:00:00'*/ as TIMESTAMP))
__
22
202
ERROR 22-322: Syntax error, expecting one of the following: !,
!!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >,
>=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET,
GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET,
NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and
will be ignored.

[Patrick · Accepted solution]

Are you now just showing us or do you still not know how to fix the issue.

From the log you've posted: 

Always show the very first error in the log. You haven't posted the code which causes the first error.

For the 2nd error where you also show the code: You haven't commented out correctly.

What you've done:

COALESCE(sdps.end_date,'01jan2099:01:00:00'dt /*CAST('2099-01-01 01:00:00'*/ as TIMESTAMP))

How it should look like:

COALESCE(sdps.end_date,'01jan2099:01:00:00'dt /*CAST('2099-01-01 01:00:00' as TIMESTAMP)*/)

[ChrisNZ]

Did you replace the cast() value as shown?

[dkassis]

Sorry for the confusion, when I commented out the cast the error went away.  

[Tom]

Code seem over complicated, especially that mod/div stuff.  Looks like you are just trying to pull out the HOUR, after subtracting 7 hours.

proc sql;
create table xxxxxxxxxxxxxxx as
select lp.epoch_interval_time
     , hour(intnx('hour',lp.epoch_interval_time,-7)) as intv_hr
     , calculated intv_hr + 1 as intrvl_24hr_ending
     , lp.local_interval_time
     , lp.utc_interval_time
     , lp.lp_value
     , lp.validation_status
     , lp.lp_interval_length
     , sdps.udc_id as sp
from xxxxxxxxxxxxxxx lp
join xxxxxxxxxxxxxxx chn
  on lp.channel_id = chn.channel_id
 and chn.channel_type = 'Interval Data'
 and chn.physical_channel_no in (1,11,21)
join xxxxxxxxxxxxxxx sdps
  on chn.sdp_id = sdps.sdp_id
where lp.utc_interval_yr_mth in (201910,201909)
  AND lp.utc_interval_time >= chn.start_date
  AND lp.utc_interval_time <= COALESCE(chn.end_date, "01JAN2099:01:00"dt)
  AND sdps.meter_id = chn.meter_id
  AND lp.utc_interval_time >= sdps.start_date
  AND lp.utc_interval_time <= COALESCE(sdps.end_date, "01JAN2099:01:00"dt)
;
quit;

Why are some of the conditions in the WHERE clause not included into the ON clause? Like:

  AND sdps.meter_id = chn.meter_id

[dkassis]

To add a little more context, this data is pulling from a Hadoop platform.  I have to convert EPOCH time (integer) to readable time hence the calculation.  I am working on developing and testing a new table structure that extracts 15 minute interval data and then will roll it up to hourly and daily data in subsequent tables I am doing those steps in a series of proc means with a limited amount of data just for testing purposes. 

I got the code above from my ETL developer who doesn't use SAS but BDM or DB visualizer, so I have to translate to SAS.  I want to ensure all the code runs in hadoop for optimal performance so I want to make sure I use supported functions within the SAS/ACCESS engine.  

The above code won't run entirely in Hadoop but I have what I need for now until my developer gets back.  Thanks for all the help.

Thanks,

Dan

[ChrisNZ]

> ensure all the code runs in hadoop for optimal performance

Why don't you use the Hadoop code in a pass-through query then? Just an idea.

[Patrick]

"I got the code above from my ETL developer who doesn't use SAS but BDM or DB visualizer, so I have to translate to SAS.  I want to ensure all the code runs in hadoop for optimal performance so I want to make sure I use supported functions within the SAS/ACCESS engine."

The SAS Access engines allow you to pass code directly to a database for execution (explicit pass-through). So if you've already got working Hive SQL then that's what I'd be doing: Just copy/paste this Hive SQL into the EXECUTE block.

Also: If you want everything working in Hadoop and you then want to hand-over this code for implementation not using SAS at all then I'd be using Hue or DBeaver for development of Hive SQL.