topic Re: How to concatenate a datetime value with time and milliseconds in SAS Enterprise Guide

How to concatenate a datetime value with time and milliseconds

Andalusia — Tue, 24 Nov 2020 15:08:39 GMT

Hello SAS Experts,

I have 2 sets:

SET-1

ID	Date	Time
1001	01NOV2020	10:01:01
1002	01NOV2020	16:30:10
1003	01NOV2020	21:45:45

SET-2

ID	Time	Price
1001	0.000	125.22
1001	0.500	122.23
1001	1.000	114.26
1001	1.500	130.60
1002	0.000	899.33
1002	0.500	881.23
1002	1.000	1000.01
1002	1.500	1035.23
1003	0.000	667.22
1003	0.500	668.33
1003	1.000	669.30
1003	1.500	678.53
1003	2.000	710.22

The target of this all is to join these two sets together and to concatenate date + time from SET1 and time from SET2. So It will be [Date+time+time]. The time of SET1 is HMS and this is actually the start time of a trend. The time is of SET-2 is in seconds/milliseconds and this is actually the time in seconds/milliseconds during the trend. Every new trend starts at 0.00 which is actually equal to the start time of a trend. Each trend takes about 1,5 or 2 seconds.

This is what I expect it to be:
SET-3

ID	DateTime	Price
1001	11/01/2020 10:01:01:000	125.22
1001	11/01/2020 10:01:01:500	122.23
1001	11/01/2020 10:01:02:000	114.26
1001	11/01/2020 10:01:02:500	130.60
1002	11/01/2020 16:30:10:000	899.33
1002	11/01/2020 16:30:10:500	881.23
1002	11/01/2020 16:30:11:000	1000.01
1002	11/01/2020 16:30:11:500	1035.23
1003	11/01/2020 21:45:45:000	667.22
1003	11/01/2020 21:45:45:500	668.33
1003	11/01/2020 21:45:46:000	669.30
1003	11/01/2020 21:45:46:500	678.53
1003	11/01/2020 21:45:47:000	710.22

Please note that ''deleting'' the millisecond is not an option, because the price fluctuates twice every second. I really need it to be DATE-HOUR-MINUT-SECOND-MILLISECOND. Also please note that the real datasets are of course much bigger.

Re: How to concatenate a datetime value with time and milliseconds

ballardw — Tue, 24 Nov 2020 15:03:26 GMT

Divide the time in Set2 so that the units are seconds instead of milliseconds before combining with the values in Set1, which would be done by either adding the fractional second or use of the intnx function

You make a date time valued variable from a date and time with

dt = dhms(datevar,0,0,timevar);

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Tue, 24 Nov 2020 15:05:42 GMT

@ballardw that's not an option. In SET3 I really need it to be DATE-HOUR-MINUTE-SECOND-MILLISECOND. I don't want to loose my milliseconds.

Re: How to concatenate a datetime value with time and milliseconds

ballardw — Tue, 24 Nov 2020 15:19:32 GMT

@Andalusia wrote:
@ballardw that's not an option. In SET3 I really need it to be DATE-HOUR-MINUTE-SECOND-MILLISECOND. I don't want to loose my milliseconds.

A millisecond is one-onethousandth of a second, a decimal portion of a second. That is how SAS uses times values. That does NOT lose any milliseconds. You may need to assign a different format to see the decimal portion which properly looks like

11/01/2020 10:01:01.500 (for 500 milliseconds or half a second). If this is supposed to be 5 milliseconds then

11/01/2020 10:01:01.005

Otherwise you will be in the world of Character values and impossible to do almost anything.

Re: How to concatenate a datetime value with time and milliseconds

PaigeMiller — Tue, 24 Nov 2020 15:34:33 GMT

data one;
input ID date date9. time time8.;
datetime=dhms(date,hour(time),minute(time),second(time));
format datetime datetime20.2;
cards;
1001 01NOV2020 10:01:01
;
data two;
input id seconds price;
cards;
1001	0.000	125.22
1001	0.500	122.23
1001	1.000	114.26
1001	1.500	130.60
;
data three;
	merge one two;
	by id;
	datetime_new=datetime+seconds;
	format datetime_new datetime20.3;
run;

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Wed, 25 Nov 2020 08:38:29 GMT

@PaigeMiller

This is how 1 looks like:

This is how 2 looks like:

This is how 3 looks like:

