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;
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.
Hi Ballardw,
It works only where all observations exist. In my case, calculation from first observation returns 11 days, but second observation returns nothing.
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.
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,
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.
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;
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);
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.
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,
Looks like you would want this version then:
total_days = sum(last_day - first_day, hold_start - hold_resume);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.