PROC IMPORT/EXPORT -- forcing column to be character or numeric

[PaigeMiller]

Yes I have SAS/ACCESS Interface to PC Files

[data_null__ · Accepted solution]

Editor's note: this is a popular topic, so we've consolidated several of the most helpful answers in a single reply.

This was posted on SAS-L from @snoopy369.  Does it help address your problem? 

With the DBMS=EXCEL or EXCELCS options, you can force SAS to read them as text using the DBDSOPTS option.  It's something like this:

proc import file="whatever.xls" data=whatever dbms=excel replace;
DBDSOPTS= "DBTYPE=(var1='NUM(8)' var2='CHAR(3)')";
run;

I don't tend to remember the exact combination of ( and = and " for this, so the above is probably slightly off, but you can google it and get the right combination, or when I get into work i'll look at some code I have that does it properly.

Another approach from@Vimal_Kurup:

The one way i am handling this is by setting GETNAMES=NO and STARTROW=1. Since the column names are obviously characters, SAS would import all the data as character attributes. You can later remove the first obs and use the remainder of dataset.

From @Haikuo, an approach that doesn't use PROC IMPORT:

In EG though, you can manually change attributes of each variable when doing import or export.  

On a side note, here is good SUGI paper on dealing with the Excel files using SAS:

http://www2.sas.com/proceedings/sugi31/020-31.pdf

---

[kleinhev]

The correct syntax for this is:

DBDSOPTS= "DBTYPE=(var1='NUM(8)' var2='CHAR(3)')";

Note the blanks between variable definitions and the different quotation marks.

[Ravikumarkummari]

proc import file="whatever.xls" data=whatever dbms=excel replace;

dbdsopts=(dbsastype("var1='NUMERIC(8)' var2='CHAR(3)'));

run;

Is this code works in SAS 9.3?

[Vimal_Kurup]

The one way i am handling this is by setting GETNAMES=NO and STARTROW=1. Since the column names are obviously characters, SAS would import all the data as character attributes. You can later remove the first obs and use the remainder of dataset.

Hope this helps!

[Ksharp]

There is little choice to change the variable's type for proc import .

But if you can, you can change it after importing it into SAS .

Ksharp

[PaigeMiller]

There is little choice to change the variable's type for proc import .
But if you can, you can change it after importing it into SAS .

Thanks for not trying to redefine my problem. I am disappointed that there is no better solution, but this seems to be the way to go.

[ShashikanthRai]

Try the following Code: if u want "LBREFID" to be in numeric then keep as shown below, else if u want it in char then Update LBREFID $ 8 in Length and $CHAR8. in input .

/*

    The file will probably have DOS line-endings, so specify the delimiter as both the comma and the carriage return. The way the do this is to write them in hexadecimal, as "DLM='2C0D'x" on the infile statement.

    Specify infile option "dsd" so that consequtive commas are recognized as missing values.

    Specify infile option "missover" so that trailing missing values on a line are recognized as such (Excel doesn't output consequtive commas for trailling missing values).

    Specify infile option "lrecl" long enough for the longest record (it defaults to 256).

    Specify infile option "firstobs=2" so that the line of column names is skipped.

    */

Data AAA;

LENGTH

        STUDY            $ 8

        PT                 6

        LBREFID           8

         ;

infile "<<Path>>\Lab_data.csv" dlm='2C0D'x dsd missover lrecl=10000 firstobs=2

;

    INPUT

        STUDY            : $CHAR8.

        PT               : ?? BEST6.

        LBREFID          : ?? BEST8.

        ;

        run;