Add date into file with time

[TobbeNord]

Hello all.

It's really a simple question, but I can't find the solution. 

I have a file with a time variable and another dataset with the starting date. 

The time variable is over several days and I want to create a time date variable (that increase the date by 1 at midnight of course) 

My solution so far doesn't work:

Time is time variable in text format.

Datum is date in text format.

Code below (inside a macro):

data &dataset_name.date;
set &dataset_name.id;
timme=substr(time,1,2)*1;
if _n_ eq 1 then do;
set &dataset_name._date(rename=(data=datum));
end;
datumnum=mdy(substr(datum,6,2),substr(datum,9,2),substr(datum,1,4));
run;

data &dataset_name.dateid;
retain datumnum;
set &dataset_name.date;
by datumnum;
if dif(timme)<0 then datumnum+1;
Timedate=DHMS( datumnum, substr(time,1,2), substr(time,4,2), substr(time,7,2) );
format Timedate datetime.;
run;

[Kurt_Bremser]

Please give us examples for your datasets that illustrate what you have, and what you want to get out of it.

[TobbeNord]

The dataset that contain time have the format:

time

23:30:01

23:30:04

23:30:06

23:30:06

23:30:08

23:30:10 

and so on..,

23:59:59

00:00:01

00:00:03

And the datafile that contains the date have the format:

data

2019-09-02 

(only one row with starting date)

I want a new variable with the format

datetime

2019-09-02 23:30:01

2019-09-02 23:30:04

2019-09-02 23:30:06

2019-09-02 23:30:06

and so on...

2019-09-02 23:59:59

2019-09-03 00:00:01

2019-09-03 00:00:03

...

[Kurt_Bremser]

See this:

data times;
input time :time8.;
format time time8.;
datalines;
23:30:01
23:30:04
23:30:06
23:30:06
23:30:08
23:30:10 
23:59:59
00:00:01
00:00:03
;

data dates;
input startdate :yymmdd10.;
format startdate yymmddd10.;
datalines;
2019-09-02
;

data want;
set times;
if _n_ = 1 then set dates;
if time < lag(time) then startdate + 1;
dt = dhms(startdate,0,0,time);
format dt e8601dt19.;
run;

It also illustrates the importance of storing date and time values as SAS date and time values, which makes it easy to use simple arithmetic and the functions and formats SAS provides for dates and times.

[TobbeNord]

Thank you, but solution do exactly the same as I already have done, and then only the first row after midnight change date, then it's starting date again. 

[Kurt_Bremser]

You obviously have not run my code as posted, see this:

data times;
input time :time8.;
format time time8.;
datalines;
23:30:01
23:30:04
23:30:06
23:30:06
23:30:08
23:30:10 
23:59:59
00:00:01
00:00:03
;

data dates;
input startdate :yymmdd10.;
format startdate yymmddd10.;
datalines;
2019-09-02
;

data want;
set times;
if _n_ = 1 then set dates;
if time < lag(time) then startdate + 1;
dt = dhms(startdate,0,0,time);
format dt e8601dt19.;
run;

proc print data=want noobs;
run;

Result:

    time     startdate    dt

23:30:01    2019-09-02    2019-09-02T23:30:01
23:30:04    2019-09-02    2019-09-02T23:30:04
23:30:06    2019-09-02    2019-09-02T23:30:06
23:30:06    2019-09-02    2019-09-02T23:30:06
23:30:08    2019-09-02    2019-09-02T23:30:08
23:30:10    2019-09-02    2019-09-02T23:30:10
23:59:59    2019-09-02    2019-09-02T23:59:59
 0:00:01    2019-09-03    2019-09-03T00:00:01
 0:00:03    2019-09-03    2019-09-03T00:00:03

As you can see, ALL observations after midnight have the new date.

[TobbeNord]

It's strange, because on my dataset it's not working that way. 

[Kurt_Bremser]

It is essential that dates and times are stored as such (counts of days from 1960-01-01 / seconds from midnight).

If my code does not work for you, post your dataset as a data step with datalines (so that the created dataset looks like yours in terms of structure/variable attributes), and the log of the code.