BookmarkSubscribeRSS Feed
lmyers2
Obsidian | Level 7

Hello - I'm trying to change variables from character to numeric. Interestingly, some of them converted but others did not. Code below. I'm importing from excel and I wonder if it's easier to format the excel spreadsheet or if that formatting is affecting how SAS is reading in the variables. Thanks!

 

ABWNum=input(ABW, Best.); drop ABW;
6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

How are we supposed to answer, we cannot see any data?  Input will convert those values which can be converted to numeric, others will just be .

lmyers2
Obsidian | Level 7

Let me clarify that I put the code within a data step. 

 

Here is an example where PP1 converted but MAP1 did not in the results. The code to convert is the same for each variable (copied/pasted line-by-line).

 

96 PP1 Num 8 BEST. PP1 
97 MAP1 Char 6 $6. $6. MAP1 
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Sorry, you misunderstand.  We need to see the data, i.e. the items read in which are not being converted, e.g.:

PP1

123

456

789

4a12

 

In the above, the fourth row will not read in correctly as it is not numeric.  Maybe post a couple of rows from your excel file which demonstrates values which don't read correctly.

lmyers2
Obsidian | Level 7
PP1MAP1
2845.00
6167.00
2551.00
0 

 

Here is sample data from excel. It's all numeric so I'm not sure why some are converting while others are not. Thanks for the help!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, would really need to see the file.  Excel does a good job at hiding files.  One thing I can suggest is to save the Excel file to CSV.  Then write your datastep import to import the csv file, which is fully under your control.  Don't use proc import (apart from maybe to create initial code), as that guesses.  

Kurt_Bremser
Super User

Your problem starts with the use of an unreliable file format for transfering data from Excel to SAS.

Save the data to a csv file, and then write a data step that reads the csv into a SAS dataset, so that you have full control over the data types and the informats used. Note that importing Excel files involves guessing on the part of SAS, and changes in the data will lead to unwanted changes in your dataset structure.

You can use proc import once on the csv, and then take the resulting data step from the log to modify it according to your needs.

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
  • 6 replies
  • 675 views
  • 0 likes
  • 3 in conversation