BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yanshuai
Quartz | Level 8

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
HB
Barite | Level 11 HB
Barite | Level 11

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

7 REPLIES 7
HB
Barite | Level 11 HB
Barite | Level 11

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. 

yanshuai
Quartz | Level 8

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!

SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran
yanshuai
Quartz | Level 8

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?

HB
Barite | Level 11 HB
Barite | Level 11

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. 




SuryaKiran
Meteorite | Level 14

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

 

Thanks,
Suryakiran
SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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