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

Transforming two date and time variables in one DateTime variable

Autotelic — Tue, 03 Oct 2017 16:37:40 GMT

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?

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.

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

Reeza — Tue, 03 Oct 2017 17:04:03 GMT

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)

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

Autotelic — Tue, 03 Oct 2017 17:19:31 GMT

I get an error, it says the fourth parameter (time) must be numeric.

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

Reeza — Tue, 03 Oct 2017 17:20:13 GMT

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

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

Autotelic — Tue, 03 Oct 2017 17:29:16 GMT

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.

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

Reeza — Tue, 03 Oct 2017 17:35:35 GMT

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;

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

Tom — Tue, 03 Oct 2017 17:39:35 GMT

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.

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

Autotelic — Tue, 03 Oct 2017 18:01:22 GMT

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

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

Reeza — Tue, 03 Oct 2017 18:01:24 GMT

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.

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

Autotelic — Tue, 03 Oct 2017 18:04:14 GMT

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?

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

Reeza — Wed, 04 Oct 2017 16:38:37 GMT

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;