Solved
Contributor
Posts: 25

Summing Based on condition for Year

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

Accepted Solutions
Solution
‎12-07-2011 06:40 PM
Valued Guide
Posts: 765

Re: Summing Based on condition for Year

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;

All Replies
Regular Contributor
Posts: 166

Summing Based on condition for Year

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;

PROC Star
Posts: 8,164

Summing Based on condition for Year

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;

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;

Regular Contributor
Posts: 184

Summing Based on condition for Year

Consider using a multilabel format.

Solution
‎12-07-2011 06:40 PM
Valued Guide
Posts: 765

Re: Summing Based on condition for Year

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;

Contributor
Posts: 25

Re: Summing Based on condition for Year

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

🔒 This topic is solved and locked.