Help using Base SAS procedures

Help in reading the complex excel file and then Transpose

Reply
Frequent Contributor
Posts: 127

Help in reading the complex excel file and then Transpose

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_TypeM_TYPE_DESCP_H_DescMARKET_Type_IndMAT ~ 09/2010MAT ~ 09/2011YTD ~ 09/2010YTD ~ 09/2011MAT ~ 09/2009MAT ~ 09/2009MAT ~ 09/2010MAT ~ 09/2010MAT   ~09/2011MAT   ~09/2012
USUSUSUSABC.GROWTHXYZ.PRODUCABC.GROWTHXYZ.PRODUCUSUS
USUSUSUSGrowth~09/2010Growth~09/2011
LLocalPharmacyE350702456608.00338338364318.00260434737707.00249355601999.0081913057250.0012863795856.0018357058601.00936889621.00-3.53-16.55

Output is as follows:

M_TypeM_TYPE_DESCP_H_DescMARKET_Type_IndY_mnthMATYTDABC.GROWTHXYZ.PRODUCGrowth~09/2010Growth~09/2011
LLocalPharmacyE201009350702456608.00260434737707.0081913057250.0012863795856.00
LLocalPharmacyE201109338338364318.00249355601999.0018357058601.00936889621.00-3.53
LLocalPharmacyE201209 -16.55
Frequent Contributor
Posts: 127

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

I am sorry the output should be as follows:

 

M_TypeM_TYPE_DESCP_H_DescMARKET_Type_IndY_mnthMATYTDABC.GROWTHXYZ.PRODUCGrowth~09/2010Growth~09/2011
LLocalPharmacyE200909 81913057250.0012863795856.00
LLocalPharmacyE201009350702456608.00260434737707.0018357058601.00936889621.00
LLocalPharmacyE201109338338364318.00249355601999.00
-3.53
LLocalPharmacyE201209 -16.55
Super User
Posts: 19,877

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

It may be best to attach a sample excel file.

Frequent Contributor
Posts: 127

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

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

Respected Advisor
Posts: 4,935

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

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

PG
Frequent Contributor
Posts: 127

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

Attachment
Frequent Contributor
Posts: 127

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

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.

Super User
Posts: 19,877

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

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

Contributor
Posts: 22

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

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.

Frequent Contributor
Posts: 127

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

Any help???

Super User
Super User
Posts: 7,997

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

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?

Frequent Contributor
Posts: 127

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

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

Super User
Super User
Posts: 7,997

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

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.

Super User
Super User
Posts: 7,079

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

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

...

Frequent Contributor
Posts: 127

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

Thanks Tom.

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

Ask a Question
Discussion stats
  • 17 replies
  • 373 views
  • 0 likes
  • 6 in conversation