topic Re: How to sum obs according to many tables in SAS Programming

How to sum obs according to many tables

yanshuai — Wed, 07 Mar 2018 00:22:47 GMT

Hello all,

I have a database, it has content like this

data temp.database ;
input
STORE YEAR SALE ID;
datalines;
001 2001 12 1212
001 2002 12 1212
001 2003 12 1212
002 2001 12 1212
002 2002 12 1212
002 2003 12 1212
003 2001 12 1216
003 2002 12 1216
003 2003 12 1216
004 2001 12 1216
004 2002 12 1216
004 2003 12 1216
005 2001 12 1216
005 2002 12 1216
005 2003 12 1216
006 2001 12 1212
006 2002 12 1212
006 2003 12 1212
007 2001 12 1212
007 2002 12 1212
007 2003 12 1212
008 2001 12 1219
008 2002 12 1219
008 2003 12 1219
009 2001 12 1219
009 2002 12 1219
009 2003 12 1219
010 2001 12 1219
010 2002 12 1219
010 2003 12 1219;
run;

Firrst, I am not looking for all the STORE in the database, I only care about the STORE I am interested in, the interested STORE list is like this

data maps.Storelist ;
input
STORE;
datalines;
002
005
008
;
run;

Firrst, I want to SUM all the SALE (let's call it SUMSALE) under the same ID and same YEAR.

Then, I want the SALE of each STORE of each YEAR in the Storelist. (This is directly given in the Database)

Then SALE/SUMSALE as SHARE.

My code is like this,

proc sql;
create table want as select
Database.STORE,
Database.YEAR,
Database.SALE,
Database.ID,
sum(Database.SALE) as SUMSALE,
Database.SALE / SUMSALE as SHARE  /* This is the part I am not clear. Can I directly use the new variable?*/
from temp.database,maps.Storelist
where Storelist.ID=Database.ID 
group by Database.SALE, Database.ID 
order by Database.STORE,Database.YEAR; 
quit;

But SAS says the SHARE variable is not found in the contributing tables.

Can I directly use the variable I am creating now in the next step calculation?

Would this be solved by this?

Database.SALE /sum(Database.SALE)as SHARE

Thank you all.

Best wish.

Re: How to sum obs according to many tables

HB — Tue, 06 Mar 2018 19:26:23 GMT

Not exactly sure what you want.

But with your data,

proc sql;
create table sum_of_st_sales_by_st_n_year as 
select a.year, a.store, sum(a.sale) as annual_st_sales, b.totalsales, sum(a.sale)/b.totalsales as sales_percent  
from mydatabase a
inner join (select year, sum(sale) as totalsales from mydatabase group by year) b on a.year = b.year
group by a.year, store
order by a.year, store;
run;

creates a table that has total store sales and a percentage of the total sales for a store in a year(although the data is rather boring and it is hard to tell). You can subset this for the stores and years in which you are interested.

Re: How to sum obs according to many tables

SuryaKiran — Tue, 06 Mar 2018 20:07:47 GMT

Your requirement is not clear enough. What do you mean by 002SALEALL? Are you missing something. Please provide your expected result.

Re: How to sum obs according to many tables

yanshuai — Wed, 07 Mar 2018 00:27:58 GMT

Hello,

Thank you for the answer.

I have figured out what I need using SQL without JOIN. I have changed my question so it is easier to read.

So my question is

Can I directly use the new variable I am creating in SQL to calculate another variable?

Like this

proc sql;

create table want as select

A,B,C

SUM(A) as ATOT

ATOT / B as SHARE

group by C

When I do this, SAS says the ATOT is not found in the contributing tables.

Can I solve this issue by change to this?

SUM(A) as ATOT

SUM(A) / B as SHARE

Thank you very much!

Re: How to sum obs according to many tables

SuryaKiran — Wed, 07 Mar 2018 02:25:51 GMT

Yes, you can do that. Also if you want it for filter then you can use HAVING clause instead of WHERE

Re: How to sum obs according to many tables

yanshuai — Wed, 07 Mar 2018 04:28:25 GMT

There is a prroblem with this code.

proc sql;
create table want as select
Database.STORE,
Database.YEAR,
Database.SALE,
Database.ID,
sum(Database.SALE) as SUMSALE,
Database.SALE / sum(Database.SALE) as SHARE  
from temp.database,maps.Storelist
where Storelist.ID=Database.ID 
group by Database.SALE, Database.ID 
order by Database.STORE,Database.YEAR; 
quit;

The SUMSALE only sum the SALE for STORE 002 005 008? Do they?

Re: How to sum obs according to many tables

HB — Wed, 07 Mar 2018 15:34:09 GMT

Still don't know what you want or what you are doing.

Just guessing a this point that instead of

from temp.database,maps.Storelist
where Storelist.ID=Database.ID

you may want

from temp.database maps.Storelist inner join Storelist.ID=Database.ID
where database.store in (002,005,008)

In the IN clause you may need 2, '2', '002', or something. Don't know how your data is stored.

Re: How to sum obs according to many tables

SuryaKiran — Wed, 07 Mar 2018 15:36:27 GMT

The SUM() function in PROC SQL is applied to GROUPED variables.