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.
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;
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?
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,
This works well!
Thank you
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.