BookmarkSubscribeRSS Feed
me_turcotte
Calcite | Level 5

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 ?

9 REPLIES 9
art297
Opal | Level 21

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.

me_turcotte
Calcite | Level 5

thank you arthur,

then whats the solution using sas?

art297
Opal | Level 21

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;

PGStats
Opal | Level 21

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
art297
Opal | Level 21

@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
Opal | Level 21

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
art297
Opal | Level 21

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

Tom
Super User Tom
Super User

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?

art297
Opal | Level 21

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2523 views
  • 0 likes
  • 4 in conversation