st: RE: merging aggregate and survey data with different state codes

["Eric M. Uslaner" <euslaner@umd.edu>]

Greetings,

I have two data sets and want to merge the aggregate data set into the survey data set.  Ultimately I want to merge the two data sets by Congressional district (which is how the aggregate data set is organized).  But the major problem is that the two data sets use different codes for states.  For the aggregate data, the states are numbered from 1-50 alphabetically.  But for the survey data the state codes are:

tabl C3_PPSTATEN

C3_PPSTATEN (State)     code  |   freq 
------------------------------+--------
                 me      11   |      4
                 nh      12   |      3
                 vt      13   |      4
                 ma      14   |     34
                 ri      15   |      2
                 ct      16   |     20
                 ny      21   |     76
                 nj      22   |     42
                 pa      23   |     60
                 oh      31   |     56
                 in      32   |     24
                 il      33   |     49
                 mi      34   |     36
                 wi      35   |     23
                 mn      41   |     35
                 ia      42   |     18
                 mo      43   |     31
                 nd      44   |      1
                 sd      45   |      8
                 ne      46   |     12
                 ks      47   |     14
                 de      51   |      5
                 md      52   |     33
                 dc      53   |      4
                 va      54   |     29
                 wv      55   |     13
                 nc      56   |     41
                 sc      57   |     18
                 ga      58   |     41
                 fl      59   |     89
                 ky      61   |     16
                 tn      62   |     29
                 al      63   |     19
                 ms      64   |      8
                 ar      71   |     12
                 la      72   |     15
                 ok      73   |     15
                 tx      74   |     92
                 mt      81   |      6
                 id      82   |      3
                 co      84   |     17
                 nm      85   |      7
                 az      86   |     33
                 ut      87   |     10
                 nv      88   |     20
                 wa      91   |     34
                 or      92   |     20
                 ca      93   |    127
                 ak      94   |      1
                 hi      95   |      6
------------------------------+--------
                      Total   |   1315

Clearly there is no one-to-one function I can think of that would transform the state numbers in the aggregate data set.  I could, of course, create a new variable step by step:

gen int statenew=.
replace statenew= 1 if statenum == 1  (where statenum is the numeric equivalent of state; or alternatively "if state=="AR")
Etc. through replace statenew=55 if statenum == 49 (statenum = 50 is WY and there are no respondents in the survey from WY)

This seems rather cumbersome.  If necessary I'll do it.  Does anyone have any easier way of doing this?

Thanks much,

Ric Uslaner
euslaner@umd.edu


*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/