topic Re: Keep the first average value in data set in SAS Programming

Keep the first average value in data set

jonatan_velarde — Tue, 16 Apr 2019 16:28:39 GMT

Good day SAS friends:

I have this program:

data have;
input ID DAM SIRE LAMBING_ORDER BW WW W180;
cards;
101 1 503 1 1.95 16.61 35.13
102 1 504 1 2.05 12.58 29.68
103 2 505 1 2.03 13.44 32.12
104 2 506 2 2.58 12.87 35.60
105 2 507 2 1.63 15.45 24.01
106 3 508 1 2.12 16.36 25.93
107 3 509 2 2.42 12.20 28.70
108 4 510 1 2.56 16.07 25.07
109 5 511 1 2.02 10.98 22.71
110 6 512 1 1.90 15.69 27.06
111 7 513 1 1.35 12.00 25.87
112 7 514 1 1.96 12.27 27.26
113 7 515 1 2.13 17.74 24.60
114 7 516 2 1.93 12.78 23.19
115 7 517 2 2.17 14.48 21.85
116 7 518 2 2.40 18.78 25.86
117 7 519 3 1.92 15.17 23.78
118 7 520 3 2.26 14.38 24.67
119 8 521 1 2.11 15.76 24.87
120 8 522 1 2.12 17.09 30.61
121 8 523 1 2.46 14.18 32.62
122 8 524 2 2.64 12.77 31.67
123 8 525 2 2.08 14.82 37.47
124 8 526 2 2.14 11.59 28.09
;

data want;
do until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;
sum_bw+bw; /* this is a SUM statement */
end;
do until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;
output;
end;
sum_bw=0; /* cumvalue is retained because of SUM statement */
run;

PROC PRINT DATA = WANT;
RUN;

and my result is:

Obs	ID	DAM	SIRE	LAMBING_ORDER	BW	WW	W180	sum_bw
1	101	1	503	1	1.95	16.61	35.13	4.00
2	102	1	504	1	2.05	12.58	29.68	4.00
3	103	2	505	1	2.03	13.44	32.12	2.03
4	104	2	506	2	2.58	12.87	35.60	4.21
5	105	2	507	2	1.63	15.45	24.01	4.21
6	106	3	508	1	2.12	16.36	25.93	2.12
7	107	3	509	2	2.42	12.20	28.70	2.42
8	108	4	510	1	2.56	16.07	25.07	2.56
9	109	5	511	1	2.02	10.98	22.71	2.02
10	110	6	512	1	1.90	15.69	27.06	1.90
11	111	7	513	1	1.35	12.00	25.87	5.44
12	112	7	514	1	1.96	12.27	27.26	5.44
13	113	7	515	1	2.13	17.74	24.60	5.44
14	114	7	516	2	1.93	12.78	23.19	6.50
15	115	7	517	2	2.17	14.48	21.85	6.50
16	116	7	518	2	2.40	18.78	25.86	6.50
17	117	7	519	3	1.92	15.17	23.78	4.18
18	118	7	520	3	2.26	14.38	24.67	4.18
19	119	8	521	1	2.11	15.76	24.87	6.69
20	120	8	522	1	2.12	17.09	30.61	6.69
21	121	8	523	1	2.46	14.18	32.62	6.69
22	122	8	524	2	2.64	12.77	31.67	6.86
23	123	8	525	2	2.08	14.82	37.47	6.86
24	124	8	526	2	2.14	11.59	28.09	6.86

But i want is

