topic Re: Converting a datetime into a date in SAS Programming

Converting a datetime into a date

tomcmacdonald — Wed, 13 Dec 2017 19:09:41 GMT

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.

Re: Converting a datetime into a date

RW9 — Wed, 13 Dec 2017 19:06:26 GMT

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;

Re: Converting a datetime into a date

Tom — Thu, 14 Dec 2017 03:39:27 GMT

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.