I have an excel data bank and I don't understand why some of my variables are consider character by SAS when they are numeric ?
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.
thank you arthur,
then whats the solution using sas?
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));
age=put(age_in,2.);
if _n_ eq 1 then age=" ";
run;
/*change character to numeric*/
data want (drop=age_in);
set test (rename=(age=age_in));
age=input(age_in,best12.);
run;
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.
PG
@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!
@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
DATAFILE= "C:\SAMPLE.xls"
DBMS=EXCEL REPLACE;
RANGE="SAMPLE$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Art -
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?
@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
Art
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.