DATA Step, Macro, Functions and more

How to sum obs according to many tables

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

How to sum obs according to many tables

[ Edited ]

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.

 

 


Accepted Solutions
Solution
‎03-06-2018 11:35 PM
Super Contributor
Super Contributor
Posts: 266

Re: How to sum obs according to many tables

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. 

View solution in original post


All Replies
Solution
‎03-06-2018 11:35 PM
Super Contributor
Super Contributor
Posts: 266

Re: How to sum obs according to many tables

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. 

Contributor
Posts: 50

Re: How to sum obs according to many tables

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!

Valued Guide
Posts: 556

Re: How to sum obs according to many tables

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

Thanks,
Suryakiran
Contributor
Posts: 50

Re: How to sum obs according to many tables

Posted in reply to SuryaKiran

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?

Super Contributor
Super Contributor
Posts: 266

Re: How to sum obs according to many tables

[ Edited ]

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. 




Valued Guide
Posts: 556

Re: How to sum obs according to many tables

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

 

Thanks,
Suryakiran
Valued Guide
Posts: 556

Re: How to sum obs according to many tables

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

Thanks,
Suryakiran
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 137 views
  • 0 likes
  • 3 in conversation