Merge Data - housing burden + Wharton Index

[juan herrera]

Dear all,
I am trying to merge data on the housing burden and the Wharton index. Both datasets include a geo ID that is composed of State, county, and place code, as: 32(state)108(county)123445(place). I noticed my variables in the Wharton dataset are not following the same units, for the place, if the place # does not have six units, they did not include zeros to keep the format.

The data looks like this:

Datafile 1:

Datafile 2:

I am struggling with this so I appreciate any suggestion to make both geoid a number of length 11.

I am sorry if this is not the best way to address this question. Thank you.

[Andrew Musau]

Please use dataex in the future to present data examples (refer to FAQ Advice #12). In the Wharton dataset, create the identifier as follows:

gen strfips= cond(length(string(fipsplace))<6, "00000"+string(fipsplace), string(fipsplace))
replace strfips= substr(strfips,-6, 6)
gen double geoid_r= real(substr(string(GEOID), 1, 5)+ strfips)
format geoid_r %13.0f

[juan herrera]

Please use dataex in the future to present data examples (refer to FAQ Advice #12). In the Wharton dataset, create the identifier as follows:

gen strfips= cond(length(string(fipsplace))<6, "00000"+string(fipsplace), string(fipsplace))
replace strfips= substr(strfips,-6, 6)
gen double geoid_r= real(substr(string(GEOID), 1, 5)+ strfips)
format geoid_r %13.0f

Mr. Musau, thank you very much and apologies for not following the guideline. Now I am aware of them. I just sent you a private message as the suggestion you gave me is not giving me what I expected, I might have explained myself incorrectly.

[Andrew Musau]

Please post a sample of your data using dataex and explain what the issue is here. Copy and paste the result of the following from the Wharton dataset.

dataex in 1/10

[William Lisowski]

juan herrera -

Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question.

Along with learning about effective presentation of your problems, you will learn from #15 that Statalist is based on public discussion of problems to preserve answers for others to learn from, and to allow multiple members to assist in responding to a single question. And from the questioner's perspective, since Statalist is worldwide, it's possible to have a new respondent answer a followup while the original respondent is gone from their computer until morning in their time zone.

[juan herrera]

[QUOTE=Andrew Musau;n1549631]Please post a sample of your data using dataex and explain what the issue is here. Copy and paste the result of the following from the Wharton dataset.

dataex in 1/10

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 GEOID float statecode int countycode18 long fipsplacecode18
"2501365825" 25 13 65825
"2502701885" 25 27  1885
"2501702130" 25 17  2130
"2502706365" 25 27  6365
"2501768050" 25 17 68050
"2500943580" 25  9 43580
"2500538225" 25  5 38225
"2502350145" 25 23 50145
"2502167945" 25 21 67945
"2502327795" 25 23 27795
end

[Andrew Musau]

Thanks for the data example. The assumption was that GEOID is numeric which emphasizes the importance of presenting data examples using the dataex command.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 GEOID float statecode int countycode18 long fipsplacecode18
"2501365825" 25 13 65825
"2502701885" 25 27  1885
"2501702130" 25 17  2130
"2502706365" 25 27  6365
"2501768050" 25 17 68050
"2500943580" 25  9 43580
"2500538225" 25  5 38225
"2502350145" 25 23 50145
"2502167945" 25 21 67945
"2502327795" 25 23 27795
end
gen strfips= cond(length(string(fipsplace))<6, "00000"+string(fipsplace), string(fipsplace))
replace strfips= substr(strfips,-6, 6)
gen double geoid_r= real(substr(GEOID, 1, 5)+ strfips)
format geoid_r %13.0f

Res.:

l, sep(10)

     +---------------------------------------------------------------------+
     |      GEOID   statec~e   count~18   fipsp~18   strfips       geoid_r |
     |---------------------------------------------------------------------|
  1. | 2501365825         25         13      65825    065825   25013065825 |
  2. | 2502701885         25         27       1885    001885   25027001885 |
  3. | 2501702130         25         17       2130    002130   25017002130 |
  4. | 2502706365         25         27       6365    006365   25027006365 |
  5. | 2501768050         25         17      68050    068050   25017068050 |
  6. | 2500943580         25          9      43580    043580   25009043580 |
  7. | 2500538225         25          5      38225    038225   25005038225 |
  8. | 2502350145         25         23      50145    050145   25023050145 |
  9. | 2502167945         25         21      67945    067945   25021067945 |
 10. | 2502327795         25         23      27795    027795   25023027795 |
     +---------------------------------------------------------------------+

[juan herrera]

Thanks for the data example. The assumption was that GEOID is numeric which emphasizes the importance of presenting data examples using the dataex command.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 GEOID float statecode int countycode18 long fipsplacecode18
"2501365825" 25 13 65825
"2502701885" 25 27 1885
"2501702130" 25 17 2130
"2502706365" 25 27 6365
"2501768050" 25 17 68050
"2500943580" 25 9 43580
"2500538225" 25 5 38225
"2502350145" 25 23 50145
"2502167945" 25 21 67945
"2502327795" 25 23 27795
end
gen strfips= cond(length(string(fipsplace))<6, "00000"+string(fipsplace), string(fipsplace))
replace strfips= substr(strfips,-6, 6)
gen double geoid_r= real(substr(GEOID, 1, 5)+ strfips)
format geoid_r %13.0f

Res.:

l, sep(10)

+---------------------------------------------------------------------+
| GEOID statec~e count~18 fipsp~18 strfips geoid_r |
|---------------------------------------------------------------------|
1. | 2501365825 25 13 65825 065825 25013065825 |
2. | 2502701885 25 27 1885 001885 25027001885 |
3. | 2501702130 25 17 2130 002130 25017002130 |
4. | 2502706365 25 27 6365 006365 25027006365 |
5. | 2501768050 25 17 68050 068050 25017068050 |
6. | 2500943580 25 9 43580 043580 25009043580 |
7. | 2500538225 25 5 38225 038225 25005038225 |
8. | 2502350145 25 23 50145 050145 25023050145 |
9. | 2502167945 25 21 67945 067945 25021067945 |
10. | 2502327795 25 23 27795 027795 25023027795 |
+---------------------------------------------------------------------+

Thank you very much!