topic Re: Find difference between two dates.. in SAS Programming

Find difference between two dates..

yaswanthj — Thu, 27 Dec 2012 09:45:09 GMT

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

-4

could any body help..that should be appreciated.

Thanks & Regards,

Yaswanth

Re: Find difference between two dates..

Patrick — Thu, 27 Dec 2012 11:11:02 GMT

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

Re: Find difference between two dates..

yaswanthj — Thu, 27 Dec 2012 11:25:44 GMT

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

Re: Find difference between two dates..

cau83 — Thu, 27 Dec 2012 17:29:21 GMT

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;

Re: Find difference between two dates..

KoMartin66 — Thu, 27 Dec 2012 17:31:41 GMT

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 ;

Re: Find difference between two dates..

ErikT — Thu, 27 Dec 2012 18:29:38 GMT

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!

Re: Find difference between two dates..

Patrick — Sat, 29 Dec 2012 01:13:21 GMT

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;