Add specific variable in 2 or 3 rows and make new data set

[greenhousemilk]

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.

[PGStats]

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

[greenhousemilk]

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        .     .        

[PGStats]

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

[Linlin]

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

[greenhousemilk]

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

[Linlin · Accepted solution]

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