Solved
Contributor
Posts: 46

# How to do calculation against first value for each id?

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.

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

Accepted Solutions
Solution
‎02-21-2016 01:03 AM
Posts: 5,540

## Re: How to do calculation against first value for each id?

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;

``````
PG

All Replies
Solution
‎02-21-2016 01:03 AM
Posts: 5,540

## Re: How to do calculation against first value for each id?

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;

``````
PG
Contributor
Posts: 46

## Re: How to do calculation against first value for each id?

thanks so much! this is eactly I needed.

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``````

Posts: 5,540

## Re: How to do calculation against first value for each id?

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;

``````
PG
🔒 This topic is solved and locked.