DATA Step, Macro, Functions and more

Converting a datetime into a date

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Converting a datetime into a date

[ Edited ]

I have a CSV file with a column with a date format of MM/DD/YYYY HH:MM that I would like to convert to MM/DD/YYYY.  This is pretty painless in Python but haven't been able to figure it out in SAS.  My data step looks something like this:

 

data foo;
	infile <path> delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
	informat ID best32. ;
	informat DATE anydtdtm40. ;
	format ID best12. ;
	format DATE datetime. ;
	input
		ID
		DATE;
run;

I figure I can do something like this:

 

DATE = INPUT(PUT(DATE, 8.), MMDDYY10.);

But the number are too big, ~1.8E9, for it to be a valid value for the input function.  I'm thinking of using regular expressions to cut off the time part?  That's all I can think of.


Accepted Solutions
Solution
‎12-13-2017 02:36 PM
Super User
Super User
Posts: 9,211

Re: Converting a datetime into a date

Posted in reply to tomcmacdonald
data foo;
	infile <path> delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
	informat ID best32. ;
	informat DATE anydtdtm40. ;
	format ID best12. ;
	format DATE datetime. ;
  want=datepart(date);
  format want mmddyy10.;
	input
		ID
		DATE;
run;

View solution in original post


All Replies
Solution
‎12-13-2017 02:36 PM
Super User
Super User
Posts: 9,211

Re: Converting a datetime into a date

Posted in reply to tomcmacdonald
data foo;
	infile <path> delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
	informat ID best32. ;
	informat DATE anydtdtm40. ;
	format ID best12. ;
	format DATE datetime. ;
  want=datepart(date);
  format want mmddyy10.;
	input
		ID
		DATE;
run;
Super User
Super User
Posts: 7,849

Re: Converting a datetime into a date

Posted in reply to tomcmacdonald

Regular expression are really only useful for parsing text, not numbers. Dates as stored as the number of days since '01JAN1960'd. Datetimes are stored as the number of seconds since '01JAN1960:00:00'dt.

 

The DATEPART() and TIMEPART() functions can be used to tease out the parts of a datetime value. 

date=datepart(dt);
time=timepart(dt);

You can use the DHMS() function to create a datetime value from date and time values. Note you can just set the Day and Hour arguments to zero and put the time value into the Seconds.   

dt=dhms(date,0,0,time) 

It would make no sense to convert the number of seconds stored in your datetime variable into a character string that would represent that number of seconds and then try to read it is if any of the digits represented year, month or day numbers.  But you could convert it to a string that looks like a date and then use the appropriate informat to convert that string back to a date. For example you could use the DTDATE9. format and the DATE9. informat.

date=input(put(dt,dtdate9.),date9.);

Also if you don't need the time value they why store it to begin with?  You could change you original code from using the ANYDTDTM informat to use the ANYDTDTE informat instead.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 128 views
  • 0 likes
  • 3 in conversation