Help in reading the complex excel file and then Transpose

[pp2014]

I need  some help in reading the complex Excel file and  then once I have read it, I want to transpose the data.

Note:   There are 2 blank lines before the data starts.

Any help will be highly appreciated.

Below is the sample Input:

M_Type

M_TYPE_DESC

P_H_Desc

MARKET_Type_Ind

MAT ~ 09/2010

MAT ~ 09/2011

YTD ~ 09/2010

YTD ~ 09/2011

MAT ~ 09/2009

MAT ~ 09/2009

MAT ~ 09/2010

MAT ~ 09/2010

MAT   ~09/2011

MAT   ~09/2012

US

US

US

US

ABC.GROWTH

XYZ.PRODUC

ABC.GROWTH

XYZ.PRODUC

US

US

US

US

US

US

Growth~09/2010

Growth~09/2011

L

Local

Pharmacy

E

350702456608.00

338338364318.00

260434737707.00

249355601999.00

81913057250.00

12863795856.00

18357058601.00

936889621.00

-3.53

-16.55

Output is as follows:

M_Type	M_TYPE_DESC	P_H_Desc	MARKET_Type_Ind	Y_mnth	MAT	YTD	ABC.GROWTH	XYZ.PRODUC	Growth~09/2010	Growth~09/2011
L	Local	Pharmacy	E	201009	350702456608.00	260434737707.00	81913057250.00	12863795856.00
L	Local	Pharmacy	E	201109	338338364318.00	249355601999.00	18357058601.00	936889621.00	-3.53
L	Local	Pharmacy	E	201209						-16.55

[pp2014]

I am sorry the output should be as follows:

M_Type	M_TYPE_DESC	P_H_Desc	MARKET_Type_Ind	Y_mnth	MAT	YTD	ABC.GROWTH	XYZ.PRODUC	Growth~09/2010	Growth~09/2011
L	Local	Pharmacy	E	200909			81913057250.00	12863795856.00
L	Local	Pharmacy	E	201009	350702456608.00	260434737707.00	18357058601.00	936889621.00
L	Local	Pharmacy	E	201109	338338364318.00	249355601999.00			-3.53
L	Local	Pharmacy	E	201209						-16.55

[Reeza]

It may be best to attach a sample excel file.

[pp2014]

Thanks Reeza.  I tried attaching the file but I got error.

[PGStats]

Having that Excel file would be very useful. Try Zipping the file before attaching.

[pp2014]

Hi PGStats, I have attached the zipped file with Input file and How output file that shows how output should look like.. I have used following to import the file PROC IMPORT OUT=tempdata datafile="c\temp\Input Data.xls" dbms=EXCEL  REPLACE;   Sheet="Sheet1";  mixed=yes;   getnames=yes; RUN; I have to use mixed = yes option since the columns have character and numeric values. Because of that certain numeric values like 1.04575E+11 don't get converted to numeric. Thanks for any help.

[Reeza]

For some reason it won't accept .xlsx files, try resaving and attaching as .xls file.

[lloydc]

I may be misinterpreting your problem but without your SAS code I am just going to ask: have you tried PROC IMPORT with GETNAMES=YES and DATAROW=3?

Last week I had to bring in a csv file where the first six records have stuff I can't use/don't need. I wound up reading it with FIRSTOBS=7 and output to a temporary file, then PROC IMPORT worked with it.

[pp2014]

Any help???

[RW9]

Tom has provided the best solution, convert the Excel file to CSV -> SaveAs from the Excel file you have to a local area, then you have the full range of import functionality through file import in a datastep.  Proc export is at best a simple wrapper for infile processing.

Otherwise, not sure what your question is, is it in relation to importing the file, or manipulating the result?

[pp2014]

Thanks RW9. I can transpose all the other  fields except MAT ~ 09/2009 MAT ~ 09/2009 MAT ~ 09/2010 MAT ~ 09/2010 MAT~09/2011 MAT~09/2012                               ABC.GROWTH XYZ.PRODUC    ABC.GROWTH XYZ.PRODUC Growth~09/2010 Growth~09/2011 How do I transpose the above fields?  You can look at my attached output file. Thanks for any help

[RW9]

Use an array and output:

data want;

     set have;

     array col{20} mat092009 mat0920xx...;

     do I=1 to 20;

          x=col{1}; output;

     end;

run;

Can't do a more definite answer at the moment.

[Tom]

1) Does it have to be an Excel file? Could you have it converted to CSV or some other more portable format first?

2) Read the data block separate from the header rows.  Then you can transpose the data block.  Transpose the header rows. Split the labels into variables.  Merge them back and then transpose into your structure of choice.

vertical_data

VAR1|VAR2|VAR3|VAR4|_NAME_|COL1

L|Local|Pharmacy|E|VAR5|350702456608.00

L|Local|Pharmacy|E|VAR6|338338364318.00

vertical_headers

_NAME_|COL1

VAR5|MAT ~ 09/2010 US

VAR6|MAT ~ 09/2011 US

VAR7|YTD ~ 09/2010 US

VAR8|YTD ~ 09/2011 US

VAR9|MAT ~ 09/2009 ABC.GROWTH US

...

[pp2014]

Thanks Tom.

Data needs to be  in excel format.  It cannot be changed.