BookmarkSubscribeRSS Feed
Jack1
Obsidian | Level 7

Hello,

I am reading monthly sales data from a SAS dataset called RAW but I need to break it out into its 7 components for that given month.  How would I modify my PROC SQL code to allow me to break this out so I can generate the the monthly sales revenue for products 'a' thru 'g' in a given month?  For simplicity assume the shares are equal at 14.3% and I want to hard code the revenue shares into the coding.  How would I modify the SQL language below to accomplish this?  

 

proc sql;
create table other_revenue as
select Date as yyyymm, OtherRevenue as revenue format comma9. 
from raw;
alter table other_revenue 
add product char (2) ;
insert into other_revenue set product='a'; * there are 6 more products (b,c,d,e,f,g);
quit;

Thanks

Jack

5 REPLIES 5
Kurt_Bremser
Super User

Hi,

 

could you please supply some example data against which we can run your SQL step to see what is intended?

And some example how the data should look like after the transformation?

The way I see it, you add 7 additional observations where "product" contains the letters "a" through "g", while in all existing observations coming from raw "product" remains missing.

Jack1
Obsidian | Level 7

Sure...here is what I am trying to do.

 

Go from something like this:

 

yyyymmRevenue
2016061,500
2016071,758
2016083,255

 

To this:

 

yyyymmRevenueProduct shr_ashr_bshr_cshr_dshr_eshr_fshr_g
20160675a 0.050.070.250.070.30.070.19
20160788a        
201608163a        
201606105b        
201607123b        
201608228b        
201606375c        
201607440c        
201608814c        
201606105d        
201607123d        
201608228d        
201606450e        
201607527e        
201608977e        
201606105f        
201607123f        
201608228f        
201606285g        
201607334g        
201608618g        

 

Alternatively would it be easier to do this as a SAS data step?

 

Thanks

Jack

Kurt_Bremser
Super User

I guess the share values can be calculated at the start or for each month?

I'd recommend creating a table holding the product names and share quotes, and then do a cartesian join with SQL.

Kurt_Bremser
Super User

Example code for my previous suggestion:

data shares;
input yyyymm $ product $ shr;
cards;
201606 a 0.05
201606 b 0.07
201606 c 0.25
201606 d 0.07
201606 e 0.3
201606 f 0.07
201606 g 0.19
;
run;

data revenue;
input yyyymm $ revenue;
cards;
201606 1500
;
run;

proc sql;
create table want as
select a.yyyymm, (a.revenue * b.shr) as revenue, b.product
from revenue as a, shares as b
where a.yyyymm = b.yyyymm
;
quit;

If you get your shares in wide rather than long format, a proc transpose will be needed.

Jack1
Obsidian | Level 7

Great, thanks!

 

Jack

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
  • 5 replies
  • 1932 views
  • 0 likes
  • 2 in conversation