@PaigeMiller You sure its doing what it should be doing?

Re: How to concatenate a datetime value with time and milliseconds

PaigeMiller — Wed, 25 Nov 2020 11:45:45 GMT

@Andalusia wrote:

@PaigeMiller

This is how 1 looks like:

This is how 2 looks like:

This is how 3 looks like:

@PaigeMiller You sure its doing what it should be doing?

It must be your data source is different than mine. And possibly you have character values while my data is numeric. The program works properly with my typed in data. If you provide the data by following these instructions, then we can be sure we're both using the same data and then we can also be sure we are both getting the same answer.

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Wed, 25 Nov 2020 11:47:09 GMT

@Well I literally just copied paste your code and ran it. I did not use it on ''my data'. Could that be it?

Re: How to concatenate a datetime value with time and milliseconds

PaigeMiller — Wed, 25 Nov 2020 11:59:44 GMT

I can literally copy and paste my code, and it runs as expected. I do not get missings for data set two as you did.

Show me the LOG from what you did, paste the log as text into the window that appears when you click on the </> icon.

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Wed, 25 Nov 2020 12:04:16 GMT

@PaigeMiller I think it has to do with your data two:

I literaly did this:

and received:

Not sure what's going on because our input is literally the same.....

Re: How to concatenate a datetime value with time and milliseconds

PaigeMiller — Wed, 25 Nov 2020 12:08:42 GMT

I see the problem. Enterprise Guide runs the code differently than base SAS. In Enterprise Guide, run this:

data two;
infile cards expandtabs;
input id seconds price;
cards;
1001	0.000	125.22
1001	0.500	122.23
1001	1.000	114.26
1001	1.500	130.60
;

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Wed, 25 Nov 2020 12:12:38 GMT

That looks better, Imma gonna run it on my real data sets now and get back to you in a few mins.

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Wed, 25 Nov 2020 12:31:43 GMT

@PaigeMiller So I did this:

data one;
SET pki.set1;
input ID Date date9. Time time8.;
datetime=dhms(date,hour(Time),minute(Time),second(Time));
format datetime datetime20.2;
run;
data three;
	merge one pki.set2;
	by ID;
	datetime_new=datetime+seconds;
	format datetime_new datetime20.3;
run;

And I received this:

ERROR: No DATALINES or INFILE statement.
ERROR: BY variable ID is not on input data set WORK.ONE.

I have no idea what those errors mean. Please also note that I did not use your part of two because I already have that dataset(pki.set2). By the way, those 2 datasets contain more columns than I originally showed.

Re: How to concatenate a datetime value with time and milliseconds

PaigeMiller — Wed, 25 Nov 2020 12:53:42 GMT

In your data step for DATA ONE, you would not use an INPUT statement becuase the SET statement brings in the data.

May I make a request? From now on, please show us the entire LOG for the steps in question with nothing chopped out or deleted or edited (not the code and separately portions of the LOG).

Re: How to concatenate a datetime value with time and milliseconds

Andalusia — Wed, 25 Nov 2020 15:36:31 GMT

@PaigeMiller Oke I'm still a rookie and new into this SAS world so I will try to keep up with your request.

So If I understand well I should run my code like this. Is that correct?

data one; SET pki.set1; datetime=dhms(date,hour(Time),minute(Time),second(Time)); format datetime datetime20.2; run; data three; merge one pki.set2; by ID; datetime_new=datetime+seconds; format datetime_new datetime20.3; run;

Re: How to concatenate a datetime value with time and milliseconds

PaigeMiller — Wed, 25 Nov 2020 15:41:36 GMT

That looks good, but since I don't know what is in your data sets (what variable names, and what variable types numeric or character, and what the variable values) it still might have problems. Which is why I suggested you provide a portion of data sets one and two via these instructions, and then we can all stop guessing.

Re: How to concatenate a datetime value with time and milliseconds

Kurt_Bremser — Wed, 25 Nov 2020 16:17:57 GMT

In the enhanced editor settings, make sure that tabs are replaced with blanks.

Re: How to concatenate a datetime value with time and milliseconds

Tom — Wed, 25 Nov 2020 16:45:06 GMT

Never put tab characters in program files.

Re: How to concatenate a datetime value with time and milliseconds

Tom — Wed, 25 Nov 2020 16:46:40 GMT

A time value is already a number of seconds, so no need for all of those extra function calls.

datetime=dhms(date,0,0,time);