Thread: [Excel] - How to assign numerical values to text data on Excel

1. [Excel] - How to assign numerical values to text data on Excel

Column: Race- has Blacks, White, Hispanics, Asians etc.

In the next column I need to give these individual races numerical values, for eg. Black is 1, White is 2 and so on.

How to do it?

Thanks.

2. Re: [Excel] - How to assign numerical values to text data on Excel

Lets say you have column A with the race and you want to put the numerical value into column B

Somewhere else on your spreadsheet (doesn't matter where) let's say, column E and F you can list the name in column E and the numerical equivalent in column F

So, e.g.

E F

Black 1
Caucasian 2
Hispanic 3
Asian 4

Or whatever you want.

You then put the following formula into column B

=VLOOKUP(A1,\$E\$1:\$F\$4,2)

This formula looks up the value from cell A1 in the array E1:F4 and returns the value from the second column in E1:F4

The \$ are important so that when you drag the copy handle and copy the formula to the other cells the A1 reference changes to A2, A3, etc, but the E1:F4 stays constant.

Hope this helps.

3. The Following User Says Thank You to SiBorg For This Useful Post:

StatsClue (12-25-2011)

4. Re: [Excel] - How to assign numerical values to text data on Excel

Also just out of curiosity - why do you want to convert everything to a number?

5. Re: [Excel] - How to assign numerical values to text data on Excel

Thanks SiBorg. I figured out another way to do it using the 'IF' function. Thanks for trying to help.

Dason- I think a regression analysis on SAS necessarily requires you to have numerical values- so no other reason .

6. Re: [Excel] - How to assign numerical values to text data on Excel

Originally Posted by StatsClue
Dason- I think a regression analysis on SAS necessarily requires you to have numerical values- so no other reason .
SAS will do the conversion for you. You just tell SAS that your variable is categorical and it does the rest for you. Typically the software you're using will do the dummy coding for you.

Plus just doing a direct coding into numbers isn't the right thing to do. See this thread at the stats stackexchange site for explanations why: http://stats.stackexchange.com/quest...-n-1-variables

7. The Following User Says Thank You to Dason For This Useful Post:

StatsClue (12-26-2011)

8. Re: [Excel] - How to assign numerical values to text data on Excel

Originally Posted by Dason
SAS will do the conversion for you. You just tell SAS that your variable is categorical and it does the rest for you. Typically the software you're using will do the dummy coding for you.

Plus just doing a direct coding into numbers isn't the right thing to do. See this thread at the stats stackexchange site for explanations why: http://stats.stackexchange.com/quest...-n-1-variables
Oh, thanks for bringing this to attention. I knew vaguely about dummy variables but when SAS refused to perform my [prog reg, model outcome=predictor, run] I thought numerical codes would solve the problem (and dummy variables seemed like a complicated procedure). So you're saying the results using numerical codes vs. dummy variables would be different?

When is coding acceptable? Is it okay to code when there are only two values, for example, yes/no, red/blue, male/female ? Asking 'cause I've done it already! Crap.

9. Re: [Excel] - How to assign numerical values to text data on Excel

When SAS refused to work with Black/White/etc, you needed to put a CLASS statement in the PROC, eg
Code:
``````proc reg;
class Race;
model ...;``````
You can keep 1/2/3/4/etc for the coded Race, but put in a CLASS statement again. Then you will get what you want: compare 2 vs 1 (eg Black vs White), 3 vs 1 (eg Asian vs White), 4 vs 1 etc.
If you do not put the CLASS statement, then you are implying that there is a consistent trend in races and as the race value increases (2 to 3 to 4) the trend continues (linearly). Obviously not what you want to do with races.

10. Re: [Excel] - How to assign numerical values to text data on Excel

Originally Posted by Mean Joe
When SAS refused to work with Black/White/etc, you needed to put a CLASS statement in the PROC, eg
Code:
``````proc reg;
class Race;
model ...;``````
You can keep 1/2/3/4/etc for the coded Race, but put in a CLASS statement again. Then you will get what you want: compare 2 vs 1 (eg Black vs White), 3 vs 1 (eg Asian vs White), 4 vs 1 etc.
If you do not put the CLASS statement, then you are implying that there is a consistent trend in races and as the race value increases (2 to 3 to 4) the trend continues (linearly). Obviously not what you want to do with races.
Thanks.

The class statement doesn't seem to be working with proc reg data=xyz; . The moment I put class underneath this, it shows up in red and SAS doesn't run eventually. I'd put : class racecode (ref='0')/param=ref; . Didn't work.

11. Re: [Excel] - How to assign numerical values to text data on Excel

Does proc reg take a class option? I don't think it does. You should probably use proc glm for this kind of case.

12. Re: [Excel] - How to assign numerical values to text data on Excel

Originally Posted by Dason
Does proc reg take a class option? I don't think it does. You should probably use proc glm for this kind of case.
Right, it doesn't.

I tried creating dummy variables for race. Should the comparison dummy variable always be coded '0'? Thanks.

13. Re: [Excel] - How to assign numerical values to text data on Excel

Originally Posted by Dason
SAS will do the conversion for you. You just tell SAS that your variable is categorical and it does the rest for you. Typically the software you're using will do the dummy coding for you.

Plus just doing a direct coding into numbers isn't the right thing to do. See this thread at the stats stackexchange site for explanations why: http://stats.stackexchange.com/quest...-n-1-variables
Oh and is direct coding alright if the variable has only two possibilities: eg Gender, type of drug used (eg. either X or Y) etc.? In this case, would the following be right:
proc reg data=mydata;
model outcome=gender;
run;

?
(coded gender 1 and 2)

Also, if the outcome is dose and the gender-dose association comes out to be significant, how do you tell from the SAS output which gender gets the higher dose?
Thanks.

14. Re: [Excel] - How to assign numerical values to text data on Excel

Coding gender as 1/2 isn't a a very good idea. Typical coding schemes usually either code it as 0/1 or -1/1. I prefer 0/1 in which case the parameter associated with gender would tell you the estimate mean difference between the genders. But you probably would be better off using proc glm and letting it take care of the coding for you.

15. Re: [Excel] - How to assign numerical values to text data on Excel

Originally Posted by Dason
Coding gender as 1/2 isn't a a very good idea. Typical coding schemes usually either code it as 0/1 or -1/1. I prefer 0/1 in which case the parameter associated with gender would tell you the estimate mean difference between the genders. But you probably would be better off using proc glm and letting it take care of the coding for you.
Thanks. You mean the parameter estimate associated with gender would tell the mean difference between genders NOT if the coding is 1/2? Also, any way to tell which gender is associated with a higher dose?

Also my previous question- must the comparison variable for dummy variables always be coded 'o'? I mean, should it always be 'else 0'?

Stats not my field so at sea. Pardon the naive questions. Thanks.

16. Re: [Excel] - How to assign numerical values to text data on Excel

Lets say you code males 0 and females 1. Then let's say the parameter associated with gender was estimated to be 2. That would mean that you would estimate that the average ... whatever your dependent variable is... is 2 units higher on average for females than it is for males.

17. Re: [Excel] - How to assign numerical values to text data on Excel

Out of interest, say you were performing logistic regression with SPSS rather than SAS and had the races coded 1-4. Would specifying the data type as 'nominal' rather than 'ordinal' create the desired effect of removing the linear relationship between the coded values?

Page 1 of 2 1 2 Last

 Tweet