DATA Step, Macro, Functions and more

converting variables to numeric

Reply
Contributor
Posts: 47

converting variables to numeric

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;
Super User
Super User
Posts: 9,599

Re: converting variables to numeric

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 .

Contributor
Posts: 47

Re: converting variables to numeric

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 
Super User
Super User
Posts: 9,599

Re: converting variables to numeric

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.

Contributor
Posts: 47

Re: converting variables to numeric

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!

Super User
Super User
Posts: 9,599

Re: converting variables to numeric

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.  

Super User
Posts: 10,209

Re: converting variables to numeric

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 6 replies
  • 51 views
  • 0 likes
  • 3 in conversation