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.
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.
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.
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!
Yes, you can do that. Also if you want it for filter then you can use HAVING clause instead of WHERE
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?
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.
The SUM() function in PROC SQL is applied to GROUPED variables.
Your requirement is not clear enough. What do you mean by 002SALEALL? Are you missing something. Please provide your expected result.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.