Substract rows from same column by same id and group

[dmanri]

Hello,

I am working with data from one dairy farm. I want to subtract the value of the column "calving_date" only if the column abort_dim = 0 to the calving_date of the previous lactation and within the same ID.

For example from ID 1, I want to subtract 2/25/2018 - 1/30/2018 because it has a 0 on the abort_dim column on lactation 3 and the previous lactation was 2.

I have been trying to use SQL, but since I have three grouping conditions I am struggling to get want I want. 

This is sample data, my original data has thousands of rows.

Any help will be greatly appreciated.

[novinosrin]

Hi @dmanri Can you please post the expected output for the sample. Also, can you please paste here as plain text rather than as attachement?

[ed_sas_member]

Hi @dmanri 

Here is something you can try.

data have;
	input ID calving_date lactation ABORT_DIM;
	informat calving_date MMDDYY10.;
	format calving_date MMDDYY10.;
	cards;
1 1/15/2018  1 .
1 1/30/2018  2 .
1 2/25/2018  3 0
1 4/4/2018   4 .
2 4/8/2018   3 .
2 4/23/2018  4 .
2 6/10/2018  5 0
3 8/14/2018  2 .
3 10/19/2018 3 0
3 11/5/2018  4 .
3 12/26/2018 5 .
3 7/15/2019  6 .
4 5/26/2035  2 0
5 12/27/2035 1 .
5 1/18/2036  2 .
5 1/19/2036  3 .
6 1/24/2036  5 .
7 3/22/2036  4 .
7 6/18/2036  5 0
7 6/27/2036  6 .
;
run;

proc sort data=have;
	by ID lactation;
run;

data want;
	set have;
	day_int = intck('day',lag(calving_date),calving_date);
	if ID ne lag(ID) or lactation ne lag(lactation)+1 or abort_dim ne 0 then call missing (day_int);
run;

Best,

[dmanri]

This works well!

Thank you

[Shmuel · Accepted solution]

Do you mean:

/* assuming data have is sorted by ID and - Location or calving_date as need */
data want;
 set have;
       by ID;
       if not first.id then do;
          if abort_dim = 0 then 
             dif_date = calving_date - lag(calving_date);
      end;
run;