Proc import returns all my data as percent

[spastranas]

I am importing an excel file, but the dataset that is returned, has all my values as percent.

so while I would expect to see a value of 266000, the dataset returns 266000000%

I attached the example excel file.

Here is the code that I am using:

PROC IMPORT

OUT= MyData

DATAFILE= "&Dir.Test.xlsx"

DBMS=XLSX REPLACE;

SHEET="ex";

GETNAMES=No;

DATAROW=7;

RUN;

 Any suggestions on how to return the values with no format?

I appreciate any help I can get!

Sandra

[Tom]

What format did you expect it to use?  The first and the fourth value in each column (VARIABLE) is a percent.  

It will work much better if you have data that is structured to be used as a dataset.

You might try reading each different type of row as a different RANGE of cells to be read.

[spastranas]

I need to transpose each row and use the correct format. I was hoping I could import all as numeric rather than percent, so that I can format it after I have transposed the data.

[ballardw]

I hope you do not have a lot of files that look like that.

You do not describe the data at all but it appears that each row should be a variable from similar appearance and range of values.

I would suggest in the spreadsheet to TRANSPOSE the data. That's a select the data and then in a different sheet or file to a Paste Special and select the Transpose before paste.

Then import the transposed version.

Because you have blank rows you'll likely get some useless variables but the values should make more "sense".

[spastranas]

Thank you. The task at hand, is to take the report as it is, and automate the transposing of the columns so that it can be consumed in tableau.
Thus, why I need to figure out how it import as number rather than percent.

[Tom]

Formats do not change the value stored.   If the value is 0.95 then it will display as 95%.  If it is 120 it will display as 12000%.

Just remove the format from the variables. You can use a FORMAT statement with no format listed after the variable list.

For example to remove formats from all numeric variables you could use:

data want;
  set have;
  format _numeric_;
run;

[ballardw]

---

@spastranas wrote:
Thank you. The task at hand, is to take the report as it is, and automate the transposing of the columns so that it can be consumed in tableau.
Thus, why I need to figure out how it import as number rather than percent.

---

Will there be more columns the next time the data is read? More rows?

The process for matching multiple rows of data into different variables is not going to be trivial and may be fragile if that source file ever changes layout, such as adding columns.

You might be ahead of the game by going to whatever creates that not-very-nice layout file and discuss whether that could create a nicer file, i.e. the actual transpose.

Some solutions that might work may require lots of maintenance if the file layout changes at all.

Relying on proc import to read data for multiple files is not a good idea as every single file is examined and variables can receive different properties. When the file has "garbage" like blank rows there becomes increasing chance of getting character values instead of numeric as expected. If the file adds columns you also run a risk of not all values on a row being treated as the same type.