Concatenate timestamp

[ssaha]

In one of my date variable the value stores as '09MAY2019:14:51:51.000000'

So I need to concatenate additional time format characters in this timestamp because I need to change it to another timestamp format in my sql pass through query.

So how can I resolve the the above mentioned timestamp into another variable to

to_timestamp('09MAY2019:14:51:51', 'DDMONYYYY:HH24:MI:SS') 

The above mentioned statement should be stored in another variable.

For example...09MAY2019:14:51:51 is one timestamp value. There are different timestamp values

[LinusH]

If you table is in a external database, and you wish to perform an explict pass-through step for this, I can't see that SAS has anything to do with it.

You should redirect your query to a forum specific for your DBMS.

[ssaha]

Hi,

Thanks for your reply

But I can do it from sas side. I just need to resolve this value '09MAY2019:14:51:51.000000' 

to     to_timestamp('09MAY2019:14:51:51', 'DDMONYYYY:HH24:MIS')  and store it in another variable. With the help of concatenation I will able to do it but not getting a proper way on how to implement it.

[ballardw]

PLEASE show an exact representation of the single string that you need. I can not tell whether you need concatenation or format or what.

Also be aware that apparently some of you code has been transformed into an emogi, so that doesn't help.

If your current value is in a SAS datetime value then likely you need something like

newvar = put(timestamp, datetime18.)

but without seeing the exact string that you need...

Try pasting/ typing into a code box opened using the forums {I} or "running man" icons to reduce the chance of an emogi.

[Tom]

It is probably easier to upload the data as a dataset (or what SQL terminology would call a table) rather than trying to put literal values into code.

But if you have one value then perhaps you can place it into a macro variable?  Something like this:

data _null_;
  set mydata ;
  where id=12345;
  call symputx('mytimestamp',quote(strip(put(mydatetimeVar,datetime20.)),"'"));
run;

And then you can use that in your passthru SQL code.

to_timestamp(&mytimestamp, 'DDMONYYYY:HH24:MI:SS')

[Tom]

Can you show actual examples of the data you have in SAS.  In the form of a data step to create a dataset (or if you really don't know SAS then perhaps in the form of SQL create and insert statements that work in SAS).  That way we know how your variable is really defined and have concrete examples of values.

Then show what code you need to create from this data.

Remember to use the Insert Code or Insert SAS Code buttons in the editor tool bar to get a pop-up window to paste/type/edit your code so that the forum editor doesn't try to turn it into paragraphs and emojis.