BookmarkSubscribeRSS Feed
Obsidian | Level 7

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

Tourmaline | Level 20

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.

Data never sleeps
Obsidian | Level 7



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:MISmiley FrustratedS')  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.

Super User

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.

Super User Tom
Super User

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.)),"'"));

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

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


Super User Tom
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 4 in conversation