Change a large number of character variables to numeric

[emk1010]

Hi everyone,

I have a very large dataset with 500+ variables from an Excel sheet. Almost all of the variables should be numeric variables.

when the sheet was imported, most of the variables were set to character values. I know you can set the character variable to a new numeric variable in a data step, but with the number of variables in this dataset it would take a long amount of time. Is there a way to convert a large number of character variables to numeric at once, without having to list each individual one in a data step?

Thanks!

[Astounding]

Macro language can make this happen.  However, you should first consider whether this is a wise thing to do.  If SAS imported them as character, some of the values are likely to contain characters that can't be converted to numeric.  Do you want to get rid of them (sight unseen) or do you want to inspect them first to see why SAS made that choice?

[Kalind_Patel · Accepted solution]

Please refer this sample note of SAS, they have provided full code for the exact scenario,

http://support.sas.com/kb/40/700.html

[Reeza]

Make sure variable names are 30 chars or less if using this method. It appends _n to name so it requires 2 chars for this. 

[emk1010]

Ok great - thank you!

[ballardw]

If this is something you may repeat in the future then you need to examine how the data was brough into SAS. The earlier in a process that things are addressed then likely the better in the long run. continuing to "fix" after import leads to headaches.

The first thing is to determine why a 500+ column Excel spreadsheet was considered a good way to interchange data.

[emk1010]

This was a very long survey - I don't have a lot of survey experience, but this seemed like the best method to import data into SAS. I am open to suggestions for how to make the process easier in the future, though!

[Reeza]

Most survey tools allow you to export the data as a CSV file. Use this instead. Set the GUESSINGROWS to a high number and you'll get the code to import the data in the log. This will detail the specification and you can easily modify the program en masse to generate what you need. The added benefit is this is now a traceable and repeatable process.  If you make changes to the Excel file, you don't have a way of tracking these changes. Reproduciblity is a big thing these days. 

[emk1010]

Thank you! I used the code somebody posted previously to fix the dataset, but this is definitely helpful in case I need to do something similar to this in the future.