topic Re: Help in reading the complex excel file and then Transpose in SAS Procedures

Help in reading the complex excel file and then Transpose

pp2014 — Tue, 30 Sep 2014 22:49:47 GMT

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/2010

MAT ~09/2011

MAT ~09/2012

ABC.GROWTH

XYZ.PRODUC

ABC.GROWTH

XYZ.PRODUC

Growth~09/2010

Growth~09/2011

Local

Pharmacy

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

Re: Help in reading the complex excel file and then Transpose

pp2014 — Tue, 30 Sep 2014 23:10:54 GMT

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

Local

Pharmacy

200909

81913057250.00

12863795856.00

Local

Pharmacy

201009

350702456608.00

260434737707.00

18357058601.00

936889621.00

Local

Pharmacy

201109

338338364318.00

249355601999.00

-3.53

Local

Pharmacy

201209

-16.55

Re: Help in reading the complex excel file and then Transpose

Reeza — Tue, 30 Sep 2014 23:17:30 GMT

It may be best to attach a sample excel file.

Re: Help in reading the complex excel file and then Transpose

pp2014 — Tue, 30 Sep 2014 23:21:02 GMT

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

Re: Help in reading the complex excel file and then Transpose

Tom — Tue, 30 Sep 2014 23:30:29 GMT

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

...

Re: Help in reading the complex excel file and then Transpose

pp2014 — Tue, 30 Sep 2014 23:39:33 GMT

Thanks Tom.

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

Re: Help in reading the complex excel file and then Transpose

Tom — Wed, 01 Oct 2014 00:02:20 GMT

Doesn't really change the prescription for the algorithm to use to parse it.

You could try using the RANGE= statement in the PROC IMPORT code to limit the input to specific parts of the sheet.

Or if you can get them to make named ranges in the worksheet then your can reference those in the RANGE= statement.

Re: Help in reading the complex excel file and then Transpose

PGStats — Wed, 01 Oct 2014 02:20:11 GMT

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

Re: Help in reading the complex excel file and then Transpose

Reeza — Wed, 01 Oct 2014 03:33:10 GMT

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

Re: Help in reading the complex excel file and then Transpose

lloydc — Wed, 01 Oct 2014 03:59:24 GMT

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.

Re: Help in reading the complex excel file and then Transpose

pp2014 — Wed, 01 Oct 2014 12:20:48 GMT

Re: Help in reading the complex excel file and then Transpose

pp2014 — Wed, 01 Oct 2014 12:59:20 GMT

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.

Re: Help in reading the complex excel file and then Transpose

pp2014 — Wed, 01 Oct 2014 13:50:29 GMT

Any help???

Re: Help in reading the complex excel file and then Transpose

RW9 — Wed, 01 Oct 2014 14:31:40 GMT

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?

Re: Help in reading the complex excel file and then Transpose

pp2014 — Wed, 01 Oct 2014 14:41:13 GMT

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

Re: Help in reading the complex excel file and then Transpose

RW9 — Wed, 01 Oct 2014 15:36:50 GMT

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.

Re: Help in reading the complex excel file and then Transpose

Tom — Thu, 02 Oct 2014 10:16:59 GMT

Try this method.

%let infile=c:\downloads\Input Data.xls;

* Read entire file and ignore column names ;

proc import datafile="&infile" out=have replace dbms=xls ;

getnames=no;

run;

* Add ROW numbers ;

data have ;

row+1;

set have ;

run;

* Generate Y_MONTH and variable names from first three rows ;

proc transpose data=have(obs=3 drop=a b c d row) prefix=name out=names(drop=_label_) ;

var _all_;

run;

data names;

length _newname_ $32 Y_month $6 ;

set names ;

if name2 = 'US' then name2=' ';

if name3 = 'US' then name3=' ';

y_month=scan(name1,-1,' ~/')||scan(name1,-2,' ~/') ;

name1=scan(name1,1,' ~');

_newname_=translate(trim(coalescec(of name3-name1)),'____',' .~/');

run;

* Transpose the data lines ;

proc transpose data=have(firstobs=6 drop=a b c d) out=values(drop=_label_ where=(_name_ ne 'row'));

by row;

var _all_;

run;

* Combine with names, convert to numbers ;

proc sql noprint ;

create table values2 as

select a.row

, a._name_

, input(col1,comma32.) as col1

, b._newname_,b.y_month

from values a , names b

where a._name_ = b._name_

order by a.row,b.y_month

;

run;

* To force column order add extra row with names in order and missing ROW and Y_MONTH ;

data values3 ;

set names (drop=Y_month) values2;

run;

* Transpose ;

proc transpose data=values3 out=want (drop=_name_ where=(row ne .)) let;

by row y_month ;

id _newname_;

var col1 ;

run;

* Add back the first 4 columns ;

data want ;

merge have(firstobs=6 keep=row a b c d) want ;

by row ;

rename a=M_Type b=M_TYPE_DESC c=P_H_Desc d=MARKET_Type_Ind ;

run;

proc print; run;

Re: Help in reading the complex excel file and then Transpose

pp2014 — Thu, 02 Oct 2014 12:47:48 GMT

Thanks everybody for help...