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;
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 .
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
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.
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!
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.