BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Autotelic
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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

10 REPLIES 10
Reeza
Super User

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)
Autotelic
Obsidian | Level 7
I get an error, it says the fourth parameter (time) must be numeric.
Reeza
Super User

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

Autotelic
Obsidian | Level 7

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.

Reeza
Super User
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;
Autotelic
Obsidian | Level 7

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

Reeza
Super User

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.

Autotelic
Obsidian | Level 7

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?

Reeza
Super User

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;

 

 

 

 

 

Tom
Super User Tom
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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