BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
emk1010
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Kalind_Patel
Lapis Lazuli | Level 10

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

8 REPLIES 8
Astounding
PROC Star

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
Lapis Lazuli | Level 10

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
Super User

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
Calcite | Level 5

Ok great - thank you!

ballardw
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 6787 views
  • 1 like
  • 5 in conversation