topic Re: Read in from Excel and transpose in SAS Programming

Read in from Excel and transpose

apolitical — Fri, 02 Mar 2018 16:05:53 GMT

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

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.

Re: Read in from Excel and transpose

apolitical — Fri, 02 Mar 2018 16:08:26 GMT

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).

Re: Read in from Excel and transpose

Reeza — Fri, 02 Mar 2018 16:14:02 GMT

I think you need to provide better sample data in this case.

Re: Read in from Excel and transpose

apolitical — Mon, 05 Mar 2018 14:54:58 GMT

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.