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
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.
Sure...here is what I am trying to do.
Go from something like this:
yyyymm | Revenue |
201606 | 1,500 |
201607 | 1,758 |
201608 | 3,255 |
To this:
yyyymm | Revenue | Product | shr_a | shr_b | shr_c | shr_d | shr_e | shr_f | shr_g | |
201606 | 75 | a | 0.05 | 0.07 | 0.25 | 0.07 | 0.3 | 0.07 | 0.19 | |
201607 | 88 | a | ||||||||
201608 | 163 | a | ||||||||
201606 | 105 | b | ||||||||
201607 | 123 | b | ||||||||
201608 | 228 | b | ||||||||
201606 | 375 | c | ||||||||
201607 | 440 | c | ||||||||
201608 | 814 | c | ||||||||
201606 | 105 | d | ||||||||
201607 | 123 | d | ||||||||
201608 | 228 | d | ||||||||
201606 | 450 | e | ||||||||
201607 | 527 | e | ||||||||
201608 | 977 | e | ||||||||
201606 | 105 | f | ||||||||
201607 | 123 | f | ||||||||
201608 | 228 | f | ||||||||
201606 | 285 | g | ||||||||
201607 | 334 | g | ||||||||
201608 | 618 | g |
Alternatively would it be easier to do this as a SAS data step?
Thanks
Jack
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.
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.
Great, thanks!
Jack
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.