Converting a String to a Date connected to a Hive

[Klapatauskas]

I am using a PROC SQL in SAS EG connected to a Hive. I am trying to take this 

Substring(CLM.etl_file_name,(length(substring_index(CLM.etl_file_name,'_',1))-7),8)

and turn it in to a date. The original data element is a long string of characters to create a file name. I've tried to use CAST 

CAST(Substring(CLM.etl_file_name,(length(substring_index(CLM.etl_file_name,'_',1))-7),8)as DATE) as myETLdate

but it returns every value as a decimal point or period...call it a dot

If I use Convert

Convert(Substring(CLM.etl_file_name,(length(substring_index(CLM.etl_file_name,'_',1))-7),8), DATE)

I get this message back

CLI prepare error: [Hortonworks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error
message from server: Error while compiling statement: FAILED: ParseException line 1:126 cannot recognize input near 'DATE'
2 The SAS System 07:51 Friday, December 27, 2019

')' 'as' in expression specification

Any idea what will work? I think it might have something to do with the connection to Hive. I've come across one other situation where Hive wanted me to use a different language than what I would normally use with SQL.

[ballardw]

Please provide an example of the variable. We have no clue if what you are attempting is appropriate.

You may also want to provide an example of how you are connecting to the Hive as others may have some ideas.

And lastly do you have an example of passing a date that does not involve any variables that works? If so show that.

[Patrick]

Looking at the code you've posted I assume you're using explicit pass-through SQL.

I normally prefer to make such explicit pass-through SQL work outside of SAS (i.e. using HUE) and then copy valid and tested SQL into a SAS pass-through block.

You haven't posted a sample filename. Based on your code I assume it looks something like: 
someChars-20191228_moreChars

Looking into what you've posted I believe you've got two issues here:

1. You're extracting the date string part of the file name

2. The date string is not suitable for a direct cast() because Hive requires a date pattern of YYYY-MM-DD for this to work

I suggest you first get the date string extraction working and only then add the conversion to a date. I can't test it but I believe below could eventually do the job.

substr(substring_index(CLM.etl_file_name,'_',1),-10) as date_string

IF your target table is in SAS then you could also do the conversion from string to SAS date or datetime value on the SAS side.