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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

6 REPLIES 6
Patrick
Opal | Level 21

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

yaswanthj
Calcite | Level 5

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

cau83
Pyrite | Level 9

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;

KoMartin66
Obsidian | Level 7

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 ;

Patrick
Opal | Level 21

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;

ErikT
Obsidian | Level 7

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!

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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