Creating new variables with time variables

Reply
Occasional Contributor
Posts: 9

Creating new variables with time variables

Hello,

 

I have been trying so hard to do what I know I've done but cannot.  

I need to create new variables from my current time variables: EndTime-StartTime=New Time.

They are numeric and in the 00:00 format.  I've been incredibly stressed which I think is hindering my progress.

I've created the NewTime in my data step but I feel that I am missing formatting.  

Please any advice is needed as of course, it needs to be done today.

Thank you!

Regular Contributor
Posts: 212

Re: Creating new variables with time variables

Hi mate,

 

I didn't get what you mean exactly but if i'm correct you are trying to use some

datetimes stored in a table as data and transform into columns.

 

To do this you can use Proc Transpose or Transpose naming variables.

 

inp1.png

 

inp2.png

 

 

 

Or if you are trying to use, you need to set this date into character to uses as

a column the syntax is like this:  "19mar2015"n;

 

Hope this helps

 

Att

Occasional Contributor
Posts: 9

Re: Creating new variables with time variables

I actual have hour:time as a variable. I am looking at healthcare's response.

So we have a starttime variable and endtime variable. I need to make a new variable out of these to show the delay inbetween.

So 00:04 starttime and 04:02 endtime. But doing a straight subtraction gives me nothing in SAS so I know I've got formatting issues.

Thank you for responding!
Super User
Super User
Posts: 7,682

Re: Creating new variables with time variables

So, you have something like:

data temp;
  endtime="14:10"t; 
  starttime="08:13"t;
  new_time=endtime-starttime;
  format endtime starttime new_time time5.;
run;

If its just formatting you are after, use the format command.  If there is more, then post test data in form of datatstep, and required output to describe the problem.

Occasional Contributor
Posts: 9

Re: Creating new variables with time variables

Hello,

I have not posted before so I'm sorry if I am not posting it correctly.

I have thousands so I will try to make this work.

my raw data
endtime: starttime:
15:26 14:15
13:16 12:10
11:42 U
19:59 U
09:15 08:30
13:05 U
13:06 U
12:14 U
21:19 19:30
00:10 21:30
05:17 04:30
11:49 10:45
20:30 19:45
17:35 16:00
04:44 03:30
17:14 16:00
23:21 22:00
17:39 13:00
18:20 17:40
08:51 23:30
23:41 U

I need my output to look like
newtime:
01:11
01:15
*

etc


Does this make more sense?
Super User
Super User
Posts: 7,682

Re: Creating new variables with time variables

Yes, it does.  So your endtime and starttime are character variables according to that data.  To do computations on them they need to be numeric, hence we use input() function, however as you have values which are not times = "U" then we need to put that in a conditional so that only when not U then do we do the calculation, otherwise you will get warnings about invalid data.

data have;
  endtime="15:26"; starttime="14:15"; output;
  endtime="13:16"; starttime="12:10"; output;
  endtime="11:42"; starttime="U"; output;
run;

data want;
  set have;
  if endtime ne "U" and starttime ne "U" then newtime=input(endtime,time5.) - input(starttime,time5.);
  format newtime tod5.;
run;

In this example, I only calculate newtime if both are valid times (to avoid warnings), i convert each to numeric version, subtract them.  ONce that is done, I then apply a time format to the newtime variable.  Remember dates are recorded as number of days since a certain date, and times are number of seconds.  So to use these numerically they need to be converted from the character ones.  It is however far better to store dates and times in your datasets as specific date or time variables, consider the have dataset:

data have;
  endtime="15:26"; starttime="14:15"; output;
  endtime="13:16"; starttime="12:10"; output;
  endtime="11:42"; starttime="U"; output;
run;

data want (keep=etime stime);
  set have;
  if endtime ne "U" then etime=input(endtime,time5.);
  if starttime ne "U" then stime=input(starttime,time5.);
  format etime stime tod5.;
run;

In the want dataset, the variables for the two items are numeric values which can be "seen" as time values, and used in the way that numeric variables can be.

Occasional Contributor
Posts: 9

Re: Creating new variables with time variables

What is the format tod5.? Is that already stored in SAS?

Is there a way to restructure the variables without doing it by hand? I have thousands of observations.

This is a legacy dataset so I didn't get any input in how they were stored.
That makes sense, cutting out the unknowns. Thank you for that!
Super User
Super User
Posts: 7,682

Re: Creating new variables with time variables

Tod5. is an inbuilt SAS format:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a001263753.htm

 

It does not matter how many observations there are, a datastep is like a loop, doing the code within for each observation:

data want;
  set have;
  if endtime ne "U" and starttime ne "U" then newtime=input(endtime,time5.) - input(starttime,time5.);
  format newtime tod5.;
run;

So in the above the if endtime ne "U" ... is applied to every observation. 

Occasional Contributor
Posts: 9

Re: Creating new variables with time variables

It made more sense as output! Thank you all!
Frequent Contributor
Posts: 91

Re: Creating new variables with time variables

I hope this helps.     Jim

 

data one; drop u;
format endtime startime duration time6.;
informat endtime startime time6.;
input endtime : u$6.;
if u='U' then startime=.;
else startime=input(u,time6.);
if startime ne . then do;
duration=endtime-startime;
if duration lt 1 then duration=duration+(3600*24);
end;
cards;
15:26 14:15
13:16 12:10
11:42 U
19:59 U
09:15 08:30
13:05 U
13:06 U
12:14 U
21:19 19:30
00:10 21:30
05:17 04:30
11:49 10:45
20:30 19:45
17:35 16:00
04:44 03:30
17:14 16:00
23:21 22:00
17:39 13:00
18:20 17:40
08:51 23:30
23:41 U
; proc print; run;

Occasional Contributor
Posts: 9

Re: Creating new variables with time variables

I have a related issue at this point.

Formatting!

I now have the values/correct output from the difference in hours:minutes.
What I would like to do is take those values and create frequency tables, meaning:

Time Difference Count
0-1hr
1-2hr
2-3hr
etc.


What's worse, is as soon as I see the solution I know I will remember doing it in the past, since I have no copy of former code I did this in.

Thank you so much for the feedback!
Super User
Super User
Posts: 7,682

Re: Creating new variables with time variables

You could do it with proc format, e.g.

proc format;

  value time_diff

    "00:00"t - "01:00"t = "0-1hr"

...

 

NOt sure if that is the correct syntax, I personally don't use formats.  I would do it in a select clause myself:

data want;
  set have;
  length diff $20;
  select;
    when("00:00"t < your_time <= "01:00"t) diff="0-1hrs";
    when("01:00"t < your_time <= "02:00"t) diff="1-2hrs";
...
    otherwise diff="Unknown";
  end;
run;
Super User
Posts: 19,023

Re: Creating new variables with time variables

Hard to see whats your issue. You may want to round the values and then use proc freq to summarize.
Ask a Question
Discussion stats
  • 12 replies
  • 461 views
  • 0 likes
  • 5 in conversation