PostgreSQL code translate into FEDSQL or SAS

[Zula]

Hi. I would like to write this postgreSQL code:

cast(to_timestamp( (to_char( chartdate, 'DD-MM-YYYY' ) || substring(ne.text, 'Date/Time: [\[\]0-9*-]+ at ([0-9:]+)')),
            'DD-MM-YYYYHH24:MI') as timestamp without time zone)
    as charttime

INTO FEDSQL or SAS SQL. Can anyone help?

Thank you. 

[Reeza]

You'll get faster responses if you post what the data looks like and what you need it to do instead. Some test data is helpful either way. 

 A rough guess, you're parsing some text field to get the time or date component, not sure. 

---

@Zula wrote:

Hi. I would like to write this postgreSQL code:

cast(to_timestamp( (to_char( chartdate, 'DD-MM-YYYY' ) || substring(ne.text, 'Date/Time: [\[\]0-9*-]+ at ([0-9:]+)')),
            'DD-MM-YYYYHH24:MI') as timestamp without time zone)
    as charttime

INTO FEDSQL or SAS SQL. Can anyone help?

Thank you. 

---

[Zula]

Thanks. 

See below comments:

-- charttime is always null for echoes..
  -- however, the time is available in the echo text, e.g.:
  -- , substring(ne.text, 'Date/Time: [\[\]0-9*-]+ at ([0-9:]+)') as TIMESTAMP
  -- we can therefore impute it and re-create charttime
  , cast(to_timestamp( (to_char( chartdate, 'DD-MM-YYYY' ) || substring(ne.text, 'Date/Time: [\[\]0-9*-]+ at ([0-9:]+)')),
            'DD-MM-YYYYHH24:MI') as timestamp without time zone)
    as charttime

 Also, see below:

  -- explanation of below substring:
  --  'Indication: ' - matched verbatim
  --  (.*?) - match any character
  --  \n - the end of the line
  -- substring only returns the item in ()s
  -- note: the '?' makes it non-greedy. if you exclude it, it matches until it reaches the *last* \n

  , substring(ne.text, 'Indication: (.*?)\n') as Indication

  -- sometimes numeric values contain de-id text, e.g. [** Numeric Identifier **]
  -- this removes that text
  , case
      when substring(ne.text, 'Height: \(in\) (.*?)\n') like '%*%'
        then null

[ballardw]

And the data looks like what before the statements shown? And What is the desired result?

Random bits of syntax descriptions of another language do not always help much with what the appropriate SAS code might be.

It also is not clear which bits may be syntax and which might be data file content descriptions.

[LinusH]

Some initial guidance: for cast we use put() for to_char and input() for to_num. This together with SAS formats. Date and rime constants is of nuneric data type but handled via corresponding formats.
Substr() is the counterpart of substring.
For the next step, be acquainted with support.sas.com.