Convert variable from character to numeric

[sastuck]

Hey,

I ran into this message when trying to run a proc means:

ERROR: Variable BONUS in list does not match type prescribed for this list.

ERROR: Variable STOCK_AWARDS in list does not match type prescribed for this list.

I looked at the  output a proc contents to verify that these variables are both character. Another variable, which is numeric, did not get the same error message. 

So I need to convert these variables from char to numeric it looks like. the variable bonus has a length of 12 and the variable stock_awards has a length of 30. 

I found this code from sas support:

/* Example 1: The simplest case character to numeric */
/* The resulting data set has both the original character variable and a new */
/* numeric variable.  If you want the result to have just one variable with  */
/* the original name, uncomment the DROP and RENAME statements. */

data new1;
   orig_var = '80000';
   new_var = input(orig_var,8.);
/* drop orig_var; */
/* rename new_var = orig_var; */
run;
proc print; 
run;

But am having trouble implementing it into my own program. 

I am not sure if I am supposed to leave the numbers in the quotes the same or adapt them to my own data. Additionally, is "new1" what the new dataset will be called? how will SAS know where the original data are located? Is there a way to change the variable to the current data set they are in currently? 

Here is the log from the proc means statement:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 title "Summary Statistics";

72 title2 "CEO Compensation";

73 PROC MEANS data=paper.ceo_firm n mean median std min max skew maxdec=2;

74 var salary bonus stock_awards;

ERROR: Variable BONUS in list does not match type prescribed for this list.

ERROR: Variable STOCK_AWARDS in list does not match type prescribed for this list.

75 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 1712.53k

OS Memory 28584.00k

Timestamp 03/31/2018 06:26:48 PM

Step Count 197 Switch Count 0

Page Faults 0

Page Reclaims 241

Page Swaps 0

Voluntary Context Switches 6

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 0

76

77 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

89

Thanks for the help!

[art297]

No, you don't have to create a new file but, at the same time, you can't just make a character variable to be numeric.

However, you can rename the two fields, and then create the two numeric fields with the original names. e.g.:

data have;
  input x y (BONUS STOCK_AWARDS) ($);
  cards;
1 1 20 30
2 2 40 70
3 3 ?  12
4 4 U/K .
;

data have;
  set have (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));
  BONUS=input(_BONUS,?? 8.);
  STOCK_AWARDS=input(_STOCK_AWARDS,?? 8.);
run;

I added the double question marks to the input function so that your log wouldn't show a bunch of errors that you'll likely get due to non numeric values that can't be read as numbers.

Art, CEO, AnalystFinder.com

[sastuck]

Here is your code as it appears in my program:

data paper.ceo_firm;
  input x y (BONUS STOCK_AWARDS) ($);
  cards;
1 1 20 30
2 2 40 70
3 3 ?  12
4 4 U/K .
;

data paper.ceo_firm;
  set paper.ceo_firm (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));
  BONUS=input(_BONUS,?? 8.);
  STOCK_AWARDS=input(_STOCK_AWARDS,?? 8.);
run;

title "Table 1.";
title2 "Summary Statistics";
title3 "CEO Compensation";
PROC MEANS data=paper.ceo_firm n mean median std min max skew maxdec=2;
	var salary bonus stock_awards;
run;

I am not sure what this output is telling me though:

  x            y            bonus   stock_awards      bonus   stock_awards

1

1

1

20

30

20

30

2

2

2

40

70

40

70

3

3

3

?

12

.

12

[art297]

You missed the underscores in two of the variables in your output.

The code I suggested RENAMED the two character variables to have the same name but each preceded by an UNDERSCORE.

i.e. _BONUS is the original character variable while BONUS is now a numeric variable. The same is true for the other variable.

You'd want to use the two new variables (i.e. BONUS and STOCK_AWARDS .. the ones whose variable names DON'T begin with an UNDERSCORE) in your analysis. 

Art, CEO, AnalystFinder.com

[art297]

Actually, I'm confused by what you showed as your output. You said you ran:

PROC MEANS data=paper.ceo_firm n mean median std min max skew maxdec=2;
	var salary bonus stock_awards;
