I want to read in an Excel table like the following. The table extends to the right with more years and months under the year. The entire window is from August 2012 to September 2017.
Year | 2012 | ||||||||||||||
Month | 8 | 9 | 10 | 11 | 12 | ||||||||||
Product | Net Sales Units | Net Sales $ | Product Margin $ | Net Sales Units | Net Sales $ | Product Margin $ | Net Sales Units | Net Sales $ | Product Margin $ | Net Sales Units | Net Sales $ | Product Margin $ | Net Sales Units | Net Sales $ | Product Margin $ |
I would like to read it into SAS and transform the table into something like this:
Product Year_Month Net_Sales_Units Net_Sales$ Product_Margin$
This can be done by reading the table into SAS and performing manual manipulations. But is there a quicker way to achieve that? Thanks.
Sorry the table got screwed up a bit. It should be 3 columns (Net Sales Units, Net Sales$, Product Margin$) under each month (8,9..12).
I think you need to provide better sample data in this case.
I figured it out. Sorry for the the lack of clarity in my question. Mod please feel free to delete this post if that's consistent with forum rules. Thank you.
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.