topic Re: Add date into file with time in SAS Programming

Add date into file with time

TobbeNord — Thu, 12 Mar 2020 12:48:17 GMT

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;

Re: Add date into file with time

Kurt_Bremser — Thu, 12 Mar 2020 12:59:30 GMT

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

Re: Add date into file with time

TobbeNord — Thu, 12 Mar 2020 13:10:47 GMT

The dataset that contain time have the format:

time

23:30:01

23:30:04

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

and so on...

2019-09-02 23:59:59

2019-09-03 00:00:01

2019-09-03 00:00:03

...

Re: Add date into file with time

Kurt_Bremser — Thu, 12 Mar 2020 13:25:04 GMT

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.

Re: Add date into file with time

TobbeNord — Thu, 12 Mar 2020 14:20:58 GMT

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.

Re: Add date into file with time

Kurt_Bremser — Thu, 12 Mar 2020 14:29:11 GMT

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.

Re: Add date into file with time

TobbeNord — Thu, 12 Mar 2020 14:56:40 GMT

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

Re: Add date into file with time

Kurt_Bremser — Thu, 12 Mar 2020 15:15:38 GMT

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.