SAS QUERY

[rakeshvvv]

Hi,

Can someone help me with below query.I have dataset with 3 variables ID,VISIT,DATE...I would like to create new variable 'DAY'  with following criteria...the difference between two succesive dates will be number assigned to 'DAY'. The number in the fourth collumn is the expected result.

100010001           CYCLE 12014-08-05       1

100010001           CYCLE 1 2014-08-06       2

100010001           CYCLE 1 2014-08-07       3

100010001           CYCLE 1 2014-08-12     8

100010001           CYCLE 1 2014-08-19   15

100010001           CYCLE 2 2014-08-26    1

100010001           CYCLE 2 2014-08-27   2

100010001           CYCLE 2 2014-08-28  3

100010001           CYCLE 2 2014-09-02 8

100010001           CYCLE 2 2014-09-09 15

100010001           CYCLE 3 2014-09-16 1

100010001           CYCLE 3 2014-09-17 2

100010001           CYCLE 3 2014-09-18 3

100010001           CYCLE 3 2014-09-23 8

100010001           CYCLE 3 2014-09-30 15

100010001           CYCLE 4 2014-10-07 1

100010001           CYCLE 4 2014-10-08 2

100010001           CYCLE 4 2014-10-09 3

100010001           CYCLE 4 2014-10-14 8

100010001           CYCLE 4 2014-10-21 15

[Reeza]

Your criteria does not match your output.

..the difference between two succesive dates

It looks like its the difference from the first date, not successive dates. Please clarify your requirements.

[rakeshvvv]

Yes Mam...Difference from first date for that particular id and visit....I may not explain correct but the expected output is the same...

[Reeza]

---

@rakeshvvv wrote:
Yes Mam...Difference from first date for that particular id and visit....I may not explain correct but the expected output is the same...

---

 But how do I know which is correct, the words or the data? 🙂

Here's another solution, 

data want;
set have;
by ID VISIT;
retain start_date;

if first.visit then start_date=date;

dur=date-start_date+1;

drop start_date;
run;

[mohamed_zaki · Accepted solution]

data have;
format date YYMMDD10.;
input ID Visit $ Date YYMMDD10.;
cards;
100010001 CYCLE1 2014-08-05
100010001 CYCLE1 2014-08-06
100010001 CYCLE1 2014-08-07
100010001 CYCLE1 2014-08-12
100010001 CYCLE1 2014-08-19
100010001 CYCLE2 2014-08-26
100010001 CYCLE2 2014-08-27
100010001 CYCLE2 2014-08-28
100010001 CYCLE2 2014-09-02
100010001 CYCLE2 2014-09-09
100010001 CYCLE3 2014-09-16
100010001 CYCLE3 2014-09-17
100010001 CYCLE3 2014-09-18
100010001 CYCLE3 2014-09-23
100010001 CYCLE3 2014-09-30
100010001 CYCLE4 2014-10-07
100010001 CYCLE4 2014-10-08
100010001 CYCLE4 2014-10-09
100010001 CYCLE4 2014-10-14
100010001 CYCLE4 2014-10-21
;
run;

data want;
set have;
by ID VISIT;
retain Day;
lagdate=lag(Date);
if first.ID or first.visit then Day=1;
else Day=Day+intck('day',lagdate,Date);
drop lagdate;
run;