topic Re: converting variables to numeric in SAS Programming

converting variables to numeric

lmyers2 — Thu, 28 Jun 2018 12:07:46 GMT

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;

Re: converting variables to numeric

RW9 — Thu, 28 Jun 2018 12:10:25 GMT

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 .

Re: converting variables to numeric

lmyers2 — Thu, 28 Jun 2018 12:10:28 GMT

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

Re: converting variables to numeric

RW9 — Thu, 28 Jun 2018 12:27:31 GMT

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.

Re: converting variables to numeric

lmyers2 — Thu, 28 Jun 2018 12:31:45 GMT

PP1	MAP1
28	45.00
61	67.00
25	51.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!

Re: converting variables to numeric

Kurt_Bremser — Thu, 28 Jun 2018 12:49:23 GMT

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.

Re: converting variables to numeric

RW9 — Thu, 28 Jun 2018 12:56:13 GMT

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.