topic Re: How to calculated row difference by group with 'from - to' in SAS Programming

How to calculated row difference by group with 'from - to'

cactooos — Mon, 11 Jan 2021 16:01:25 GMT

Hi,

I would like to calculate difference between START - END for each name.

Assuming: table is already sorted by name date,

START is always first,

has to be at least one START per name but always there is a sequence START END START..

Table have like this:

DATA HAVE;
LENGTH name $10. var1 $5.;
 INPUT name $ date :date9. var1 $;
 format date date9. ;
 DATALINES;
aaa 10JUN2020 START
aaa 15JUN2020 END
bbb 10JUL2020 START
bbb 20JUL2020 END
bbb 25JUL2020 START
bbb 01SEP2020 END
ccc 20JUN2020 START
ddd 15JUL2020 START
ddd 30JUL2020 END
ddd 02SEP2020 START
;
RUN;

want like this:

I know how to do it within the group but got lost with START END piece

Huge thanks in advance

Re: How to calculated row difference by group with 'from - to'

gamotte — Mon, 11 Jan 2021 16:09:47 GMT

Hello,

data want;
    set have;

    ldate=lag(date);
    if var1="START" then dif=0;
    else dif=intck('day', ldate, date);
run;

Edit : removed useless by name statement.

Re: How to calculated row difference by group with 'from - to'

cactooos — Mon, 11 Jan 2021 16:19:34 GMT

Works perfectly, thanks!