How to do calculation against first value for each id?

[Lulus]

Hello all,

Have a silly quesiton but really want your help.

I have a plant growth data and it looks like this in the following has [ID, date]

I would like to calcualte date interval  against each first_date (planting date for each plant) 

Is there an efficient way to calcualte the "want"?

I have tried to use a data step to set by ID and date and create an ID first_dt and merge this back to the original dataset

and calcuate based on that.  But is there a better way or even a macro coding method?

any efficient logic is appreicated.  because I would need to a lot of this kind of claculaton for next steps.

thanks all for your help! 

ID       Date                 want

001   01Jan2012            0 

001   01Feb2012           30

001   15Mar2012           63

001   01Apr2013            79       

001   06Mar2013           105

002   01Jan2012            0

002   01Feb2012           30                      ...

003

003

004

004

004

004

[PGStats · Accepted solution]

Good candidate task for a DOW loop:

data have;
input ID Date :date.;
format date date9.;
datalines;
001   01Jan2012 
001   01Feb2012 
001   15Mar2012 
001   01Apr2013     
001   06Mar2013 
002   01Jan2012 
002   01Feb2012 
;

data want;
do until(last.id);
    set have; by id;
    if first.id then plantDate = date;
    want = intck("DAY", plantDate, date);
    output;
    end;
drop plantDate;
run;

proc print data=want noobs; run;

[Lulus]

thanks so much! this is eactly I needed.

can I ask two follow up quesitons.

1. I would also like to add "gap" which would give me diffrence from current date to previous date (the first added column)

2. sequence of measurement

both value is based on unique ID.  thank you in advance for helping. 

001   01Jan2012    0    1
001   01Feb2012    28   2
001   15Mar2012    45   3
001   01Apr2013    16   4
001   06Mar2013    36   5
002   01Jan2012     0   1
002   01Feb2012    28   2

[PGStats]

I also added a sort, just in case.

data have;
input ID Date :date.;
format date date9.;
datalines;
001   01Jan2012 
001   01Feb2012 
001   15Mar2012 
001   01Apr2013     
001   06Mar2013 
002   01Jan2012 
002   01Feb2012 
;

proc sort data=have; by id date; run;

data want;
seq = 0;
do until(last.id);
    set have; by id;
    if first.id then plantDate = date;
    seq + 1;
    gap = intck("DAY", coalesce(prevDate, date), date);
    want = intck("DAY", plantDate, date);
    output;
    prevDate = date;
    end;
drop plantDate prevDate;
run;

proc print data=want noobs; run;