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.
In case you have a long list of text data in excel (e.g. names of states, countries etc.) and you want to import this to SAS or STATA, you first need to assign a numerical value to entries in the list. This can be easily achieved using the "IF" function in Excel. I will use the Penn World Table to illustrate:
Procedure:
1) Insert an extra column.
2) In the Penn World Table, the first country is Angola (country iso code AGO) and assuming that you have 41 year observations for each country ranging from 1960 to 2000 - the observations for Angola will run from row 2 to row 42. The next country is Albania (country iso code ALB) and its observations run from row 43- row 83, etc. (observations start from the second row since the first row contains variable names)
3) In the new column, put a 1 in the second row (corresponding to the first observation for Angola). Then, use the "IF" function to specify that all remaining entries for Angola be coded "1", Albania "2", etc. Assuming that the iso_code identifier is in column B in Excel, the following formula should do it
"=IF(B3= B2,C2,C2+1)"
Recall that we put a 1 in the second row of the new column ( "C2" in this case). So in cell C3, we have instructed Excel to assign value 1 if the country identifier in column B3 is the same as the country identifier in column B2, ELSE - assign a value of C2+1 = 2.
4) Drag down this formula until the bottom and each country will now have a unique numerical identifier.
Andrew Musau
Faculty of Economics and Social Sciences
University of Agder