Date Calculation

[mlogan]

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;

[ballardw]

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.

[mlogan]

Hi Ballardw,

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

[Steelers_In_DC]

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.

[mlogan]

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,

[Steelers_In_DC]

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. 

[Steelers_In_DC]

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;

[slchen]

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);

[Astounding]

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.

[mlogan]

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,

[Astounding]

Looks like you would want this version then:

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