topic Re: Reading in all numeric variables as character variables from .csv files in SAS Programming

Reading in all numeric variables as character variables from .csv files

Jordana — Tue, 11 Feb 2014 21:51:28 GMT

Our analytics group is attempting to work with data files in .csv format with variable type inconsistencies. In some cases the variables from the source files are character (this is correct) in other files the same variables with the same names are numeric and causing errors on import and data manipulation. Is there some way to format all input variables in a list and covert them to character formats if they happen to exist as numeric fields? If so what would the syntax look like?

Re: Reading in all numeric variables as character variables from .csv files

Reeza — Tue, 11 Feb 2014 22:00:45 GMT

Assuming you're importing multiple files of the same type don't use proc import, use a "manual" import instead.

Run Proc Import once, get the code from the log and modify it to be what you want. Then use that code to import the rest of the files.

If you're importing many files you'll need to tweak each one somehow, but you could automate it if you did have naming conventions.

Re: Reading in all numeric variables as character variables from .csv files

Jordana — Tue, 11 Feb 2014 23:02:33 GMT

Reeza, thank you for the quick response. Do you have sample syntax? I have over 20 source files with this inconsistency issue and manual statements may work but I am hoping to avoid too much typing. I was able to fix the issue using Proc Import followed by put statements but each variable requires a put (about three lines of syntax) for about seven variables per file; any way I have thought of so far gets to quite a bit of coding. Any syntax shortcuts are welcome!

Re: Reading in all numeric variables as character variables from .csv files

Reeza — Tue, 11 Feb 2014 23:05:00 GMT

Are the files the same structure/format?

Re: Reading in all numeric variables as character variables from .csv files

Jordana — Tue, 11 Feb 2014 23:06:54 GMT

They are indeed.

Re: Reading in all numeric variables as character variables from .csv files

Fugue — Tue, 11 Feb 2014 23:27:20 GMT

How are the csv files being created? Some tools (e.g. Excel) will look at the first several rows to determine format.

Re: Reading in all numeric variables as character variables from .csv files

Reeza — Tue, 11 Feb 2014 23:28:12 GMT

Then once you've fixed it once you're done. Just change where the import for the .csv file. On a separate note you can read all files at once using a wild card in your filename statement.

If you need help besides this, I'd suggest posting code

Re: Reading in all numeric variables as character variables from .csv files

Tom — Tue, 11 Feb 2014 23:49:34 GMT

Just write the code to read the files using a data step.

All you need to know is the names of the variables and the types and lengths to use.

data want ;

infile 'myfile.csv' dsd dlm=',' lrecl=100000 truncover firstobs=2 ;

length var1 8 var2 $50 var3 $25 ;

input var1 var2 var3 ;

run;

Re: Reading in all numeric variables as character variables from .csv files

Jordana — Wed, 12 Feb 2014 15:04:26 GMT

Tom,

Thank you, I will give this a try!

Re: Reading in all numeric variables as character variables from .csv files

agoldma — Sat, 18 Jun 2016 22:01:27 GMT

If you want PROC IMPORT to have an option to import all fields as character, please vote:

PROC IMPORT -- all fields as character