SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

PostgreSQL code translate into FEDSQL or SAS

Reply
New Contributor
Posts: 4

PostgreSQL code translate into FEDSQL or SAS

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. 

Super User
Posts: 20,236

Re: PostgreSQL code translate into FEDSQL or SAS

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. 


 

New Contributor
Posts: 4

Re: PostgreSQL code translate into FEDSQL or SAS

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
Super User
Posts: 11,578

Re: PostgreSQL code translate into FEDSQL or SAS

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.

 

Super User
Posts: 5,493

Re: PostgreSQL code translate into FEDSQL or SAS

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.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 151 views
  • 0 likes
  • 4 in conversation