+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 19

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

  1. #1
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

    [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. #2
    Points: 5,259, Level: 46
    Level completed: 55%, Points required for next Level: 91
    SiBorg's Avatar
    Posts
    255
    Thanks
    71
    Thanked 25 Times in 22 Posts

    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. #3
    Beep
    Points: 63,230, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Discussion EnderPosting AwardCommunity AwardMaster TaggerFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    11,313
    Thanks
    266
    Thanked 2,202 Times in 1,881 Posts

    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. #4
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

    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. #5
    Beep
    Points: 63,230, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Discussion EnderPosting AwardCommunity AwardMaster TaggerFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    11,313
    Thanks
    266
    Thanked 2,202 Times in 1,881 Posts

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

    Quote Originally Posted by StatsClue View Post
    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-25-2011)

  8. #6
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

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

    Quote Originally Posted by Dason View Post
    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. #7
    TS Contributor
    Points: 9,122, Level: 64
    Level completed: 24%, Points required for next Level: 228

    Posts
    888
    Thanks
    0
    Thanked 93 Times in 92 Posts

    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. #8
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

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

    Quote Originally Posted by Mean Joe View Post
    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. #9
    Beep
    Points: 63,230, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Discussion EnderPosting AwardCommunity AwardMaster TaggerFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    11,313
    Thanks
    266
    Thanked 2,202 Times in 1,881 Posts

    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. #10
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

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

    Quote Originally Posted by Dason View Post
    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. #11
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

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

    Quote Originally Posted by Dason View Post
    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.
    Last edited by StatsClue; 12-29-2011 at 12:31 PM.

  14. #12
    Beep
    Points: 63,230, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Discussion EnderPosting AwardCommunity AwardMaster TaggerFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    11,313
    Thanks
    266
    Thanked 2,202 Times in 1,881 Posts

    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. #13
    Points: 4,315, Level: 41
    Level completed: 83%, Points required for next Level: 35

    Posts
    156
    Thanks
    87
    Thanked 1 Time in 1 Post

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

    Quote Originally Posted by Dason View Post
    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. #14
    Beep
    Points: 63,230, Level: 100
    Level completed: 0%, Points required for next Level: 0
    Awards:
    Discussion EnderPosting AwardCommunity AwardMaster TaggerFrequent Poster
    Dason's Avatar
    Location
    Ames, IA
    Posts
    11,313
    Thanks
    266
    Thanked 2,202 Times in 1,881 Posts

    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. #15
    Points: 5,259, Level: 46
    Level completed: 55%, Points required for next Level: 91
    SiBorg's Avatar
    Posts
    255
    Thanks
    71
    Thanked 25 Times in 22 Posts

    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?

+ Reply to Thread
Page 1 of 2 1 2 LastLast

           




Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats