DATA Step, Macro, Functions and more

Read in from Excel and transpose

Reply
Frequent Contributor
Posts: 102

Read in from Excel and transpose

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.

 

 Year2012
 Month89101112
ProductNet Sales UnitsNet Sales $Product Margin $Net Sales UnitsNet Sales $Product Margin $Net Sales UnitsNet Sales $Product Margin $Net Sales UnitsNet Sales $Product Margin $Net Sales UnitsNet 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.

Frequent Contributor
Posts: 102

Re: Read in from Excel and transpose

Posted in reply to apolitical

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

Super User
Posts: 23,928

Re: Read in from Excel and transpose

Posted in reply to apolitical

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

Frequent Contributor
Posts: 102

Re: Read in from Excel and transpose

Posted in reply to apolitical

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. 

Ask a Question
Discussion stats
  • 3 replies
  • 99 views
  • 0 likes
  • 2 in conversation