Transforming two date and time variables in one DateTime variable

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Transforming two date and time variables in one DateTime variable

I'm on SAS Enterprise Guide 7.1, Windows 10.

 

How does one go about transforming the variables date and time seen below in one DateTime variable?

Q.PNG

I want this variable to have a date format.

 

I have attached this dataset as a csv (semi colon separated values, actually) to the question.


Accepted Solutions
Solution
‎10-04-2017 11:49 AM
Super User
Posts: 23,275

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic
dhms(date, 0, 0, input(time, time.))

SAS stores date as a number, where its the number of days from Jan 1, 1960

SAS stores datetime as numbers, where it is the number of seconds from Jan 1, 1960

 

So yes, you get a number. And then you apply a datetime variable to view it as a datetime but it's still a number. 

 

I'm not sure if you're using SQL or a data step, but apply a format either way is easy. 

 

data have;
	time="12:32";
	date='01Jan2017'd;
	my_date = dhms(date, 0, 0, input(time, time.));
	format my_date datetime21.;
run;

proc print;
run;

View solution in original post


All Replies
Super User
Posts: 23,275

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic

DHMS() function in a query. 

 

Use the date as the date component and the time as the second component and the others are zero.

 

dhms(date, 0, 0, time)
Contributor
Posts: 38

Re: Transforming two date and time variables in one DateTime variable

I get an error, it says the fourth parameter (time) must be numeric.
Super User
Posts: 23,275

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic

Then convert time to a number using the INPUT() function. 

Contributor
Posts: 38

Re: Transforming two date and time variables in one DateTime variable

Then shouldn't it be what is below instead?

 

dhms(date, input(substr(time, 1, 2), 2.), input(substr(time, 4, 2), 2.), 0)

 

The problem is this yields a number and I want some kind of date/datetime format.

Solution
‎10-04-2017 11:49 AM
Super User
Posts: 23,275

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic
dhms(date, 0, 0, input(time, time.))

SAS stores date as a number, where its the number of days from Jan 1, 1960

SAS stores datetime as numbers, where it is the number of seconds from Jan 1, 1960

 

So yes, you get a number. And then you apply a datetime variable to view it as a datetime but it's still a number. 

 

I'm not sure if you're using SQL or a data step, but apply a format either way is easy. 

 

data have;
	time="12:32";
	date='01Jan2017'd;
	my_date = dhms(date, 0, 0, input(time, time.));
	format my_date datetime21.;
run;

proc print;
run;
Contributor
Posts: 38

Re: Transforming two date and time variables in one DateTime variable

[ Edited ]

Just a quick follow up question: what format should I use if time="20171003155533" and I want to get this in seconds?

Super User
Posts: 23,275

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic

Is that supposed to be:

October 3, 2017, 3:55.33?

And is it a character variable or numeric?

 

You would need to convert it to the correct number first in SAS and then apply the format.

Contributor
Posts: 38

Re: Transforming two date and time variables in one DateTime variable

[ Edited ]

Yes, that's the time I'm going for, only it should be PM. It's a character variable. But what does the "correct number" mean here? Why is "20171003155533" incorrect?

Super User
Posts: 23,275

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic

Because of what I said earlier. 

 

SAS stores datetime as numbers, where it is the number of seconds from Jan 1, 1960

 

If your variable is a number that reflects a date but not something SAS can work from as a date. For example you can subtract SAS dates to determine the number of days between two dates. Numbers stored in the format you've shown will not allow for that type of calculation. 

 

The PM part comes from the format you apply to the data.

 

data have;
mydate = "20171003155533";
mydate_num = input(mydate, best32.);
mydate_sas = input(mydate, B8601DJ.);
format mydate_num mydate_sas datetime.;
run;

proc print;run;

 

 

 

 

 

Super User
Super User
Posts: 7,932

Re: Transforming two date and time variables in one DateTime variable

Posted in reply to Autotelic

You can use the TIME informat to read the string directly.

dhms(date,0,0,input(time,time.));

If you want the values to print in a way that meaningful then attach a format.  Like the datetime format.

format datetime datetime19.;

Note there is a bug in the DATETIME format. Even though a 9 character date and an 8 character time plus a colon separator should fit in 18 characters SAS will use only two digits for the year if you use only 18 characters for the width in the format specification.

 

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 215 views
  • 2 likes
  • 3 in conversation