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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.