PROC IMPORT dbms= EXCEL force all imported variables to character

[pkopersk]

Hello,

I am importing several excel files and I am having trouble with the format specification of each column. Namely the imported columns are sometimes set as numeric variables when they should be characters. This results in rows containing a letter to be set to blank. 

I would like to force all imported columns to be directly set to character.  I have tried this solution:  https://communities.sas.com/t5/SAS-Procedures/PROC-IMPORT-EXPORT-forcing-column-to-be-character-or-n... without success. The columns are still numeric. 

Here's a synthetic example:

proc import out=WANT
datafile="path\file.xls"
dbms=EXCEL replace;
sheet="Sheet1";
mixed=yes;

DBDSOPTS= "DBTYPE=(var1='CHAR(24)')";
run;

But the var1 column is still set as numeric

[Tom]

Are your column headers all text?

Try GETNAMES=NO so that the headers will force the column to be character and then delete the first row.

You can use the values from the first row to generate a RENAME statement if you want the real names.

[pkopersk]

Thank you for your reply. This solution doesnt seem to work. All columns have character headers but when using the option GETNAMES=NO, some of them get converted to "." which is annoying. For the moment I am saving each sheet in a separate .csv and using

dbms=CSV replace;
delimiter=';';
guessingrows=50000;

[Tom]

I would try using DBMS=XLSX, if you can, to get Excel/Microsoft code out of the loop.

[Kurt_Bremser]

Do what most of us do: save the data to a csv file and read that with a data step, so you have total control over all attributes.

proc import with Excel is just a more tedious (and less funny) form of throwing dice.

[pkopersk]

How would I do that?

I tried this:

data WANT;
infile "path\file.csv"
delimiter= ';';
run;

But the imported dataset is empty

[ballardw]

---

@pkopersk wrote:
How would I do that?

I tried this:

data WANT;
infile "path\file.csv"
delimiter= ';';
run;

But the imported dataset is empty

---

Use proc import with a csv, use a: guessingrows=max; statement.

The log will have data step code generated by proc import to read the data.

Copy the code from log and paste into the editor. Clean up by removing line numbers if such appear.

Change the INFORMAT statements for the numeric variables, likely something like INFORMAT VARIABLE 32.;

to a character informat like : INFORMAT VARIABLE $15. ; use a number large enough to read the longest expected sequence of digits. You can remove any of the FORMAT statements for these or other variables with a $w. informat.

You should leave anything related to dates, times or date times alone as you will find in the long run that the SAS date values are much easier to manipulate or examine than strings. These would likely have informats that look like MMDDYY10. DATE9. ANYDTDTE40. TIME8. or similar depending on the layout of the variable.

SAVE the code, especially if you will have other similar files read. Then you only need to change the infile statement and the output data set name.