BookmarkSubscribeRSS Feed
pp2014
Fluorite | Level 6

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
17 REPLIES 17
pp2014
Fluorite | Level 6

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
Reeza
Super User

It may be best to attach a sample excel file.

pp2014
Fluorite | Level 6

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

PGStats
Opal | Level 21

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

PG
pp2014
Fluorite | Level 6

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
Super User

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

lloydc
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User Tom
Super User

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
Fluorite | Level 6

Thanks Tom.

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 1463 views
  • 0 likes
  • 6 in conversation