Help using Base SAS procedures

Why does SAS consider variables as character type when they are numeric ?

Reply
Occasional Contributor
Posts: 5

Why does SAS consider variables as character type when they are numeric ?

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 ?

PROC Star
Posts: 7,467

Re: Why does SAS consider variables as character type when they are numeric ?

Posted in reply to me_turcotte

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.

Occasional Contributor
Posts: 5

Re: Why does SAS consider variables as character type when they are numeric ?

thank you arthur,

then whats the solution using sas?

PROC Star
Posts: 7,467

Re: Why does SAS consider variables as character type when they are numeric ?

Posted in reply to me_turcotte

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;

Respected Advisor
Posts: 4,919

Re: Why does SAS consider variables as character type when they are numeric ?

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

PG
PROC Star
Posts: 7,467

Re: Why does SAS consider variables as character type when they are numeric ?

@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!

Respected Advisor
Posts: 4,919

Re: Why does SAS consider variables as character type when they are numeric ?

Look at the documentation for MIXED=NO here:

http://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#n0msy4hy1so0ren1ac...

it says :

NO assigns numeric or character type for the column, depending on the majority of the type data that is found.                       

Note: Numeric data in a character column and character data in a numeric column are imported as missing values.

PG

PG
PROC Star
Posts: 7,467

Re: Why does SAS consider variables as character type when they are numeric ?

@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;

Attachment
Super User
Super User
Posts: 7,039

Re: Why does SAS consider variables as character type when they are numeric ?

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?

PROC Star
Posts: 7,467

Re: Why does SAS consider variables as character type when they are numeric ?

Posted in reply to me_turcotte

@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

Ask a Question
Discussion stats
  • 9 replies
  • 379 views
  • 0 likes
  • 4 in conversation