Need help with merge

[Euslaner]

I know that I shouldn't post files here but I have been working on trying to merge these two files for 2 days without success. Any help would be gratefully received. The variable to be merged is state. Thanks,

Ric Uslaner

[Ken Chui]

For those who would like to help, here are the dataex results:

File 1:

* Example generated by -dataex-. For more info, type help dataex
clear
input long stateab float stateid_01 long state str14(StateAbbr StateAbbr_01)
 2  1  51 "Alabama"        "Alabama"       
 4  3  53 "Arizona"        "Arizona"       
 3  4  54 "Arkansas"       "Arkansas"      
 1  2  52 "Alaska"         "Alaska"        
 5  5  55 "California"     "California"    
 6  6  56 "Colorado"       "Colorado"      
 7  7  57 "Connecticut"    "Connecticut"   
 8  8  58 "Delaware"       "Delaware"      
 9 10  59 "Florida"        "Florida"       
10 11  60 "Georgia"        "Georgia"       
11 12  61 "Hawaii"         "Hawaii"        
13 13  62 "Idaho"          "Idaho"         
14 14  63 "Illinois"       "Illinois"      
15 15  64 "Indiana"        "Indiana"       
12 16  65 "Iowa"           "Iowa"          
16 17  66 "Kansas"         "Kansas"        
17 18  67 "Kentucky"       "Kentucky"      
18 19  68 "Louisiana"      "Louisiana"     
21 20  69 "Maine"          "Maine"         
20 21  70 "Maryland"       "Maryland"      
19 22  71 "Massachusetts"  "Massachusetts"
22 23  72 "Michigan"       "Michigan"      
23 24  73 "Minnesota"      "Minnesota"     
25 27  74 "Mississippi"    "Mississippi"   
24 26  75 "Missouri"       "Missouri"      
26 27  76 "Montana"        "Montana"       
29 28  77 "Nebraska"       "Nebraska"      
33 29  78 "Nevada"         "Nevada"        
30 30  79 "New Hampshire"  "New Hampshire"
31 31  80 "New Jersey"     "New Jersey"    
32 32  81 "New Mexico"     "New Mexico"    
34 33  82 "New York"       "New York"      
27 34  83 "North Carolina" "North Carolina"
28 35  84 "North Dakota"   "North Dakota"  
35 36  85 "Ohio"           "Ohio"          
36 37  86 "Oklahoma"       "Oklahoma"      
37 38  87 "Oregon"         "Oregon"        
38 39  88 "Pennsylvania"   "Pennsylvania"  
39 40  89 "Rhode Island"   "Rhode Island"  
40 41  90 "South Carolina" "South Carolina"
41 42  91 "South Dakota"   "South Dakota"  
42 43  92 "Tennessee"      "Tennessee"     
43 44  93 "Texas"          "Texas"         
44 45  94 "Utah"           "Utah"          
46 46  95 "Vermont"        "Vermont"       
45 47  96 "Virginia"       "Virginia"      
47 48  97 "Washington"     "Washington"    
49 49  98 "West Virginia"  "West Virginia"
48 50  99 "Wisconsin"      "Wisconsin"     
50 51 100 ""               ""              
end
label values stateab state
label values state state
label def state 1 "AK", modify
label def state 2 "AL", modify
label def state 3 "AR", modify
label def state 4 "AZ", modify
label def state 5 "CA", modify
label def state 6 "CO", modify
label def state 7 "CT", modify
label def state 8 "DE", modify
label def state 9 "FL", modify
label def state 10 "GA", modify
label def state 11 "HI", modify
label def state 12 "IA", modify
label def state 13 "ID", modify
label def state 14 "IL", modify
label def state 15 "IN", modify
label def state 16 "KS", modify
label def state 17 "KY", modify
label def state 18 "LA", modify
label def state 19 "MA", modify
label def state 20 "MD", modify
label def state 21 "ME", modify
label def state 22 "MI", modify
label def state 23 "MN", modify
label def state 24 "MO", modify
label def state 25 "MS", modify
label def state 26 "MT", modify
label def state 27 "NC", modify
label def state 28 "ND", modify
label def state 29 "NE", modify
label def state 30 "NH", modify
label def state 31 "NJ", modify
label def state 32 "NM", modify
label def state 33 "NV", modify
label def state 34 "NY", modify
label def state 35 "OH", modify
label def state 36 "OK", modify
label def state 37 "OR", modify
label def state 38 "PA", modify
label def state 39 "RI", modify
label def state 40 "SC", modify
label def state 41 "SD", modify
label def state 42 "TN", modify
label def state 43 "TX", modify
label def state 44 "UT", modify
label def state 45 "VA", modify
label def state 46 "VT", modify
label def state 47 "WA", modify
label def state 48 "WI", modify
label def state 49 "WV", modify
label def state 50 "WY", modify
label def state 51 "Alabama", modify
label def state 52 "Alaska", modify
label def state 53 "Arizona", modify
label def state 54 "Arkansas", modify
label def state 55 "California", modify
label def state 56 "Colorado", modify
label def state 57 "Connecticut", modify
label def state 58 "Delaware", modify
label def state 59 "Florida", modify
label def state 60 "Georgia", modify
label def state 61 "Hawaii", modify
label def state 62 "Idaho", modify
label def state 63 "Illinois", modify
label def state 64 "Indiana", modify
label def state 65 "Iowa", modify
label def state 66 "Kansas", modify
label def state 67 "Kentucky", modify
label def state 68 "Louisiana", modify
label def state 69 "Maine", modify
label def state 70 "Maryland", modify
label def state 71 "Massachusetts", modify
label def state 72 "Michigan", modify
label def state 73 "Minnesota", modify
label def state 74 "Mississippi", modify
label def state 75 "Missouri", modify
label def state 76 "Montana", modify
label def state 77 "Nebraska", modify
label def state 78 "Nevada", modify
label def state 79 "New Hampshire", modify
label def state 80 "New Jersey", modify
label def state 81 "New Mexico", modify
label def state 82 "New York", modify
label def state 83 "North Carolina", modify
label def state 84 "North Dakota", modify
label def state 85 "Ohio", modify
label def state 86 "Oklahoma", modify
label def state 87 "Oregon", modify
label def state 88 "Pennsylvania", modify
label def state 89 "Rhode Island", modify
label def state 90 "South Carolina", modify
label def state 91 "South Dakota", modify
label def state 92 "Tennessee", modify
label def state 93 "Texas", modify
label def state 94 "Utah", modify
label def state 95 "Vermont", modify
label def state 96 "Virginia", modify
label def state 97 "Washington", modify
label def state 98 "West Virginia", modify
label def state 99 "Wisconsin", modify
label def state 100 "Wyoming", modify

