BookmarkSubscribeRSS Feed
Zula
Obsidian | Level 7

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. 

4 REPLIES 4
Reeza
Super User

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
Obsidian | Level 7

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
Super User

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
Tourmaline | Level 20
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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1288 views
  • 0 likes
  • 4 in conversation