BookmarkSubscribeRSS Feed
Data_geekz
Calcite | Level 5

I have an excel sheet where a certain column has a Percentage format. However, when imported to VA, the data returns as Character (all other data values from the excel sheet remains like date except for this one).

The funny thing, I have been using, updating and importing  the same excel sheet for 3 months in my VA weekly report and had this trouble started only two days ago.

I used a different excel file and when imported to VA identifies a numeric column in excel as category in VA.

 

Tried the following but nothing worked:

1. Clear cache, use other browser

2. Copy the values  to a new Excel sheet and save/import as CSV 

 

Help!

2 REPLIES 2
ballardw
Super User

Look for either an extra header row, which would treat a second row of header text as data and would be character or look for another row with some character other than digits, decimals and % sign. Maybe a dash or something used to indicate "no value" other than blank. Or you'll see the Excel indicator of "text", the little green triangle currently that appears in one corner of the cell. That indicates someone typed an ' before the value and is now text.

 

Save as to CSV is a good thought but did you look at the TEXT resulting? As in with a text viewer like Notepad? Typically a bit hard to read but should see likely see something in the first 20 or so rows of data. Look for quotes around the percent field, possibly with one or more leading spaces inside the quotes if no other odd characters.

 

Import + Excel = guessing game.

Save Excel to CSV and read with a data step will control variable types, names and properties and likely through an invalid data message or more when something odd happens to the Excel source.

 

One of my favorite reading Excel files was more interesting as part way through the file columns that had been dates started appearing as currency (none of the SAS date informat like currency values), the income column started appearing as dates (the Comma informat typically used to read currency doesn't like yyyy-mm-dd values).

For that particular issue since the cell properties were the problem the solution was highlight the entire column and set the value type as all dates or all currency ( and a similar fix had to be done to a couple of Id number columns that were partially currency). And this was from a source that was computer generating the files (with a long history of stupidity). Human entered values are worse.

Data_geekz
Calcite | Level 5

@ballardw - thank you for your response. My sheet does not contain an extra header. No sign of a single value in that particular column as Text (no green triangle) nor any blank space. I can do pivot on my excel sheet and perform calculations for that column with no errors too. 

So I tried uploading again the same excel sheet today with the same steps I've been doing for the past months, lo and behold, no more errors. 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 432 views
  • 0 likes
  • 2 in conversation