topic Difference between Two dataset variable values in SAS Data Management

Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 02:38:41 GMT

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

Re: Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 02:44:26 GMT

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

Re: Difference between Two dataset variable values

Reeza — Fri, 16 Jun 2017 02:48:16 GMT

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

Re: Difference between Two dataset variable values

kiranv_ — Fri, 16 Jun 2017 03:01:08 GMT

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

Re: Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 03:12:35 GMT

So noithing else neeed to get the correct time difference?

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

Re: Difference between Two dataset variable values

Reeza — Fri, 16 Jun 2017 03:23:38 GMT

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

Re: Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 03:35:45 GMT

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 🙂

Re: Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 03:36:46 GMT

worked beautifully thank you

Re: Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 03:48:35 GMT

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

Re: Difference between Two dataset variable values

Reeza — Fri, 16 Jun 2017 03:56:28 GMT

@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

Re: Difference between Two dataset variable values

AnnaNZ — Fri, 16 Jun 2017 04:04:20 GMT

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

Re: Difference between Two dataset variable values

Reeza — Fri, 16 Jun 2017 04:28:24 GMT

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.

Re: Difference between Two dataset variable values

AnnaNZ — Sat, 17 Jun 2017 08:44:12 GMT

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