Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Date Calculation

Reply
Regular Contributor
Posts: 215

Date Calculation

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;

Super User
Posts: 10,497

Re: Date Calculation

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.

Regular Contributor
Posts: 215

Re: Date Calculation

Hi Ballardw,

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

Valued Guide
Posts: 858

Re: Date Calculation

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.

Regular Contributor
Posts: 215

Re: Date Calculation

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,

Valued Guide
Posts: 858

Re: Date Calculation

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. 

Valued Guide
Posts: 858

Re: Date Calculation

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;

Super Contributor
Posts: 275

Re: Date Calculation

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

Super User
Posts: 5,081

Re: Date Calculation

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.

Regular Contributor
Posts: 215

Re: Date Calculation

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,

Super User
Posts: 5,081

Re: Date Calculation

Looks like you would want this version then:

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

Ask a Question
Discussion stats
  • 10 replies
  • 568 views
  • 1 like
  • 5 in conversation