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

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

greenhousemilk — Wed, 04 Jul 2012 20:59:15 GMT

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.

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

PGStats — Wed, 04 Jul 2012 21:23:02 GMT

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;

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

greenhousemilk — Wed, 04 Jul 2012 21:57:04 GMT

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

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

PGStats — Wed, 04 Jul 2012 22:15:49 GMT

Then simplify the code to :

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;

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

Linlin — Wed, 04 Jul 2012 22:19:27 GMT

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

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

greenhousemilk — Wed, 04 Jul 2012 22:48:17 GMT

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

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

Linlin — Thu, 05 Jul 2012 02:07:59 GMT

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