Step 2 : Extraction & Merging NSSO datasets using few simple steps using the Stata software package
(A) Understand the folder and files you get in the Unit Level Record data CD bought from NSSO
Here, we will use the Unit Level 68th round Employment & Unemployment Data (2012-13) Schedule Type I data given in the folder called Nss68_1.0_Type1..,the contents are as below,
The most important files are the data files and what it contains, which is explained very nicely in the Readme file, as below,
Next is the multiplier file, which explains the basis behind multipliers assigned to each household, which is nothing but sampling weights (we had explained in the earlier posts how that was derived).
But, the most impotant files, which are the key to your work for extracting data are provided in the excel table called Layout68_1_0typ1.XLS (see the first figure)..as b below, you may like to understand these thoroughly before you start your work. We will guide you in every step here..
(B) Understand the levels of data presented an create a common household ID and also a person ID; use Stata codes for extraction and merging as given below,
/*Our
strategy would be to extract our relevant data in Stata file format, thus for
each level we would like to have a .dta file. For not causing any confusion, we
will create the dictionary file .dct with the same name as the .dta file to be
created later. The .dct file creation will take into account firstly the
relevant variables to create the HOUSEHOLD ID or PERSON ID as the case may be
and then other variables which are of interest. Choose and give names to the
variables you require as given in the LAYOUT table (take a printout). In the case below, we jump to the Level2 file
as the HHID can also be created from there (instead from Level1) and
additionally other variables can also be created*/
/*
CREATING THE HH_CHARS.DTA FILE*/
/*
Ist Step: Create the hh_chars.dct for household details as below by saving it
with the same name. Use Stata Do-file editor for this and save it as desired.
This essentially informs Stata about a data structure of a database, from which
data from your chosen specific fields needs to be extracted */
infix dictionary
{
str
fsu 4-8
str
hg 32-32
str
sss 33-33
str
hhno 34-35
str
state 16-17
str
sector 15-15
hh_size
43-44
str prin 45-49
str proc 50-52
str hh_type 53-53
str
religion 54-54
str
social_group 55-55
land_own 56-63
land_pos 64-71
land_cult 72-79
bank 83-83
str
level 36-37
nss
127-129
nsc
130-132
mlt
133-142
}
/*
IInd Step: Create the hh_chars.dta as below from Level2. You are asking Stata
to extract data from the Level2 file from the specific fields to be created
from byte positions given in the .dct file */
infix
using "D:\NSS\EU68\hh_chars.dct", using
("D:\NSS\EU68\R6810L02.txt")
browse
/*Most
important code below for fixing identity of Households in all level files for
all times! All level files will have these variables compulsorily within Common
Items 1-35 in the same position and have the same number of bytes. You must
generate it every time for every level file you want to use for maintaining
common identity of the household!*/
gen
hhid= fsu+ hg+ sss+ hhno
brow
gen
comb_wt = mlt /100 if nss == nsc
replace
comb_wt= mlt/200 if nss != nsc
brow
*
CHECK FOR DUPLICATION IN hhid
isid
hhid
*
CHECK THE NUMBER OF HOUSEHOLD RECORDS IN DATASET
count
*
CALCULATE THE ESTIMATED NUMBER OF HOUSEHOLDS
*
tabstat comb_wt, statistics( sum ) format(%14.0g)
tabstat
comb_wt, statistics( sum ) format(%14.0f)
destring
sector, gen(sector_nu)
label
define lblSector 1 "Rural" 2 "Urban"
label
values sector_nu lblSector
/*
PERCENTAGE DISTRIBUTION OF HOUSEHOLDS BY SECTOR IN THE SAMPLE (WITHOUT
ATTACHING COMBINED WEIGHTS)*/
tab
sector_nu
/*
PERCENTAGE DISTRIBUTION OF HOUSEHOLDS IN THE POPULATION BY SECTOR (i.e.
ATTACHING COMBINED WEIGHTS)*/
tabulate
sector_nu [fweight = round(comb_wt)]
save
"D:\NSS\EU68\hh_chars.dta"
clear
all
**********************************************************************************
/*
CREATING THE PS_CHARS.DTA FILE*/
*
1st Step: Create the ps_chars.dct for personal details as below by saving it
with the same name
infix dictionary
{
str
fsu 4-8
str
hg 32-32
str
sss 33-33
str
hhno 34-35
str
psno 41-42
str
relhd 43-43
str
sex 44-44
str
age 45-47
str
mar 48-48
str
genedu 49-50
str
tecedu 51-52
str
eduatt 53-54
str
vt 57-57
str
ft 58-59
str
mgn 60-60
str
mgnw 61-61
nss
127-129
nsc
130-132
mlt
133-142
}
*
IInd Step: Create the ps_chars.dta as below from Level3
infix
using "D:\NSS\EU68\ps_chars.dct", using ("D:\NSS\EU68\R6810L03.txt")
browse
*Generate
HHID for household identification & PSID for person identification
gen
hhid= fsu+ hg+ sss+ hhno
gen
psid= hhid+psno
brow
gen
comb_wt = mlt /100 if nss == nsc
replace
comb_wt= mlt/200 if nss != nsc
br
*
CHECK FOR DUPLICATION IN HHID it should throw warning that it is not unique
since for each person the household is same
isid
hhid
*
CHECK FOR DUPLICATION IN PSID it should throw no warning
isid
psid
*
CHECK THE NUMBER OF PERSON RECORDS IN DATASET
/*This
should tally with the Readme_68_10 file for number of records in that level
from where you are extracting data*/
count
*
CALCULATE THE ESTIMATED NUMBER OF PERSONS
*
tabstat comb_wt, statistics( sum ) format(%14.0g)
tabstat
comb_wt, statistics( sum ) format(%14.0f)
save
"D:\NSS\EU68\ps_chars.dta"
clear
all
**********************************************************************************/*
CREATING THE PS_PREF.DTA FILE*/
/*
1st Step: Create the ps_pref.dct for personal details as below by saving it
with the same name*/
infix dictionary
{
str
fsu 4-8
str
hg 32-32
str
sss 33-33
str
hhno 34-35
str sector 15-15
str state 16-18
str
psno 41-42
str
age 43-45
str
dom 46-46
str
domres 47-47
str
dompur 48-48
str
hhind 50-50
str
coll 52-52
str
dung 58-58
str
tail 59-59
str
watdist 63-65
str
w_will 66-66
str
w_nat 67-67
str
w_type 68-68
str
w_skill 69-69
str
w_asst 70-70
nss
127-129
nsc
130-132
mlt
133-142
}
*
IInd Step: Create the ps_pref.dta as below from Level8
infix
using "D:\NSS\EU68\ps_pref.dct", using
("D:\NSS\EU68\R6810L08.txt")
browse
*Generate
HHID for household identification & PSID for person identification
gen
hhid= fsu+ hg+ sss+ hhno
gen
psid= hhid+psno
brow
gen
comb_wt = mlt /100 if nss == nsc
replace
comb_wt= mlt/200 if nss != nsc
br
/*
CHECK FOR DUPLICATION IN HHID it should throw warning that it is not unique
since for each person the household is same*/
isid
hhid
*
CHECK FOR DUPLICATION IN PSID it should throw no warning
isid
psid
*
CHECK THE NUMBER OF PERSON RECORDS IN DATASET
/*This
should tally with the Readme_68_10 file for number of records in that level
from where you are extracting data*/
count
*
CALCULATE THE ESTIMATED NUMBER OF PERSONS
*
tabstat comb_wt, statistics( sum ) format(%14.0g)
tabstat
comb_wt, statistics( sum ) format(%14.0f)
save
"D:\NSS\EU68\ps_pref.dta"
clear
all
*************************************************************************************
/*
CREATING THE HH_CON.DTA FILE*/
/*
1st Step: Create the hh_con.dct for household consumption expenditure details
as below by saving it with the same name*/
infix dictionary
{
str
fsu 4-8
str
hg 32-32
str
sss 33-33
str
hhno 34-35
str
item 41-42
str
con30 43-50
str
con365 51-58
nss 127-129
nsc
130-132
mlt
133-142
}
*
IInd Step: Create the hh_con.dta as below from Level9
infix
using "D:\NSS\EU68\hh_con.dct", using
("D:\NSS\EU68\R6810L09.txt")
browse
*Generate
HHID for household identification
gen
hhid= fsu+ hg+ sss+ hhno
*Generate
ITEMHHID for household & its item consumption *identification
gen
itemhhid = hhid+item
brow
gen
comb_wt = mlt /100 if nss == nsc
replace
comb_wt= mlt/200 if nss != nsc
br
*
CHECK FOR DUPLICATION IN HHID, there should be warning
isid
hhid
*
CHECK FOR DUPLICATION IN ITEMHHID no warning
isid
itemhhid
*
CHECK THE NUMBER OF ITEMS & HH COMBO RECORDS IN DATASET
*This
should tally with the Readme_68_10 file for number of records in that level
from where you are extracting data
count
*
CALCULATE THE ESTIMATED NUMBER OF ITEM&HH COMBINATIONS
*
tabstat comb_wt, statistics( sum ) format(%14.0g)
tabstat
comb_wt, statistics( sum ) format(%14.0f)
save
"D:\NSS\EU68\hh_con.dta"
clear
all
*************************************************************
/*
CREATING THE PS_ACT.DTA FILE*/
*
1st Step: Create the ps_act.dct for person activity details as below by saving
it with the same name
infix dictionary
{
str
fsu 4-8
str
hg 32-32
str
sss 33-33
str
hhno 34-35
str
psno 41-42
str
age2 43-45
str
pracstat 46-47
str
pracnic 48-52
str
pracnco 53-55
str
ssac 56-56
str
sawork 66-66
nss 127-129
nsc
130-132
mlt
133-142
}
*
IInd Step: Create the ps_act.dta as below from Level4
infix
using "D:\NSS\EU68\ps_act.dct", using
("D:\NSS\EU68\R6810L04.txt")
browse
*Generate
HHID for household identification
gen
hhid= fsu+ hg+ sss+ hhno
*Generate
PSID for person identification
gen
psid = hhid+psno
brow
gen
comb_wt = mlt /100 if nss == nsc
replace
comb_wt= mlt/200 if nss != nsc
br
*
CHECK FOR DUPLICATION IN HHID, there should be warning
isid
hhid
*
CHECK FOR DUPLICATION IN PSID no warning
isid
psid
*
CHECK THE NUMBER OF PERSON RECORDS IN DATASET
*This
should tally with the Readme_68_10 file for number of records in that level
from where you are extracting data
count
*
CALCULATE THE ESTIMATED NUMBER OF ITEM&HH COMBINATIONS
*
tabstat comb_wt, statistics( sum ) format(%14.0g)
tabstat
comb_wt, statistics( sum ) format(%14.0f)
save
"D:\NSS\EU68\ps_act.dta"
clear
all
*************************************************************
/*
CREATING THE PS_SACT.DTA FILE*/
*
1st Step: Create the ps_sact.dct for person subsidiary activity details as
below by saving it with the same name
infix dictionary
{
str
fsu 4-8
str
hg 32-32
str
sss 33-33
str
hhno 34-35
str
psno 41-42
str
age3 43-45
str
pracstat 46-47
str
pracnic 48-52
str
pracnco 53-55
str
loct 56-57
str
entype 58-58
str
enelec 59-59
str
enworker 60-60
str
jobtyp 61-61
str
elpl 62-62
str
elss 63-63
str
pay 64-64
nss
127-129
nsc
130-132
mlt
133-142
}
*
IInd Step: Create the ps_sact.dta as below from Level5
infix
using "D:\NSS\EU68\ps_sact.dct", using
("D:\NSS\EU68\R6810L05.txt")
browse
*Generate
HHID for household identification
gen
hhid= fsu+ hg+ sss+ hhno
*Generate
PSID for person identification
gen
psid = hhid+psno
brow
gen
comb_wt = mlt /100 if nss == nsc
replace
comb_wt= mlt/200 if nss != nsc
br
*
CHECK FOR DUPLICATION IN HHID, there should be warning
isid
hhid
*
CHECK FOR DUPLICATION IN PSID no warning
isid
psid
*
CHECK THE NUMBER OF PERSON RECORDS IN DATASET
*This
should tally with the Readme_68_10 file for number of *records in that level
from where you are extracting data
count
*
CALCULATE THE ESTIMATED NUMBER PERSONS WITH SUBSIDIARY *ACTIVITY
*
tabstat comb_wt, statistics( sum ) format(%14.0g)
tabstat
comb_wt, statistics( sum ) format(%14.0f)
save
"D:\NSS\EU68\ps_sact.dta"
clear
all
*************************************************************************************
/*Merging process of the person
files with the household files, however there has to be a common code for
merging 2 files. Remember that many persons may belong to ONE household!
Therefore we will have a M:1 (Many to ONE) RELATIONSHIP between Person records
& Household records viz. one household record may be associated with many person
records as they belong to the same household or family*/
/*
1st Step: Call the "Many" file
first, in our case, its lets say, ps_chars.dta and sort it on the common code,
which is hhid & save
use
"D:\NSS\EU68\ps_chars.dta"
sort
hhid
save "D:\NSS\EU68\ps_chars.dta"
/*
2nd Step: Call the "One" file next its lets say, hh_chars.dta and
sort it on the common code which is also hhid & save
use
"D:\NSS\EU68\hh_chars.dta"
sort
hhid
save "D:\NSS\EU68\hh_chars.dta"
/*
3rd Step: Now issue the merge command
for merging the sorted personal file
with the household file in M:1 manner.
/*First
call the "Many file"*/
use
"D:\NSS\EU68\ps_chars.dta"
/*Now
call the the "One" file and issue merge command on common code hhid.
The merged dataset will have all records of "Many" file
and
duplications of the "One" file*/
merge
m:1 hhid using "D:\NSS\EU68\hh_chars.dta"
save
"D:\NSS\EU68\pshh_chars.dta"
