07-28-2012 08:33 AM
I don't know the full answer, but it is likely related to the fact that excel formats are at the cell rather than the column level. Thus, if any cell is formatted as a character (within the number of rows your system is set for guessing rows), the variable will be set to character.
07-28-2012 09:28 AM
On import, directly, I don't think there currently is one. However, you can always correct it after the fact. e.g.:
/*create test dataset to approximate import result*/
data test (drop=age_in);
set sashelp.class (rename=(age=age_in));
if _n_ eq 1 then age=" ";
/*change character to numeric*/
data want (drop=age_in);
set test (rename=(age=age_in));
07-28-2012 12:38 PM
With de default MIXED=NO option, your numeric cells will be imported from Excel as missing values in your character column. To get a text version of the numbers in those cells, that you can convert using Art's technique, you must specify option MIXED=YES on import.
07-28-2012 06:16 PM
@PGStats: I could be wrong, but I think that with a mixed column and mixed=NO, the field might be created as character although the numbers will appear as they were entered. At least they did in the test I just ran!
07-28-2012 07:51 PM
Look at the documentation for MIXED=NO here:
it says :
07-28-2012 10:54 PM
@PGStats: Not that I have learned to distrust what the documentation says, but run the following code on the attached spreadsheet (after downloading the file to the root of your c: drive):
PROC IMPORT OUT= WORK.WANT
07-29-2012 12:36 AM
The trouble with your example is that both columns with missing values have the missing value in the first column. On my machine (SAS 9.2) this is causing mixed=NO to make the variables $1, which doesn't work to read the column as either a number or text. When I moved the value of 4822 to the first row of the DISTCD column then SAS read the column as a number when mixed=NO.
Does seem like it is not exactly doing what the manual says, but perhaps I have the wrong guessing rows setting?
07-29-2012 05:08 PM
@me.turcotte: Actually, in answer to your question, there are usually a number of ways to achieve what one needs to accomplish.
In fact, your question gives me an opportunity to promote a paper that a group of us did for SGF2012, and which I will be presenting again at the MWSUG meeting in September. The paper is called "Copy and Paste Almost Anything" and was written to give users a lot more control during an import than proc import allows for any file type. Its one catch, but which was really a feature, is that it only works with tables that one has copied to their system's clipboard.
In the case of an Excel file, that simply means bringing up Excel, highlighting all of the columns by left clicking on the letter A (at the top left of the table) and, while holding the button down, dragging the mouse across the table until all columns (that contain data) are highlighted, and then clicking on copy.
The code allows one to specify the informats and formats they want to use for every column. It has a number of other neat features, but they aren't relevant for your question,
The paper and code can be found at: http://www.sascommunity.org/wiki/Copy_and_Paste_Almost_Anything