Converting character to numeric for dummies

[jpelot]

I have a column of data with the following attributes:

Data Set Name: WORK.IMPORT

Variable: Kill_wt

Type: Char

Len: 14

Format: $14

Informat: $14

Label: Kill wt

These data are continuous numerical data, but SAS doesn't see it this way. I tried the following command from a Youtube video to try and correct this:

data WORK.IMPORT2; set WORK.IMPORT;
* Convert Kill_wt from character to numeric;
Kill_wt = input(Kill_wt, best12.);
run;

No errors show up when I run this, but nothing changes. How can I change the format/informat for "Kill_wt" from $14(character) to BEST12(this is what all my numerical data in the set are set as)? Please explain it to me like I have only been using SAS for a week (I have).

Thanks!

[Reeza · Accepted solution]

Editor's Note: This post is among the most visited in these forums! In addition to the help provided below, here's a tutorial that walks you through what to do in about 9 minutes:

Your code is correct, with one major issue, you can't convert a variable from text to numeric and keep the same name.

That would be changing the variable type on the fly and SAS doesn't support that.

So...either rename the original variable on your SET statement or your calculated variable.

kill_wt2 = input(kill_wt, best12.)

This SAS Note contains lots of helpful advice for converting data types in SAS: 

• Sample 24590: Convert variable values from character to numeric or from numeric to character

[jpelot]

Thanks Reeza, but I tried that to no avail. Any other ideas? Could it be how I imported the data from .xls?

[Reeza]

Post your code, log and proc contents from new data set. That should have worked.

[jpelot]

/* Generated Code (IMPORT) */
/* Source File: Aspirin field trial.xls */
/* Source Path: /folders/myfolders */
/* Code generated on: Thursday, January 28, 2016 3:26:09 AM */

%web_drop_table(WORK.IMPORT);

FILENAME REFFILE "/folders/myfolders/Aspirin field trial.xls" TERMSTR=CR;

PROC IMPORT DATAFILE=REFFILE
DBMS=XLS
OUT=WORK.IMPORT;
GETNAMES=YES;
RUN;

PROC CONTENTS DATA=WORK.IMPORT; RUN;

%web_open_table(WORK.IMPORT);

data WORK.IMPORT2; set WORK.IMPORT;
* Convert kill_wt from character to numeric;
Lung_wt2 = input(Lung_wt, best9.);
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

55

56 /* Generated Code (IMPORT) */

57 /* Source File: Aspirin field trial.xls */

58 /* Source Path: /folders/myfolders */

59 /* Code generated on: Thursday, January 28, 2016 3:26:09 AM */

60

61 %web_drop_table(WORK.IMPORT);

NOTE: Table WORK.IMPORT has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

62

63

64 FILENAME REFFILE "/folders/myfolders/Aspirin field trial.xls" TERMSTR=CR;

65

66 PROC IMPORT DATAFILE=REFFILE

67 DBMS=XLS

68 OUT=WORK.IMPORT;

69 GETNAMES=YES;

70 RUN;

NOTE: Variable Name Change. Ear tag -> Ear_tag

NOTE: Variable Name Change. Entry wt -> Entry_wt

NOTE: Variable Name Change. Treat d1 -> Treat_d1

NOTE: Variable Name Change. Treat d3 -> Treat_d3

NOTE: Variable Name Change. Treat d6 -> Treat_d6

NOTE: Variable Name Change. Lung wt -> Lung_wt

NOTE: Variable Name Change. Kill wt -> Kill_wt

NOTE: The import data set has 244 observations and 21 variables.

NOTE: WORK.IMPORT data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.06 seconds

cpu time 0.04 seconds

71

72 PROC CONTENTS DATA=WORK.IMPORT; RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.18 seconds

cpu time 0.18 seconds

73

74

75 %web_open_table(WORK.IMPORT);

76

77 data WORK.IMPORT2; set WORK.IMPORT;

78 * Convert kill_wt from character to numeric;

79 Lung_wt2 = input(Lung_wt, best9.);

80 run;

NOTE: There were 244 observations read from the data set WORK.IMPORT.

NOTE: The data set WORK.IMPORT2 has 244 observations and 22 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.02 seconds

81

82

83

84

85 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

97

PROC SQL;
CREATE TABLE WORK.query AS
SELECT Ear_tag , Entry_wt , Gender , 'Cast'n , DH , Treat_d1 , Treat_d3 , Treat_d6 , Lung_wt , Kill_wt , 'Group'n , ADG , Fibrin , Fibrosis , Lesion , Plant , Feedlot , S_3MI_D0 , S_3MI_D3 , S_3MI_D6 , R_3MI FROM WORK.IMPORT;
RUN;
QUIT;

PROC DATASETS NOLIST NODETAILS;
CONTENTS DATA=WORK.query OUT=WORK.details;
RUN;

PROC PRINT DATA=WORK.details;
RUN;

I'm not sure where my proc contents are, but hopefully this helps.

[Reeza]

Your last step references import not the new data set you created, import2.

Change it to refer to import2 and change the select to include lung_wt2.

Basically you created the correct dataset but didn't use it in your next steps, but went back to the original dataset.

Manually check the data set import2 -> open it in the editor.

[jpelot]

I hate to ask this, you have been so patient and helpful, but which step am I changing to import2?

[Reeza]

proc sql, the last step.