How to add a column of minimum wage information to each country?

[ian ding]

Dear all:

I have 73 countries microdata (n range from1 to 73), where I intend to add a column of minimum wage information for each country. However some country have one national rate, while some country have multiple rates by province or industry.

pais	country	ccode	year	region	mw
37	Brazil	BRA	2015		788
41	Mexico	MEX	2016		1,899
48	Russia	RUS	2015		7,800
58	China	CHN	2013	1	1,620
58	China	CHN	2013	2	1,580
58	China	CHN	2013	3	1,430

How can I do that? I tried to merge when running each country but does not seem to work at all...

foreach n of numlist 1(1)73 {

use D:\Work\WAGE\DATA_GLOBAL\DATA_GWR1819\GWR6_MASTERD ATA_P`n'.dta

if (`n'>=1 & `n'<=73) {

g code= `n' ?


merge m:m code using `mw' ?


}

}


Best regards,

Ding

[Ashish Tyagi]

Hi Ian,

The problem is not fully clear to me. Would a simple by pais, sort: egen minwage = min(mw) not work here? Perhaps more explanation on what you want to do will help.

[ian ding]

Dear Ashish:

Sorry I was not being very clear. The purpose is to add a variable (MW) for each country. if it is just one country, the code would be gen mw==xxx. However, I need to do this for 73 countries database (GWR6_MASTERD ATA_P1 to GWR6_MASTERD ATA_P73). It is too big to append all 73 country microdata together then merge with the mw table, so my goal is to merge minimum wage with one country each time. (a loop going through one country each time from P1 to P73)

foreach n of numlist 1(1)73 {

use D:\Work\WAGE\DATA_GLOBAL\DATA_GWR1819\GWR6_MASTERD ATA_P`n'.dta

....

}

The issue is that some country have one minimum wage rate, some have different minimum wage rate for each province. I have a table of minimum wage by country by province below,

pais	country	ccode	year	region	mw
37	Brazil	BRA	2015		788
41	Mexico	MEX	2016		1,899
48	Russia	RUS	2015		7,800
58	China	CHN	2013	1	1,620
58	China	CHN	2013	2	1,580
58	China	CHN	2013	3	1,430

and I thought the logic would be merge the minimum wage to each country P"n" through "country" or "ccode", but I don't know how to do it especially when some country have multiple minimum wage rates such as China.

Hope anyone could share some ideas,

Best,

Ding

[William Lisowski]

You do not tell us about the microdata to which you intend to add the minimum wage numbers. Does each observation contain country, year, and region, or does it just contain country and year?

If the latter, you need to figure out what the national minimum wage value should be.

If the former, you need to do two merges. Something like the following untested code may start you in a useful direction.

use mwdata, clear
keep if missing(region)
rename mw mwc
tempfile mwc
save `mwc'
use mwdata, clear
keep if !missing(region)
rename mw mwr
tempfile mwr
save `mwr'
use microdata, clear
merge m:1 country year using `mwc'
drop if _merge==2
merge m:1 country region year using `mwr'
drop if _merge==2
generate mw = cond(missing(region),mwc,mwr)

[ian ding]

Dear William:

Yes, as you said each observation contain country, year, and region. I will try the two merges thank you again for the help!!

Best,

Ding