How to concatenate a datetime value with time and milliseconds

[Andalusia]

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.

[ballardw]

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);

[Andalusia]

@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.

[ballardw]

---

@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.

[PaigeMiller]

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;

[Andalusia]

@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?

[PaigeMiller]

---

@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.

[Andalusia]

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

[PaigeMiller]

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.

[Andalusia]

@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.....

[PaigeMiller]

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
;

[Andalusia]

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

[Andalusia]

@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.

[PaigeMiller]

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).

[Andalusia]

@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;