BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AnnaNZ
Quartz | Level 8

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


timedif.JPG
1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

View solution in original post

12 REPLIES 12
AnnaNZ
Quartz | Level 8

 

timedif.JPG

 

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

Reeza
Super User

The difference is being calculated currently. Why do you think it's wrong? 

 

 

AnnaNZ
Quartz | Level 8

So noithing else neeed to get the correct time difference?

- and then just divide by 3600 to get minutes or 86400 to get days.

 

 

Reeza
Super User

@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. 

AnnaNZ
Quartz | Level 8

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 🙂

AnnaNZ
Quartz | Level 8

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

 

Reeza
Super User

@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

AnnaNZ
Quartz | Level 8

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

Reeza
Super User

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. 

kiranv_
Rhodochrosite | Level 12

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);
 
AnnaNZ
Quartz | Level 8

worked beautifully thank you

AnnaNZ
Quartz | Level 8

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 12 replies
  • 2314 views
  • 4 likes
  • 3 in conversation