BookmarkSubscribeRSS Feed
comeon2012
Fluorite | Level 6

harHi community,

I am at the beginning stage of learning SAS and I have a problem when I import data from Excel to SAS.

the first 5 obs. of variable DS Code in Excel file 1

DS Code
321767
982616
27734K
13811D
321768

when I imported to SAS, problem arose and obs. mixed by number and letter are missing like below and the type of this variable is numeric with format BEST12.

DS Code
321767
982616
            .
            .
321768

the first 5 obs. of variable DS Code in Excel file 2

DS Code
53546D
53677Q
27501K
932726
945857

when I imported to SAS, it seems ok and the type of this variable is character with format $6.

DS Code
53546D
53677Q
27501K
932726
945857

My question is that:

Can I avoid the missing values and unify the type (It should be character in my case) when I import data from Excel using Import Wizard no matter the first obs is only numbers or mixed with letter? I need to combine the two SAS files together so the variable type should be consistent. Thanks.

7 REPLIES 7
ChrisSelley
Calcite | Level 5

You need to use the option MIXED=YES in the import

ballardw
Super User

SAS will look at the first 20 rows of data and determine the data type by the majority of values for each column by default when importing from XLS.


Haikuo
Onyx | Level 15

And if 20 row is not sufficient to include both character or number, then increase it:

GUESSINGROWS=

Haikuo

MikeZdeb
Rhodochrosite | Level 12

hi ... if it's an XLS file, GUESSINGROWS is not an option ... only works with delimited files (e.g. tabs, commas)

even if the value is changed in the SAS Registry, it has no effect if you start with an XLS file

Haikuo
Onyx | Level 15

Good to know that. Since it worked for CSV file for me, I took it for granted it also works for xls. Thanks, Mike.

Haikuo

p12937
Obsidian | Level 7

we had this problem and changing the column format in excel from Generic to Text resolved it.

comeon2012
Fluorite | Level 6

Thanks p12937

A friend also advised me to insert a row under the variable title row in Excel/CSV. If you want to keep a variable as character, just put a character value such as "blank" in the inserted row under that variable name(i.e. the first obs.). While if you want to keep a variable as numberic, just put a  numeric value like "1234" in the inserted row under that variable name(i.e. the first obs.). The type of first obs. will decide the type in SAS. When the data is imported to SAS, you can delete the inserted row.

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
  • 7 replies
  • 4960 views
  • 0 likes
  • 6 in conversation