DATA Step, Macro, Functions and more

Change a large number of character variables to numeric

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Change a large number of character variables to numeric

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!


Accepted Solutions
Solution
‎01-03-2017 11:00 PM
Frequent Contributor
Posts: 96

Re: Change a large number of character variables to numeric

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

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

View solution in original post


All Replies
Super User
Posts: 5,083

Re: Change a large number of character variables to numeric

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?

Solution
‎01-03-2017 11:00 PM
Frequent Contributor
Posts: 96

Re: Change a large number of character variables to numeric

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

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

Super User
Posts: 17,836

Re: Change a large number of character variables to numeric

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. 

New Contributor
Posts: 4

Re: Change a large number of character variables to numeric

Ok great - thank you!

Super User
Posts: 10,500

Re: Change a large number of character variables to numeric

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.

New Contributor
Posts: 4

Re: Change a large number of character variables to numeric

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!

Super User
Posts: 17,836

Re: Change a large number of character variables to numeric

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. 

New Contributor
Posts: 4

Re: Change a large number of character variables to numeric

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 306 views
  • 0 likes
  • 5 in conversation