DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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

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? 


Accepted Solutions
Solution
‎02-11-2014 06:49 PM
Super User
Super User
Posts: 6,500

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

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;

View solution in original post


All Replies
Super User
Posts: 17,831

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

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.

Occasional Contributor
Posts: 11

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

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! Smiley Happy

Super User
Posts: 17,831

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

Are the files the same structure/format?

Occasional Contributor
Posts: 11

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

They are indeed.

Super Contributor
Posts: 307

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

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

Super User
Posts: 17,831

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

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 Smiley Happy

Solution
‎02-11-2014 06:49 PM
Super User
Super User
Posts: 6,500

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

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;

Occasional Contributor
Posts: 11

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

Tom,

Thank you, I will give this a try!

Contributor
Posts: 74

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

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

PROC IMPORT -- all fields as character

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 2052 views
  • 6 likes
  • 5 in conversation