SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Difference between Two dataset variable values

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Difference between Two dataset variable values

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

Accepted Solutions
Solution
‎06-15-2017 11:36 PM
PROC Star
Posts: 252

Re: Difference between Two dataset variable values

[ Edited ]

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


All Replies
Contributor
Posts: 58

Re: Difference between Two dataset variable values

 

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

Super User
Posts: 17,828

Re: Difference between Two dataset variable values

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

 

 

Contributor
Posts: 58

Re: Difference between Two dataset variable values

So noithing else neeed to get the correct time difference?

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

 

 

Super User
Posts: 17,828

Re: Difference between Two dataset variable values


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 Smiley Wink 

 

You could also convert to date variables by using the DATEPART function and then use your standard processes that you know. 

Contributor
Posts: 58

Re: Difference between Two dataset variable values

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

Contributor
Posts: 58

Re: Difference between Two dataset variable values

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

 

Super User
Posts: 17,828

Re: Difference between Two dataset variable values


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? Smiley Happy

 

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

Contributor
Posts: 58

Re: Difference between Two dataset variable values

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

Super User
Posts: 17,828

Re: Difference between Two dataset variable values

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. 

Solution
‎06-15-2017 11:36 PM
PROC Star
Posts: 252

Re: Difference between Two dataset variable values

[ Edited ]

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);
 
Contributor
Posts: 58

Re: Difference between Two dataset variable values

worked beautifully thank you

Contributor
Posts: 58

Re: Difference between Two dataset variable values

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

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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