topic Re: PostgreSQL code translate into FEDSQL or SAS in SAS Data Management

PostgreSQL code translate into FEDSQL or SAS

Zula — Wed, 15 Nov 2017 22:27:14 GMT

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.

Re: PostgreSQL code translate into FEDSQL or SAS

Reeza — Wed, 15 Nov 2017 22:32:50 GMT

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.

Re: PostgreSQL code translate into FEDSQL or SAS

Zula — Wed, 15 Nov 2017 22:38:15 GMT

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

Re: PostgreSQL code translate into FEDSQL or SAS

ballardw — Thu, 16 Nov 2017 15:56:12 GMT

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.

Re: PostgreSQL code translate into FEDSQL or SAS

LinusH — Thu, 16 Nov 2017 18:22:52 GMT

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.