File 2:

* Example generated by -dataex-. For more info, type help dataex
clear
input long state str14 statestr long state_02 str14(StateAbbr StateAbbr_01)
 1 "Alabama"         . "Alabama"        "Alabama"       
 2 "Alaska"          2 ""               ""              
 3 "Arizona"         3 "Arizona"        ""              
 4 "Arkansas"        4 "Alaska"         "Alaska"        
 5 "California"      5 "California"     ""              
 6 "Colorado"        6 "Colorado"       ""              
 7 "Connecticut"     7 "Connecticut"    ""              
 8 "Delaware"        8 "Delaware"       ""              
 9 "Florida"         9 "Florida"        ""              
10 "Georgia"        10 "Georgia"        ""              
11 "Hawaii"         11 "Hawaii"         ""              
12 "Idaho"          12 "Idaho"          ""              
13 "Illinois"       13 "Illinois"       ""              
14 "Indiana"        14 "Indiana"        ""              
15 "Iowa"           15 "Iowa"           ""              
16 "Kansas"         16 "Kansas"         ""              
17 "Kentucky"       17 "Kentucky"       ""              
18 "Louisiana"      18 "Louisiana"      ""              
19 "Maine"          19 "Maine"          "Maine"         
20 "Maryland"       20 "Maryland"       ""              
21 "Massachusetts"  21 "Massachusetts"  ""              
22 "Michigan"       22 "Michigan"       "Michigan"      
23 "Minnesota"      23 "Minnesota"      "Minnesota"     
24 "Mississippi"    24 "Mississippi"    "Mississippi"   
25 "Missouri"       25 "Missouri"       "Missouri"      
26 "Montana"        26 "Montana"        "Montana"       
27 "Nebraska"       27 "Nebraska"       ""              
28 "Nevada"         28 "Nevada"         ""              
29 "New Hampshire"  29 "New Hampshire"  "New Hampshire"
30 "New Jersey"     30 "New Jersey"     "New Jersey"    
31 "New Mexico"     31 "New Mexico"     "New Mexico"    
32 "New York"       32 "New York"       ""              
33 "North Carolina" 33 "North Carolina" "North Carolina"
34 "North Dakota"   34 "North Dakota"   "North Dakota"  
35 "Ohio"           35 "Ohio"           "Ohio"          
36 "Oklahoma"       36 "Oklahoma"       "Oklahoma"      
37 "Oregon"         37 "Oregon"         "Oregon"        
38 "Pennsylvania"   38 "Pennsylvania"   "Pennsylvania"  
39 "Rhode Island"   39 "Rhode Island"   "Rhode Island"  
40 "South Carolina" 40 "South Carolina" "South Carolina"
41 "South Dakota"   41 "South Dakota"   "South Dakota"  
42 "Tennessee"      42 "Tennessee"      "Tennessee"     
43 "Texas"          43 "Texas"          "Texas"         
44 "Utah"           44 "Utah"           "Utah"          
45 "Vermont"        45 "Vermont"        ""              
46 "Virginia"       46 "Virginia"       ""              
47 "Washington"     47 "Washington"     ""              
48 "West Virginia"  48 "West Virginia"  ""              
49 "Wisconsin"      49 "Wisconsin"      "Wisconsin"     
50 "Wyoming"        50 ""               ""              
end
label values state state
label def state 1 "Alabama", modify
label def state 2 "Alaska", modify
label def state 3 "Arizona", modify
label def state 4 "Arkansas", modify
label def state 5 "California", modify
label def state 6 "Colorado", modify
label def state 7 "Connecticut", modify
label def state 8 "Delaware", modify
label def state 9 "Florida", modify
label def state 10 "Georgia", modify
label def state 11 "Hawaii", modify
label def state 12 "Idaho", modify
label def state 13 "Illinois", modify
label def state 14 "Indiana", modify
label def state 15 "Iowa", modify
label def state 16 "Kansas", modify
label def state 17 "Kentucky", modify
label def state 18 "Louisiana", modify
label def state 19 "Maine", modify
label def state 20 "Maryland", modify
label def state 21 "Massachusetts", modify
label def state 22 "Michigan", modify
label def state 23 "Minnesota", modify
label def state 24 "Mississippi", modify
label def state 25 "Missouri", modify
label def state 26 "Montana", modify
label def state 27 "Nebraska", modify
label def state 28 "Nevada", modify
label def state 29 "New Hampshire", modify
label def state 30 "New Jersey", modify
label def state 31 "New Mexico", modify
label def state 32 "New York", modify
label def state 33 "North Carolina", modify
label def state 34 "North Dakota", modify
label def state 35 "Ohio", modify
label def state 36 "Oklahoma", modify
label def state 37 "Oregon", modify
label def state 38 "Pennsylvania", modify
label def state 39 "Rhode Island", modify
label def state 40 "South Carolina", modify
label def state 41 "South Dakota", modify
label def state 42 "Tennessee", modify
label def state 43 "Texas", modify
label def state 44 "Utah", modify
label def state 45 "Vermont", modify
label def state 46 "Virginia", modify
label def state 47 "Washington", modify
label def state 48 "West Virginia", modify
label def state 49 "Wisconsin", modify
label def state 50 "Wyoming", modify

Basically, it is probably due to messy data management. StateAbbr_01 seems to be a good candidate because it exists in both files, have the same style (full spelling) and same format (string). However, for some reason in File 2 a lot of them showed missing. If you fill them up, then it may work.

Also, file 2 line 4 has an error. There is a line that says it's Alaska and also Arkansas.

[George Ford]

use  statedataaggjune2024, clear
rename StateAbbr_01 statestr
joinby statestr using historyusreligionnewjune2024

[Euslaner]

Thanks everyone.