Obs	ID	DAM	SIRE	LAMBING_ORDER	BW	WW	W180	sum_bw
1	101	1	503	1	1.95	16.61	35.13	4.00
2	102	1	504	1	2.05	12.58	29.68	.
3	103	2	505	1	2.03	13.44	32.12	2.03
4	104	2	506	2	2.58	12.87	35.60	4.21
5	105	2	507	2	1.63	15.45	24.01	.
6	106	3	508	1	2.12	16.36	25.93	2.12
7	107	3	509	2	2.42	12.20	28.70	2.42
8	108	4	510	1	2.56	16.07	25.07	2.56
9	109	5	511	1	2.02	10.98	22.71	2.02
10	110	6	512	1	1.90	15.69	27.06	1.90
11	111	7	513	1	1.35	12.00	25.87	5.44
12	112	7	514	1	1.96	12.27	27.26	.
13	113	7	515	1	2.13	17.74	24.60	.
14	114	7	516	2	1.93	12.78	23.19	6.50
15	115	7	517	2	2.17	14.48	21.85	.
16	116	7	518	2	2.40	18.78	25.86	.
17	117	7	519	3	1.92	15.17	23.78	4.18
18	118	7	520	3	2.26	14.38	24.67	.
19	119	8	521	1	2.11	15.76	24.87	6.69
20	120	8	522	1	2.12	17.09	30.61	.
21	121	8	523	1	2.46	14.18	32.62	.
22	122	8	524	2	2.64	12.77	31.67	6.86
23	123	8	525	2	2.08	14.82	37.47	.
24	124	8	526	2	2.14	11.59	28.09	.

This means, that i want to keep just the first value of the sumatori of BW.

Thanks in advance

Re: Keep the first average value in data set

novinosrin — Tue, 16 Apr 2019 16:35:07 GMT

Hi @jonatan_velarde

Slight logic tweak

do _n_=1 by 1 until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;

if _n_>1 then call missing(sum_bw);
output;

data have;
input ID DAM SIRE LAMBING_ORDER BW WW W180;
cards;
101 1 503 1 1.95 16.61 35.13
102 1 504 1 2.05 12.58 29.68
103 2 505 1 2.03 13.44 32.12
104 2 506 2 2.58 12.87 35.60
105 2 507 2 1.63 15.45 24.01
106 3 508 1 2.12 16.36 25.93
107 3 509 2 2.42 12.20 28.70
108 4 510 1 2.56 16.07 25.07
109 5 511 1 2.02 10.98 22.71
110 6 512 1 1.90 15.69 27.06
111 7 513 1 1.35 12.00 25.87
112 7 514 1 1.96 12.27 27.26
113 7 515 1 2.13 17.74 24.60
114 7 516 2 1.93 12.78 23.19
115 7 517 2 2.17 14.48 21.85
116 7 518 2 2.40 18.78 25.86
117 7 519 3 1.92 15.17 23.78
118 7 520 3 2.26 14.38 24.67
119 8 521 1 2.11 15.76 24.87
120 8 522 1 2.12 17.09 30.61
121 8 523 1 2.46 14.18 32.62
122 8 524 2 2.64 12.77 31.67
123 8 525 2 2.08 14.82 37.47
124 8 526 2 2.14 11.59 28.09
;
data want;
do until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;
sum_bw+bw; /* this is a SUM statement */
end;
do _n_=1 by 1 until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;
if _n_>1 then call missing(sum_bw);
output;
end;
sum_bw=0; /* cumvalue is retained because of SUM statement */
run;

Re: Keep the first average value in data set

jonatan_velarde — Tue, 16 Apr 2019 16:37:25 GMT

Awesome!!!

You Rock dude@@@!!!

Re: Keep the first average value in data set

jonatan_velarde — Tue, 16 Apr 2019 16:42:12 GMT

Thank you, i would like to know how to make this for the next variables WW and W180.

Thanks

Re: Keep the first average value in data set

jonatan_velarde — Tue, 16 Apr 2019 17:03:19 GMT

data want;
do until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;
sum_BW+BW; /* this is a SUM statement */
sum_WW+WW; /* this is a SUM statement */
sum_W180+W180; /* this is a SUM statement */
end;
do _n_=1 by 1 until(last.LAMBING_ORDER);
set have;
by dam LAMBING_ORDER;
if _n_>1 then call missing(sum_BW);
if _n_>1 then call missing(sum_WW);
if _n_>1 then call missing(sum_W180);
output;
end;
sum_BW=0; /* cumvalue is retained because of SUM statement */
sum_WW=0; /* cumvalue is retained because of SUM statement */
sum_W180=0; /* cumvalue is retained because of SUM statement */
run;

Re: Keep the first average value in data set

novinosrin — Tue, 16 Apr 2019 17:54:21 GMT

HI @jonatan_velarde

if _n_>1 then call missing(sum_BW,sum_WW,sum_W180 );

And if there are too many, we can use arrays.