Hi
I have various proceedures where I need to work out the time or the age difference between two datetime variables.
In principle I principle I thought that I could just deduct datetime2 from datetime1, as this would give me the time difference in seconds.
Data one; Set two; REFER_VAR= (REFER_DT - LST_UPDT_DT ); RUN;
Seems not to work as results are faulty as can be seen on the screenshot below.
In this example below I want the difference between REFER_DT and LST_UPDT_DT resulting in REFER_VAR.
Eventually I want to keep only the smallest value of each REFER_VAR for each REFER_ID.
I think I can do the last part with a proc means function (but any advice is appreaciated):
proc means data= a NOPRINT ;
class REFER_ID;
var REFER_VAR;output out=b min=min max=max;
run;
However, I am failling with the datetime values. Can somenody please help how to set them up between to datetime variables? There are plenty of examples in the net with single dates, but I am having trouble to incorporate my datetime variables: REFER_DT and LST_UPDT_DT
What do I need to do in order to get the right timedifferences given that different months have different mount of days?
Many thanks
may be something like this
proc sql; create table have as select * from (select *, (dtime2-dtime1) as difference from want) group by id having difference = min(difference);
Timedifference needed between LST_UPDT_DT and REFER_DT
Then only those REFER_ID with the smaller timedifference (for each REER_ID - there are several of the same) are selected to the next datastep
The difference is being calculated currently. Why do you think it's wrong?
So noithing else neeed to get the correct time difference?
- and then just divide by 3600 to get minutes or 86400 to get days.
@AnnaNZ wrote:
So noithing else neeed to get the correct time difference?
- and then just divide by 3600 to get minutes or 86400 to get days.
Not necessarily.
You just stated it was incorrect, and I'm saying it's not. There's a big difference between 'correct' and not what you want, especially because you had already stated you knew it would give you the difference in seconds, which is what is shown.
If you want the difference in days/months use INTCK, with DTMONTH or DTDAY to get the months or days difference. Personally, I prefer the division method for anything besides weeks and days. There isn't a standard definition for a 'month' which is annoying from a mathematical perspective. But I'm Canadian and we have the metric system 😉
You could also convert to date variables by using the DATEPART function and then use your standard processes that you know.
yes, very true, it is was a case of not seeing the wood for the trees - thnak you very much for your clarification
For reaspons of smplicity, I would like to keep the variables in datetime, as it is easier to come back to them later.
Thank you very much 🙂
What is the best way then to convert this into age?
Say I have two datetime varoiables: DOB and a variable called School Entry, at which age were the children schooled ?
So again I could just get Time_in_Seconds = (School Entry - DOB) - what is the best way to convert this into age including months and days?
May thanks
@AnnaNZ wrote:
So again I could just get Time_in_Seconds = (School Entry - DOB) - what is the best way to convert this into age including months and days?
May thanks
What does that mean? 🙂
Do you want age in months - how are you defining months? ie Zuli is 360 months old
Do you want age in days? ie Zuli is 3650 days old
Do you want age in Year, month, days? Ie Zuli is 10 years, 5 months and 4 days old
I am age in terms of years, months and days
Often it is just displayed in years, but I would want a more exact approach, therefore something like in your last example: 'Ie Zuli is 10 years, 5 months and 4 days old. '
I could create someting over multiple colums, whereby
age_min =ROUND ((age_sec)/60);
age_h =ROUND ((age_sec)/3600);
age_d =ROUND ((age_sec)/86400);
age_y =ROUND ((age_sec)/31536000);
but that is not very elegant. Would yu know a better way to deal with this?
many thanks
I doubt there's a way with 'less code' but if you're going to do this occoasionally I'd consider a custom function or format.
I feel like I've seen a time format that could do this (custom time) via proc format.
Those are the approaches I would try. I can't provide any code now, but I'll try to remember to look at this question again tomorrow, if someone hasn't already answered it.
may be something like this
proc sql; create table have as select * from (select *, (dtime2-dtime1) as difference from want) group by id having difference = min(difference);
worked beautifully thank you
Hi
the appraoch does in principle resolve my issue however, it delets all REFER_IDs that have no timedifference, meaning,it deletes all the enties where LST_UPDT_DT = REFER_DT
How can I immplement a keep statement ? Something like keep REFER_ID if LST_UPDT_DT = REFER_DT
Many thanks
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.