In a proc sql statement how to convert character date and time to date and time

[alepage]

Hello, 

Here's my proc SQL

proc sql;
create table sasfile as
select 	distinct owner,
        group,
		permissions,
		cfname,
		fname,
		cr_dt,
		time, 
		sum(filesize) format =  SIZEKMG6.2 as totalfsize
		
from sasfiles2
group by fname;
quit;

How to convert the cr_dt from character to date format like yyyy-mm-dd and time like 15h22

fname cr_dt time totalfsize
be_auto_prmnov2020 2020-11-26 00:48 1.39GB
be_auto_prmnov2020 2020-12-02 15:22 1.39GB

[Tom]

The same way you would when not using SQL,  with the INPUT() function.

If there is an INFORMAT that can read the strings you have then you could convert them into a DATETIME value using the INPUT() function.

If not then use SCAN() or some other tool to split the string into the date and time parts and use informats that can convert those strings into date or time values.

Please share some example values of the strings you have.  Remember to use the INSERT CODE or INSERT SAS CODE buttons to get a pop-up window where you an paste and/or edit the text you want to share.  That will prevent the webpage from reflowing the text into paragraphs.

[PaigeMiller]

I don't recognize a date and time that looks like 15h22, what date and time is that?

[alepage]

15:22

[PaigeMiller]

---

@alepage wrote:

15:22

---

15 minutes and 22 seconds?

15 hours and 22 minutes?

[Patrick · Accepted solution]

Is below what you're after?

data have;
  var='be_auto_prmnov2020 2020-11-26 00:48 1.39GB'; output;
  var='be_auto_prmnov2020 2020-12-02 15:22 1.39GB'; output;
run;

proc sql;
  select 
    var
    ,scan(var,2,' ') as dt_string
    ,scan(var,3,' ') as tm_string
    ,input(catx('T',scan(var,2,' '),scan(var,3,' ')),e8601dt.) as dttm format=datetime.
  from have
  ;
quit;