topic Summing Based on condition for Year in SAS Programming

Summing Based on condition for Year

yash82 — Wed, 07 Dec 2011 11:40:58 GMT

Hi Guys, I have following dataset

data x;

input year 4. price1 price2 Price3

datalines;

2002 100 200 500

2006 600 . 700

2001 500 400 .

2008 1200 1000 2000

2009 1300 1700 8000

2003 . 500 300

2011 1200 1000 1000

;

run;

Now I want to have the sum of price1 price2 and price3 in two parts i.e. for current year in one part and for remaining years clubed out together put together in another part. So my output should be like this

Year Price1 Price2 Price3

2002 100 200 500

2006 600 . 700

2001 500 400 .

2008 1200 1000 2000

2009 1300 1700 8000

2003 . 500 300

Total 3700 3800 10200

2011 1200 1000 1000

Total 4900 4800 11200

I am using data _null_ for preparing the report so I can just use put @ to print out the values what I am concerned about is my if statements.... can somebody help me out with that... Thanks in advance

Summing Based on condition for Year

NN — Wed, 07 Dec 2011 15:16:00 GMT

Hi,

Can i suggest trying a normal proc report ... Will it help.

data x;

set x;

if year = 2011 then a=1;

else a=0;

run;

proc report data=x;

column a year price1 price2 price3;

define a / group noprint ;

define year / group;

break after a / summarize;

rbreak after / summarize;

run;

Summing Based on condition for Year

art297 — Wed, 07 Dec 2011 17:41:57 GMT

It's a little more complicated doing it in a datastep, rather than using proc report, but you asked:

data x (keep=year price:);

input year $ price1-Price3;

array iprice(*) price:;

array hprice(3);

array tprice(3);

retain tprice:;

do i=1 to 3;

hprice(i)=iprice(i);

end;

if year eq 2011 then do;

year="Total";

do i=1 to 3;

iprice(i)=tprice(i);

end;

output;

year="2011";

do i=1 to 3;

iprice(i)=hprice(i);

end;

output;

do i=1 to 3;

tprice(i)=sum(tprice(i),iprice(i));

end;

if year eq 2011 then do;

year="Total";

do i=1 to 3;

iprice(i)=tprice(i);

end;

output;

end;

datalines;

2002 100 200 500

2006 600 . 700

2001 500 400 .

2008 1200 1000 2000

2009 1300 1700 8000

2003 . 500 300

2011 1200 1000 1000

;

run;

Summing Based on condition for Year

Howles — Wed, 07 Dec 2011 20:06:19 GMT

Consider using a multilabel format.

Re: Summing Based on condition for Year

MikeZdeb — Wed, 07 Dec 2011 23:40:21 GMT

hi ... if the data set is in the order you show and that output is all you want using data _null_ ...

data _null_;

file print;

if _n_ eq 1 then put ' YEAR PRICE1 PRICE2 PRICE3';

set x;

if year ne 2011 then do;

put (year price1-price3) (5. 3*8.);

t1+price1; t2+price2; t3+price3;

end;

else do;

put / 'TOTAL' (t1-t3) (8.);

put (year price1-price3) (5. 3*8.);

t1+price1; t2+price2; t3+price3;

put 'TOTAL' (t1-t3) (8.);

end;

run;

Re: Summing Based on condition for Year

yash82 — Sat, 10 Dec 2011 09:01:58 GMT

Thanks guys... it solved the purpose...