Hello;
I have following data set
ID birth_date sample_date parity time quantity wt bcs
IE121067041077 07/04/11 11/04/11 4 2 5.3 . .
IE121067041077 07/04/11 12/04/11 4 1 11.9 . .
IE121067041077 07/04/11 12/04/11 4 2 5.3 . .
IE121067041077 07/04/11 13/04/11 4 1 11.9 . .
IE121067041077 07/04/11 13/04/11 4 2 6.1 . .
IE151306820735 31/01/10 26/11/10 5 1 8.7 434 .
IE151306820735 31/01/10 26/11/10 5 2 1.8 434 .
IE151306820735 31/01/10 27/11/10 5 1 5.3 . .
IE151306820735 31/01/10 27/11/10 5 2 2 . .
IE151306820735 31/01/10 28/11/10 5 1 4.5 . .
IE151306820735 31/01/10 29/11/10 5 1 5 . .
IE151306820735 31/01/10 29/11/10 5 2 2.5 . .
There is repeated measurement for quantity (1, 2 and sometime 3)for same sample date.
I want to add these values (quantity) in total for each day and make new table.
I could not figureout the SAS code and if someone help me.
data test;
length id $16;
format birth_date sample_date yymmdd10.;
input ID $ birth_date :ddmmyy8. sample_date :ddmmyy8. parity time quantity wt bcs;
datalines;
IE121067041077 07/04/11 11/04/11 4 2 5.3 . .
IE121067041077 07/04/11 12/04/11 4 1 11.9 . .
IE121067041077 07/04/11 12/04/11 4 2 5.3 . .
IE121067041077 07/04/11 13/04/11 4 1 11.9 . .
IE121067041077 07/04/11 13/04/11 4 2 6.1 . .
IE151306820735 31/01/10 26/11/10 5 1 8.7 434 .
IE151306820735 31/01/10 26/11/10 5 2 1.8 434 .
IE151306820735 31/01/10 27/11/10 5 1 5.3 . .
IE151306820735 31/01/10 27/11/10 5 2 2 . .
IE151306820735 31/01/10 28/11/10 5 1 4.5 . .
IE151306820735 31/01/10 29/11/10 5 1 5 . .
IE151306820735 31/01/10 29/11/10 5 2 2.5 . .
;
proc sql;
create table want as
select distinct a.id ,a.birth_date,a.sample_date,parity,daily_quantity
from test a,(select id,sample_date,sum(quantity) as daily_quantity from test group by id,sample_date) b
where a.id=b.id and a.sample_date=b.sample_date;
quit;
proc print;run;
sample_ daily_
Obs id birth_date date parity quantity
1 IE121067041077 2011-04-07 2011-04-11 4 5.3
2 IE121067041077 2011-04-07 2011-04-12 4 17.2
3 IE121067041077 2011-04-07 2011-04-13 4 18.0
4 IE151306820735 2010-01-31 2010-11-26 5 10.5
5 IE151306820735 2010-01-31 2010-11-27 5 7.3
6 IE151306820735 2010-01-31 2010-11-28 5 4.5
7 IE151306820735 2010-01-31 2010-11-29 5 7.5
Linlin
I assumed you wanted to drop time and quantity, since they don't make sense for daily values :
data test;
length id $16;
format birth_date sample_date yymmdd10.;
input ID $ birth_date :ddmmyy8. sample_date :ddmmyy8. parity time quantity wt bcs;
datalines;
IE121067041077 07/04/11 11/04/11 4 2 5.3 . .
IE121067041077 07/04/11 12/04/11 4 1 11.9 . .
IE121067041077 07/04/11 12/04/11 4 2 5.3 . .
IE121067041077 07/04/11 13/04/11 4 1 11.9 . .
IE121067041077 07/04/11 13/04/11 4 2 6.1 . .
IE151306820735 31/01/10 26/11/10 5 1 8.7 434 .
IE151306820735 31/01/10 26/11/10 5 2 1.8 434 .
IE151306820735 31/01/10 27/11/10 5 1 5.3 . .
IE151306820735 31/01/10 27/11/10 5 2 2 . .
IE151306820735 31/01/10 28/11/10 5 1 4.5 . .
IE151306820735 31/01/10 29/11/10 5 1 5 . .
IE151306820735 31/01/10 29/11/10 5 2 2.5 . .
;
proc sort data=test; by ID birth_date sample_date parity time; run;
data want;
do until(last.sample_date);
set test;
by ID sample_date;
totalQuantity + quantity;
end;
drop time quantity;
run;
PG
Hello; thank you very much But I was not able to give clear question consequently not desired output
I just want to sum the quantity of each day production (since it is milk production of cow so time 1 means morning milking, 2 means afternoon milking and some data also contain time 3). So I just want to sum milk production of each date (each day for individual cow ID) which I will use for next analysis. I have 500 thousand records so it is important for each day milk productions.
The result was as follow
Obs id birth_date sample_date parity wt bcs totalQuantity
1 IE121067041077 2011-04-07 2011-04-11 4 . . 5.3
2 IE121067041077 2011-04-07 2011-04-12 4 . . 22.5
3 IE121067041077 2011-04-07 2011-04-13 4 . . 40.5
4 IE151306820735 2010-01-31 2010-11-26 5 434 . 51.0
5 IE151306820735 2010-01-31 2010-11-27 5 . . 58.3
6 IE151306820735 2010-01-31 2010-11-28 5 . . 62.8
7 IE151306820735 2010-01-31 2010-11-29 5 . . 70.3
but I want
1 IE121067041077 2011-04-07 2011-04-11 4 . . 5.3
2 IE121067041077 2011-04-07 2011-04-12 4 . . 17.2
3 IE121067041077 2011-04-07 2011-04-13 4 . . 18.0
4 IE151306820735 2010-01-31 2010-11-26 5 434 . and so so
5 IE151306820735 2010-01-31 2010-11-27 5 . .
6 IE151306820735 2010-01-31 2010-11-28 5 . .
7 IE151306820735 2010-01-31 2010-11-29 5 . .
Then simplify the code to :
data test;
length id $16;
format birth_date sample_date yymmdd10.;
input ID $ birth_date :ddmmyy8. sample_date :ddmmyy8. parity time quantity wt bcs;
datalines;
IE121067041077 07/04/11 11/04/11 4 2 5.3 . .
IE121067041077 07/04/11 12/04/11 4 1 11.9 . .
IE121067041077 07/04/11 12/04/11 4 2 5.3 . .
IE121067041077 07/04/11 13/04/11 4 1 11.9 . .
IE121067041077 07/04/11 13/04/11 4 2 6.1 . .
IE151306820735 31/01/10 26/11/10 5 1 8.7 434 .
IE151306820735 31/01/10 26/11/10 5 2 1.8 434 .
IE151306820735 31/01/10 27/11/10 5 1 5.3 . .
IE151306820735 31/01/10 27/11/10 5 2 2 . .
IE151306820735 31/01/10 28/11/10 5 1 4.5 . .
IE151306820735 31/01/10 29/11/10 5 1 5 . .
IE151306820735 31/01/10 29/11/10 5 2 2.5 . .
;
proc sort data=test; by ID sample_date; run;
data want(keep=ID sample_date totalQuantity);
do until(last.sample_date);
set test;
by ID sample_date;
totalQuantity + quantity;
end;
run;
PG
Is this want you want?
data test;
length id $16;
format birth_date sample_date yymmdd10.;
input ID $ birth_date :ddmmyy8. sample_date :ddmmyy8. parity time quantity wt bcs;
datalines;
IE121067041077 07/04/11 11/04/11 4 2 5.3 . .
IE121067041077 07/04/11 12/04/11 4 1 11.9 . .
IE121067041077 07/04/11 12/04/11 4 2 5.3 . .
IE121067041077 07/04/11 13/04/11 4 1 11.9 . .
IE121067041077 07/04/11 13/04/11 4 2 6.1 . .
IE151306820735 31/01/10 26/11/10 5 1 8.7 434 .
IE151306820735 31/01/10 26/11/10 5 2 1.8 434 .
IE151306820735 31/01/10 27/11/10 5 1 5.3 . .
IE151306820735 31/01/10 27/11/10 5 2 2 . .
IE151306820735 31/01/10 28/11/10 5 1 4.5 . .
IE151306820735 31/01/10 29/11/10 5 1 5 . .
IE151306820735 31/01/10 29/11/10 5 2 2.5 . .
;
proc sql;
create table want as
select a.*,daily_quantity
from test a,(select id,sample_date,sum(quantity) as daily_quantity from test group by id,sample_date) b
where a.id=b.id and a.sample_date=b.sample_date;
quit;
proc print;run;
Linlin
many thanks; Nearly done; still my result looks like
bs id birth_date date parity time quantity wt bcs quantity
1 IE121067041077 2011-04-07 2011-04-11 4 2 5.3 . . 5.3
2 IE121067041077 2011-04-07 2011-04-12 4 2 5.3 . . 17.2
3 IE121067041077 2011-04-07 2011-04-12 4 1 11.9 . . 17.2
4 IE121067041077 2011-04-07 2011-04-13 4 2 6.1 . . 18.0
5 IE121067041077 2011-04-07 2011-04-13 4 1 11.9 . . 18.0
6 IE151306820735 2010-01-31 2010-11-26 5 2 1.8 434 . 10.5
7 IE151306820735 2010-01-31 2010-11-26 5 1 8.7 434 . 10.5
8 IE151306820735 2010-01-31 2010-11-27 5 2 2.0 . . 7.3
9 IE151306820735 2010-01-31 2010-11-27 5 1 5.3 . . 7.3
10 IE151306820735 2010-01-31 2010-11-28 5 1 4.5 . . 4.5
11 IE151306820735 2010-01-31 2010-11-29 5 2 2.5 . . 7.5
12 IE151306820735 2010-01-31 2010-11-29 5 1 5.0 . . 7.5
but I just want to remove entry 2, 4, 6 because they are redundant and sot I only need one record per day and I dont need wt and quantity in new output file anymore.
desired output
1 IE121067041077 2011-04-07 2011-04-11 4 2 5.3 . . 5.3
2 IE121067041077 2011-04-07 2011-04-12 4 2 5.3 . . 17.2
3 IE121067041077 2011-04-07 2011-04-12 4 1 11.9 . . 17.2
4 IE121067041077 2011-04-07 2011-04-13 4 2 6.1 . . 18.0
5 IE121067041077 2011-04-07 2011-04-13 4 1 11.9 . . 18.0
6 IE151306820735 2010-01-31 2010-11-26 5 2 1.8 434 . 10.5
7 IE151306820735 2010-01-31 2010-11-26 5 1 8.7 434 . 10.5
Thanx
data test;
length id $16;
format birth_date sample_date yymmdd10.;
input ID $ birth_date :ddmmyy8. sample_date :ddmmyy8. parity time quantity wt bcs;
datalines;
IE121067041077 07/04/11 11/04/11 4 2 5.3 . .
IE121067041077 07/04/11 12/04/11 4 1 11.9 . .
IE121067041077 07/04/11 12/04/11 4 2 5.3 . .
IE121067041077 07/04/11 13/04/11 4 1 11.9 . .
IE121067041077 07/04/11 13/04/11 4 2 6.1 . .
IE151306820735 31/01/10 26/11/10 5 1 8.7 434 .
IE151306820735 31/01/10 26/11/10 5 2 1.8 434 .
IE151306820735 31/01/10 27/11/10 5 1 5.3 . .
IE151306820735 31/01/10 27/11/10 5 2 2 . .
IE151306820735 31/01/10 28/11/10 5 1 4.5 . .
IE151306820735 31/01/10 29/11/10 5 1 5 . .
IE151306820735 31/01/10 29/11/10 5 2 2.5 . .
;
proc sql;
create table want as
select distinct a.id ,a.birth_date,a.sample_date,parity,daily_quantity
from test a,(select id,sample_date,sum(quantity) as daily_quantity from test group by id,sample_date) b
where a.id=b.id and a.sample_date=b.sample_date;
quit;
proc print;run;
sample_ daily_
Obs id birth_date date parity quantity
1 IE121067041077 2011-04-07 2011-04-11 4 5.3
2 IE121067041077 2011-04-07 2011-04-12 4 17.2
3 IE121067041077 2011-04-07 2011-04-13 4 18.0
4 IE151306820735 2010-01-31 2010-11-26 5 10.5
5 IE151306820735 2010-01-31 2010-11-27 5 7.3
6 IE151306820735 2010-01-31 2010-11-28 5 4.5
7 IE151306820735 2010-01-31 2010-11-29 5 7.5
Linlin
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.