topic Re: In a proc sql statement how to convert character date and time to date and time in SAS Enterprise Guide

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

alepage — Tue, 29 Oct 2024 19:45:27 GMT

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

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

Tom — Tue, 29 Oct 2024 19:51:19 GMT

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.

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

PaigeMiller — Tue, 29 Oct 2024 20:51:51 GMT

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

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

alepage — Tue, 29 Oct 2024 21:05:01 GMT

15:22

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

PaigeMiller — Tue, 29 Oct 2024 22:18:23 GMT

@alepage wrote:

15:22

15 minutes and 22 seconds?

15 hours and 22 minutes?

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

Patrick — Tue, 29 Oct 2024 22:34:50 GMT

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;