06-14-2012 09:43 AM
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
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.
the first 5 obs. of variable DS Code in Excel file 2
when I imported to SAS, it seems ok and the type of this variable is character with format $6.
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.
06-14-2012 10:34 AM
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.
06-14-2012 12:59 PM
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
06-20-2012 08:22 PM
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.