topic Re: New to SAS - How to add up values for the same item? in New SAS User

New to SAS - How to add up values for the same item?

12we — Wed, 03 Apr 2019 18:13:09 GMT

Hello there, I'm new to SAS and was wondering if someone can help me with this simple question.

I managed to sort the data but I'm wondering how a to add up all the values for each store instead of each sales showing up like this one for example?

Store_no Sales

store1 $10

store1 $5

store2 $12

store2 $1

store2 $100

store3 $50

store4 $0

store4 $16

Thanks a bunch!

Re: New to SAS - How to add up values for the same item?

novinosrin — Wed, 03 Apr 2019 18:17:46 GMT

do you mean you want a sum(sales) i.e 194 ?

sum(sales) within group by storeno ?

Re: New to SAS - How to add up values for the same item?

12we — Wed, 03 Apr 2019 18:18:46 GMT

Hi, thanks for getting back, that was so fast!

I would like the sum of sales for each store number so that only one store number obs will show up. Something like this:

Store_no Sales

store1 $15

store2 $113

store3 $50

store4 $16

Re: New to SAS - How to add up values for the same item?

novinosrin — Wed, 03 Apr 2019 18:20:38 GMT

if you like proc sql

proc sql;

create table want as

select storeno, sum(sales) as tot_sales

from your_have

group by storeno

order by storeno;

quit;

Re: New to SAS - How to add up values for the same item?

12we — Wed, 03 Apr 2019 21:14:55 GMT

Thanks for the insight! It was the "group by" statement I was looking for. (Y)

Re: New to SAS - How to add up values for the same item?

Kurt_Bremser — Thu, 04 Apr 2019 07:52:39 GMT

As always, there are multiple ways to achieve the same result.

(example data)

data have;
input Store_no $ Sales :dollar10.;
format sales dollar10.;
datalines;
store1 $10
store1 $5
store2 $12
store2 $1
store2 $100
store3 $50
store4 $0
store4 $16
;
run;

In addition to the SQL method, there's

a data step that does it "manually":

data want1;
set have;
by store_no;
retain tot_sales;
format tot_sales dollar10.;
if first.store_no
then tot_sales = sales;
else tot_sales + sales;
if last.store_no then output;
drop sales;
run;

(needs sort by store_no)

or proc summary:

proc summary data=have nway;
class store_no;
var sales;
output
  out=want2 (drop=_type_ _freq_)
  sum(sales)=tot_sales
;
run;

Re: New to SAS - How to add up values for the same item?

ballardw — Thu, 04 Apr 2019 14:57:57 GMT

One question to ask yourself when doing summaries: Do I need a data set for further processing or is the summary a report that people will read?

If people are using the information, an indication being currency symbol, then perhaps one of the report procedures such as Proc Report or Tabulate might be in order.

proc report data=have ;
   columns Store_no sales;
   define Store_no /group;
   define sales/ analysis;
run;

If a report is in order then you may find that a simple to moderately complex layout may be easy enough to do in a single step as you may still need to do stuff to display your data set later.