run;

But then showed something with x, y and two sets of bonus and stock_awards. I don't know where that came from. It looks like the result of a PROC PRINT of the example I showed.

Art, CEO, AnalystFinder.com

[sastuck]

I am confused as well--that is the output in paper.ceo_firm after I ran the code I posted a message ago. 

[art297]

Post your log! That is the best way for anyone to see what you ran.

Art, CEO, AnalystFinder.com

[sastuck]

Here you are!

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

70

71 data paper.ceo_firm;

72 input x y (BONUS STOCK_AWARDS) ($);

73 cards;

NOTE: The data set PAPER.CEO_FIRM has 4 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

user cpu time 0.00 seconds

system cpu time 0.01 seconds

memory 525.75k

OS Memory 33244.00k

Timestamp 03/31/2018 08:07:25 PM

Step Count 342 Switch Count 2

Page Faults 0

Page Reclaims 90

Page Swaps 0

Voluntary Context Switches 41

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

78 ;

79

80 data paper.ceo_firm;

81 set paper.ceo_firm (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));

82 BONUS=input(_BONUS,?? 8.);

83 STOCK_AWARDS=input(_STOCK_AWARDS,?? 8.);

84 run;

NOTE: There were 4 observations read from the data set PAPER.CEO_FIRM.

NOTE: The data set PAPER.CEO_FIRM has 4 observations and 6 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 808.43k

OS Memory 33504.00k

Timestamp 03/31/2018 08:07:25 PM

Step Count 343 Switch Count 2

Page Faults 0

Page Reclaims 124

Page Swaps 0

Voluntary Context Switches 49

Involuntary Context Switches 0

Block Input Operations 288

Block Output Operations 264

85

86 title "Table 1.";

87 title2 "Summary Statistics";

88 title3 "CEO Compensation";

89 PROC MEANS data=paper.ceo_firm n mean median std min max skew maxdec=2;

90 var salary bonus stock_awards;

ERROR: Variable SALARY not found.

91 run;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE MEANS used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 351.84k

OS Memory 33244.00k

Timestamp 03/31/2018 08:07:25 PM

Step Count 344 Switch Count 0

Page Faults 0

Page Reclaims 49

Page Swaps 0

Voluntary Context Switches 10

Involuntary Context Switches 0

Block Input Operations 288

Block Output Operations 0

92

93 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

105

User: apmorabito0

Messages: 53

WEIGHT=

[art297 · Accepted solution]

You weren't supposed to re-create the file paper.ceo_firm. Since you did, you'll have to re-create it!

What you wanted to run was:

data paper.ceo_firm;
  set paper.ceo_firm (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));
  BONUS=input(_BONUS,?? 8.);
  STOCK_AWARDS=input(_STOCK_AWARDS,?? 8.);
run;

However, since you over-wrote the original file, you'll have to back up and recreate it before running the above code.

Art, CEO, AnalystFinder.com

[sastuck]

How do I know this won't overwrite it again?

data paper.ceo_firm;
  set paper.ceo_firm (rename=(BONUS=_BONUS STOCK_AWARDS=_STOCK_AWARDS));
  BONUS=input(_BONUS,?? 8.);
  STOCK_AWARDS=input(_STOCK_AWARDS,?? 8.);
run;

Isn't this how I had it as well?

[art297]

Because that isn't what you did. What you had run was:

data paper.ceo_firm;
   input x y (BONUS STOCK_AWARDS) ($);
   cards;
 etc.

That overwrote your original dataset.

SAS will simply do what you tell it to do (as long as there aren't errors in the code).

Art, CEO, AnalystFinder.com

[sastuck]

When you say "recreate it", what do you mean? I simply deleted the code and re-ran the program, and everything looks the same as it was. Should it not be? Do I need to open a clean program and re-import the csv?

[art297]

If you ran the code you posted a couple of posts back, then you'd have to go back to whatever step you ran to create the paper.ceo_firm dataset.

Art, CEO, AnalystFinder.com

[sastuck]

Okay, well I just deleted some of the earlier code we had and then ran the one I just accepted as the solution. It worked in converting the variables. 

Thanks!