sum by every n rows.
in [SAS]
|
Prev: hwo to pull years of data..
Next: IF-THEN
From: Mike Zdeb on 4 Mar 2010 13:23 hi ... if you can live with the sum on each of the two lines ... data have; input n x @@; datalines; 1 -1 2 -50 3 -2 4 -1 5 1 6 -1 7 3 8 2 9 2 10 -2 11 1 12 2 13 5 14 40 15 30 16 39 17 -2 ; run; proc sql; create table want as select n, x, sum(x) as x2 from have group by round(n,2) order by n; quit; data set ... Obs n x x2 1 1 -1 -51 2 2 -50 -51 3 3 -2 -3 4 4 -1 -3 5 5 1 0 6 6 -1 0 7 7 3 5 8 8 2 5 9 9 2 0 10 10 -2 0 11 11 1 3 12 12 2 3 13 13 5 45 14 14 40 45 15 15 30 69 16 16 39 69 17 17 -2 -2 if you really need a missing value, then the sum, add this ... data want; set want end=last; if last and mod(n,2) then return; if mod(n,2) then x2=.; run; data set ... Obs n x x2 1 1 -1 . 2 2 -50 -51 3 3 -2 . 4 4 -1 -3 5 5 1 . 6 6 -1 0 7 7 3 . 8 8 2 5 9 9 2 . 10 10 -2 0 11 11 1 . 12 12 2 3 13 13 5 . 14 14 40 45 15 15 30 . 16 16 39 69 17 17 -2 -2 -- Mike Zdeb U(a)Albany School of Public Health One University Place Rensselaer, New York 12144-3456 P/518-402-6479 F/630-604-1475 > On Thu, Mar 4, 2010 at 2:53 AM, db <daronnebonneau(a)gmail.com> wrote: > >> Hi, >> Could anyone help me on summing following dataset ? >> I have n, x variables and would like to calculate for sum by every 2 >> rows . >> >> n x sum_by_every_2_rows >> 1 -1 >> 2 -50 -51 >> 3 -2 >> 4 -1 -3 >> 5 1 >> 6 -1 0 >> 7 3 >> 8 2 5 >> 9 2 >> 10 -2 0 >> 11 1 >> 12 2 3 >> 13 5 >> 14 40 45 >> 15 30 >> 16 39 69 >> 17 -2 -2 >> >> Thanks, db >> >
From: Arthur Tabachneck on 4 Mar 2010 13:51 If you can't live with the sum on each of the two lines you can still use Mike's approach by simply adding a case statement. e.g.: proc sql; create table want as select n, x, round(n,2) as roundn, case when n eq round(n,2) or count(*) eq 1 then sum(x) end as x2 from have group by round(n,2) order by n; quit; HTH, Art -------- On Thu, 4 Mar 2010 13:23:34 -0500, Mike Zdeb <msz03(a)ALBANY.EDU> wrote: >hi ... if you can live with the sum on each of the two lines ... > >data have; >input n x @@; >datalines; >1 -1 2 -50 3 -2 4 -1 >5 1 6 -1 7 3 8 2 >9 2 10 -2 11 1 12 2 >13 5 14 40 15 30 16 39 >17 -2 >; >run; > >proc sql; >create table want as >select n, x, sum(x) as x2 from have >group by round(n,2) >order by n; >quit; > >data set ... > >Obs n x x2 > 1 1 -1 -51 > 2 2 -50 -51 > 3 3 -2 -3 > 4 4 -1 -3 > 5 5 1 0 > 6 6 -1 0 > 7 7 3 5 > 8 8 2 5 > 9 9 2 0 > 10 10 -2 0 > 11 11 1 3 > 12 12 2 3 > 13 13 5 45 > 14 14 40 45 > 15 15 30 69 > 16 16 39 69 > 17 17 -2 -2 > > >if you really need a missing value, then the sum, add this ... > >data want; >set want end=last; >if last and mod(n,2) then return; >if mod(n,2) then x2=.; >run; > >data set ... > >Obs n x x2 > 1 1 -1 . > 2 2 -50 -51 > 3 3 -2 . > 4 4 -1 -3 > 5 5 1 . > 6 6 -1 0 > 7 7 3 . > 8 8 2 5 > 9 9 2 . > 10 10 -2 0 > 11 11 1 . > 12 12 2 3 > 13 13 5 . > 14 14 40 45 > 15 15 30 . > 16 16 39 69 > 17 17 -2 -2 > > > > >-- >Mike Zdeb >U(a)Albany School of Public Health >One University Place >Rensselaer, New York 12144-3456 >P/518-402-6479 F/630-604-1475 > >> On Thu, Mar 4, 2010 at 2:53 AM, db <daronnebonneau(a)gmail.com> wrote: >> >>> Hi, >>> Could anyone help me on summing following dataset ? >>> I have n, x variables and would like to calculate for sum by every 2 >>> rows . >>> >>> n x sum_by_every_2_rows >>> 1 -1 >>> 2 -50 -51 >>> 3 -2 >>> 4 -1 -3 >>> 5 1 >>> 6 -1 0 >>> 7 3 >>> 8 2 5 >>> 9 2 >>> 10 -2 0 >>> 11 1 >>> 12 2 3 >>> 13 5 >>> 14 40 45 >>> 15 30 >>> 16 39 69 >>> 17 -2 -2 >>> >>> Thanks, db >>> >>
From: Ya Huang on 4 Mar 2010 14:01
One more way: data need; set have (where=(mod(n,2)=0) rename=(x=x0)); set have (where=(mod(n,2)=1) rename=(x=x1)); newx=sum(x0,x1); run; proc print; run; Obs n x0 x1 newx 1 1 -50 -1 -51 2 3 -1 -2 -3 3 5 -1 1 0 4 7 2 3 5 5 9 -2 2 0 6 11 2 1 3 7 13 40 5 45 8 15 39 30 69 On Thu, 4 Mar 2010 00:53:44 -0800, db <daronnebonneau(a)GMAIL.COM> wrote: >Hi, >Could anyone help me on summing following dataset ? >I have n, x variables and would like to calculate for sum by every 2 >rows . > >n x sum_by_every_2_rows >1 -1 >2 -50 -51 >3 -2 >4 -1 -3 >5 1 >6 -1 0 >7 3 >8 2 5 >9 2 >10 -2 0 >11 1 >12 2 3 >13 5 >14 40 45 >15 30 >16 39 69 >17 -2 -2 > >Thanks, db |