topic Re: Converting character to numeric for dummies in SAS Software for Learning Community

Converting character to numeric for dummies

jpelot — Thu, 28 Jan 2016 04:33:51 GMT

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!

Re: Converting character to numeric for dummies

Reeza — Tue, 27 Jul 2021 20:15:05 GMT

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

Re: Converting character to numeric for dummies

jpelot — Thu, 28 Jan 2016 04:53:28 GMT

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

Re: Converting character to numeric for dummies

Reeza — Thu, 28 Jan 2016 04:55:30 GMT

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

Re: Converting character to numeric for dummies

jpelot — Thu, 28 Jan 2016 05:00:56 GMT

/* 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;

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 */

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

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

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

72 PROC CONTENTS DATA=WORK.IMPORT; RUN;

NOTE: PROCEDURE CONTENTS used (Total process time):

real time 0.18 seconds

cpu time 0.18 seconds

75 %web_open_table(WORK.IMPORT);

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

85 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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.

Re: Converting character to numeric for dummies

Reeza — Thu, 28 Jan 2016 05:07:11 GMT

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.

Re: Converting character to numeric for dummies

jpelot — Thu, 28 Jan 2016 05:15:43 GMT

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

Re: Converting character to numeric for dummies

Reeza — Thu, 28 Jan 2016 05:17:10 GMT

proc sql, the last step.