As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


User talk:Erinroze

From sasCommunity
Jump to: navigation, search

Welcome! and response to your questions

Hi Erinroze, and welcome to our community! I wanted to note that I responded to the questions you posted here.

If you have questions or would like any help editing, please feel to comment here or on my talk page. When you leave notes in discussion pages, I recommend signing your comments by using ~~~~.

I hope you like the place and decide to stay! = paulkaefer (talk) 14:14, 13 April 2017 (CDT)

Question on Talk:Main Page removed

Hi Erin,

In some April 2017 edits to Talk:Main Page you asked a questions about How to add 24 hours to an existing time field. These questions and their answers are being moved here instead of being lost in the discussion about the Main Page. The questions and the responses to them are reproduced below. I have also added my solution too. I hope this assists you. - Cameron (talk) 06:29, 29 April 2017 (CDT)

How to add 24 hours to an existing time field

I am currently trying to find a way to add 24 hours to an existing time field. I've copied the case statement I'm attempting now and i'm getting an error that states ERROR: Character format $ in PUT function requires a character argument.

1st try:

         case when t1.Day_Diff = 0 then t1.Time_Diff else 
         (case when t1.Day_Diff = 1 then t1.Time_Diff + 24:00:00 else 
         (case when t1.Day_Diff = 2 then t1.Time_Diff + 48:00:00 else 
         (case when t1.Day_Diff = 3 then t1.Time_Diff + 72:00:00 else '0' end) end) end) end as Add_24hours


2nd try:

         case when t1.Day_Diff = 0 then strip(put(t1.Time_Diff,$32.)) else 
         (case when t1.Day_Diff = 1 then strip(put(t1.Time_Diff + "24:00:00"T,$32.)) else 
         (case when t1.Day_Diff = 2 then strip(put(t1.Time_Diff + "48:00:00"T,$32.)) else 
         (case when t1.Day_Diff = 3 then strip(put(t1.Time_Diff + "72:00:00"T,$32.)) else "over" end) end) end) end as Add_24hours

Any help you can give would be greatly appreciated.

Thank you! Erin


Erin, As is stated above sas.Community.org is not designed to handle a question answer forum format. Please consider posing your question to one of the forums hosted by SAS, such as the SAS Programming forum. That said adding 24 hours is time=time+(24*60*60); --Art Carpenter (talk) 17:16, 14 April 2017 (CDT)

I have made no additional changes

I am currently trying to find a way to add 24 hours to an existing time field. I've copied the case statement I'm attempting now and i'm getting an error that states ERROR: Character format $ in PUT function requires a character argument.

1st try:

         case when t1.Day_Diff = 0 then t1.Time_Diff else 
         (case when t1.Day_Diff = 1 then t1.Time_Diff + 24:00:00 else 
         (case when t1.Day_Diff = 2 then t1.Time_Diff + 48:00:00 else 
         (case when t1.Day_Diff = 3 then t1.Time_Diff + 72:00:00 else '0' end) end) end) end as Add_24hours


2nd try:

         case when t1.Day_Diff = 0 then strip(put(t1.Time_Diff,$32.)) else 
         (case when t1.Day_Diff = 1 then strip(put(t1.Time_Diff + "24:00:00"T,$32.)) else 
         (case when t1.Day_Diff = 2 then strip(put(t1.Time_Diff + "48:00:00"T,$32.)) else 
         (case when t1.Day_Diff = 3 then strip(put(t1.Time_Diff + "72:00:00"T,$32.)) else "over" end) end) end) end as Add_24hours

Any help you can give would be greatly appreciated.

Thank you! Erin

Hi Erin, for adding and subtracting from time variables, use the INTNX function. I see an example on the INTNX Function examples page that looks like what you are trying to do.
I did want to note that while you are welcome to post questions here, you may get a quicker response on the SAS Communities Forums. sasCommunity.org isn't really a forum so much as a repository of articles and pages covering topics, user groups, conferences, etc. Here's a related thread from the SAS Communities Forums. = paulkaefer (talk) 14:11, 13 April 2017 (CDT)

Erin, perhaps the place to start understanding your problem is with the error message

ERROR: Character format $ in PUT function requires a character argument.

It seems to me that t1.Time_Diff is defined as a numeric variable, (you did say it was a TIME variable), but you are trying to add character values to it. This error message is indicating you have a type conversion problem and suggesting that rather than use a numeric (or TIME) variable t1.Time_Diff in PUT function with a character format, you might want to use a character variable instead. Since you are also trying to do arithmetic with t1.Time_Diff this should be outside the PUT function and you should instead use an INPUT function with a TIME or DATETIME format instead to convert the time strings to numbers first. That said, the value of t1.Day_Diff does tell you the exact number of 24 hour periods you wish to add, so there is no need to have PUT or INPUT functions to convert strings to numbers as the numbers are already available. Also there is no need for a CASE statement, or even the multiple CASE statements you have used because the amount to add can be calculated with simple arithmetic and a little bit of logic. The following formula should do what you want:

IFN(t1.Day_Diff >= 0 AND t1.Day_Diff <= 3,(t1.Time_Diff + (t1.Day_Diff * 24 * 60 * 60)), 0) as Add_24hours

To explain what I have done. The IFN function is a numeric version of an IF statement that run faster in the SQL procedure than CASE statements do. Your multiple nested CASE statements are unnecessary and you only need one WHEN clause and an ELSE clause, so an IFN function is an alternative. When t1.Day_Diff is between 0 and 3 (I assume it is an integer and contains date or day differences) you want add that many blocks of 24 hours to t1.Time_Diff. Since a TIME variable is stored as the integer number of seconds since midnight (on 1 Jan 1960) this means the number of days you add to t1.Time_Diff is the result of 60 seconds times 60 minutes time 24 hours times t1.Day_Diff. Though if t1.Day_Diff is more than 3 or less than zero then t1.Time_Diff is zero. However, if you instead wanted a missing value because the calculation was invalid you could replace the last zero(0) in the IFN function with a period(.) to represent the missing value instead. If you use this formula, you need to be aware that your TIME variable is turning into a DATETIME variable because of the number of hours you have added, so you need to use a different TIME format or a DATETIME format that will display more than 24 hours, either as days or real hours rather than clock hours for the variable Add_24hours.

If you do not want to use the IFN function then the following CASE statement is equivalent.

CASE
  WHEN (t1.Day_Diff >= 0 AND t1.Day_Diff <= 3
      THEN (t1.Time_Diff + (t1.Day_Diff * 24 * 60 * 60))
  ELSE 0
END  as Add_24hours

Also, to do the same thing your original CASE statement could be rewritten as

CASE t1.Day_Diff
  WHEN 0 THEN (t1.Time_Diff)
  WHEN 1 THEN (t1.Time_Diff + (1 * 24 * 60 * 60))
  WHEN 2 THEN (t1.Time_Diff + (2 * 24 * 60 * 60))
  WHEN 3 THEN (t1.Time_Diff + (3 * 24 * 60 * 60))
  ELSE 0
END  as Add_24hours

You might not have had this problem at all if, at your first attempt, you had written you times as "24:00:00"T, 2 * "24:00:00"T, 3 * "24:00:00"T and used zero without quotes in your first try. The string "24:00:00"T is a number and is the same as 24 * 60 * 60! But then we wouldn't have had the opportunity to learn from your experience that there are frequently several alternative solutions to an problem.

PS: Personally, I prefer directly working with the original DATETIMES and using the INTNX and INTCK functions to manipulate them. But it looks like you don't have that option in this situation. - Cameron (talk) 06:29, 29 April 2017 (CDT)