DATA Step, Macro, Functions and more

Find difference between two dates..

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Find difference between two dates..

Hi All,

I have a data like below..

data test;

input id name date anydtdte23.;

format date date9.;

cards;

10 bb 2012-04-13 03:36:53.350

11 cc 2012-04-21 15:27:59.003

12 dd 2012-04-14 04:48:18.030

13 ee 2012-04-18 22:17:29.357

;

run;

I want to create a new flag, and  data should be like below(that means difference between 1st row minus second, second minus third so on.....)..

and if  i want to know, diff b/w months  or years ...how could i do...please provide the code for this.

flag

-7

7

-4

.

could any body help..that should be appreciated.

Thanks & Regards,

Yaswanth


Accepted Solutions
Solution
‎12-28-2012 08:13 PM
Respected Advisor
Posts: 3,892

Re: Find difference between two dates..

Below code uses the same approach like ErikT but is implemented with a MERGE statement instead of a combination of SET statements.

data test;
input date anydtdte23.;
format date date9.;
cards;
2012-04-13 03:36:53.350
2012-04-21 15:27:59.003
2012-04-14 04:48:18.030
2012-04-18 22:17:29.357
;
run;

data want;
  merge test test(firstobs=2 keep=date rename=(date=date1));
  Day_Diff=intck('days',date1,date);
run;

View solution in original post


All Replies
Respected Advisor
Posts: 3,892

Re: Find difference between two dates..

What have you done so far? Could you please post some of your code and tell us where you've got stuck?

Contributor
Posts: 70

Re: Find difference between two dates..

Hi Patrick,

Thanks for your quick reply..

I am working on below mentioned code..

data test;

input date anydtdte23.;

format date date9.;

cards;

2012-04-13 03:36:53.350

2012-04-21 15:27:59.003

2012-04-14 04:48:18.030

2012-04-18 22:17:29.357

;

run;

proc print data = test;

run;

data test1;

set test;

date1 = intck('days',date,lag(date));

run;

proc print data = test1;

run;

Output:

  Obs         date    date1

   1     13APR2012       .

   2     21APR2012      -8

   3     14APR2012       7

   4     18APR2012      -4

Here i am getting the  difference between dates(date1) populating correctly..but the thing was i need the date1 difference should start from 1st row like below..

required output:

Obs         date    date1

   1     13APR2012       -8

   2     21APR2012        7

   3     14APR2012       -4

   4     18APR2012      .

Thanks

Yaswanth

Contributor
Posts: 69

Re: Find difference between two dates..

write out a new data set with what you are already doing well, just keeping the date1 variable... but also delete the first '.'

(you could add this to your data step)

keep date1;

if date1=. then delete;

then, concatenate that data set to your original one so that the first obs of each will be put together, then the 2nd obs of each, etc.

data combined-data-set;

     set original-set;

     set new-data-set;

run;

Occasional Contributor
Posts: 10

Re: Find difference between two dates..

SAS data step doesn't allow you to read ahead to the next record, not within a single pass of the data anyway.  The LAG function is retaining the value from the previously read record so that's why your initial attempt to put the date difference calculation was "offset" by one line (for your tastes anyway).   Fortunately, you should be able to accomplish this type of staged calculation using a hash table approach in V9 of SAS.   Try the following.....

data test ;

input id name $2. date anydtdte23. ;

format date date9. ;

counter=_n_ ;   /* create an observation counter from automatic PDV variable.  */

mykey=counter-1 ;    /* create a trick field to apply as key to previous record in subsequent hash table step.  */

cards ;

...... your data here .....

;

run;

data test2(drop=rc  mykey  tempdt  counter ) ;

if 0 then set test(keep=mykey date  rename=(date=tempdt) ) ;   /* rename date field to use it in calculation step later */

if _n_=1 then do;    /* define the hash table definition once */

  declare  hash  aa(dataset:'work.test(keep=mykey date  where=(mykey>0)  rename=(date=tempdt) )'  ) ;   /* throw out the first obs from original file */

  rc=aa.definekey('mykey') ;   /*  key field on which to identify matching records */

  rc=aa.definedata('tempdt') ;   /*  the data field(s) to introduce into the PDV when a match is found on the key value */

  rc=aa.definedone() ;    /*  end the definition of the hash object  */

end;

set  test(drop=mykey) ;    /* read in all the original data but throw out the mykey field */

call missing(tempdt) ;    /* initialize the temporary date field to missing for each pass of the main data, otherwise values would persist */

rc=aa.find( key: counter ) ;   /* look in the hash object for a matching record, based on the value of the counter field */

diff=intck('day' , tempdt , date )  ;

format tempdt  date9. ;

run;

proc print ; run ;

Solution
‎12-28-2012 08:13 PM
Respected Advisor
Posts: 3,892

Re: Find difference between two dates..

Below code uses the same approach like ErikT but is implemented with a MERGE statement instead of a combination of SET statements.

data test;
input date anydtdte23.;
format date date9.;
cards;
2012-04-13 03:36:53.350
2012-04-21 15:27:59.003
2012-04-14 04:48:18.030
2012-04-18 22:17:29.357
;
run;

data want;
  merge test test(firstobs=2 keep=date rename=(date=date1));
  Day_Diff=intck('days',date1,date);
run;

Contributor
Posts: 31

Re: Find difference between two dates..

KoMartin66 says: "SAS data step doesn't allow you to read ahead to the next record, not within a single pass of the data anyway."

Well there is no function for it, but that does not mean that you cannot do it!

This is how I would solve the issue:

data test;

input date anydtdte23.;

format date date9.;

cards;

2012-04-13 03:36:53.350

2012-04-21 15:27:59.003

2012-04-14 04:48:18.030

2012-04-18 22:17:29.357

;

run;

data test1;

set test;

set test(firstobs=2 rename=(date=nextdate)) test(drop = _all_) ;

difference = date - nextdate;

run;

proc print;

run;

The key to the solution is to use 2 SET statements. The second one starts reading at the second observation and is therefore always one step ahead. It would therefore also hit "end of file" first. That is the reason to include the dataset also without the firstobs option, but with "drop=_all_".

You can calculate the difference like you did with the INTCK function, but hey, these are SAS dates, so numbers, thus you can simply  subtract them!

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 1320 views
  • 0 likes
  • 5 in conversation