topic Re: How to do calculation against first value for each id? in SAS Programming

How to do calculation against first value for each id?

Lulus — Sun, 21 Feb 2016 05:23:27 GMT

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

004

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

PGStats — Sun, 21 Feb 2016 05:35:55 GMT

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;

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

Lulus — Sun, 21 Feb 2016 06:03:00 GMT

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

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

PGStats — Sun, 21 Feb 2016 06:18:03 GMT

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;