topic Re: Date Calculation in SAS Data Science

Date Calculation

mlogan — Tue, 05 May 2015 19:06:17 GMT

Hi All,

I have following four date. Can anyone tell me what should be the code to calculate Total_Day=(Last_Day-Start_Day) - (Hold_Resume-Hold_Start)

Start_Day Hold_start Hold_Resume Last_Day

Jan-01-2014 Jan-05-2014 Jan-10-2014 Jan-12-2014

Feb-01-2014 Feb-12-2014

Mar-01-2014 Mar-10-2014 Mar-12-2014

I wrote the following code, but it's not working:

data out;

set Have;

Total_Day = (Last_Day -Start_Day)-(Hold_Resume-Hold_start);

run;

Re: Date Calculation

ballardw — Tue, 05 May 2015 19:33:22 GMT

Describe what you mean by "not working". Do you get an error? Unexpected values?

The approach you chose should work if the date variables are SAS date valued variables.

You will get a missing value for any record that has one or more missing values for any of the variables.

Re: Date Calculation

mlogan — Tue, 05 May 2015 19:37:42 GMT

Hi Ballardw,

It works only where all observations exist. In my case, calculation from first observation returns 11 days, but second observation returns nothing.

Re: Date Calculation

Steelers_In_DC — Tue, 05 May 2015 19:48:47 GMT

the second observation returns nothing because null - anything is null. You can use coalesce function to replace null with something else.

data want;

set dates;

total_days = (coalesce(last_day,0)-coalesce(start_day,0))-(coalesce(hold_resume,0)-coalesce(hold_start,0));

run;

when using dates this may not always be helpful. You might want to replace one date with the date you are subtracting it from, depends on the input/output you are looking for.

Re: Date Calculation

mlogan — Tue, 05 May 2015 19:56:15 GMT

Hi Mark Johnson,

Thanks for the code, it's helpful. But it doesn't work when the Hold_Start date is mising. Can you please tell me how do I ignore the second part of the calculation where Hols_Start is missing. See my example on the top.

Thanks,

Re: Date Calculation

Steelers_In_DC — Tue, 05 May 2015 20:11:32 GMT

It is working, you have to substitute the zero for whatever you want to replace a null value with. The date 0 in sas is 01JAN1960, which I assume is not what you want.

Re: Date Calculation

Steelers_In_DC — Tue, 05 May 2015 21:29:19 GMT

coalesce will replace the date with anything you want when it is missing, what do you want to replace it with? If an easy coalesce won't fix it you will have to set up if then,

data want;

set dates;

if not missing(Start_Day,Hold_start,Hold_Resume,Last_Day) then do;

total_days = (last_day-start_day)-(hold_resume-hold_start);

end;

if not missing(Start_Day,Hold_start,Hold_Resume) then do;

/*whatever calculation you want etc*/

end;

run;

Re: Date Calculation

slchen — Tue, 05 May 2015 23:30:31 GMT

It depends on how you deal with missing value. If there is missing value, you either don't calculate or omit it by this way,

total_day=sum(last_day,-start_day,-hold_resume,hold_start);

Re: Date Calculation

Astounding — Wed, 06 May 2015 01:49:35 GMT

Most likely what you are looking for is this:

total_days = sum(last_day - first_day, hold_resume - hold_start);

But the bigger battle is how to handle the unusual conditions in the data. This is not a programming problem, but a matter of making decisions.

What if last_day is earlier than first_day (as in the fourth line of data)?

What if hold_resume is present, but hold_start is absent? Should hold_resume default to the current date or to last_day, or to nothing?

What is hold_resume is present, but hold_start is absent?

If you have made decisions to cover all the possibilities, the programming will be straightforward. But if not, it's anybody's guess as to what the outcome should be.

Re: Date Calculation

mlogan — Wed, 06 May 2015 14:00:45 GMT

Hi Astounding,

Look like you are almost there. I don't want a sum, instead I want a subtraction

You wrote:

total_days = sum(last_day - first_day, hold_resume - hold_start);

I want:

(Last_Day -Start_Day)-(Hold_Resume-Hold_start);

Do you know if there is a way to ignore the null value when date (eg. Hold_Start or Hold_resume) does not exist?

Thanks,

Re: Date Calculation

Astounding — Wed, 06 May 2015 14:11:43 GMT

Looks like you would want this version then:

total_days = sum(last_day - first_day, hold_start - hold_resume);