Back to Blog
Data merge in sas7/13/2023 Finally, using the right commands depending on the software to be used, the files will be merged. In this case, the base file should be the men's questionnaire and the resulting file (unit of analysis) will be the Couples file.Ĥ. This means that not all currently married women have a match with a men's questionnaire. In DHS, men's questionnaires are only applied to a sub-sample of households. If the relationship is that of one to one, the base file is normally the one with the least number of cases.That way, mothers’ characteristics are assigned to children. In the case of matching women and children, the base file should be the children’s file. If the match is done the other way around, once the program matches the first woman it will not look for another woman or it will give an error for finding duplicate cases. The reason is that you may want to assign to every woman the characteristics of her household. For example, if merging data from households and women, the base file should be the women’s file. Normally, when the relationship is that of one to many, the base file is the one with the many entities.The base file establishes the unit of analysis. Sort both data files by the identification variables.ģ. Determine the common identifiers (identification variables).Ģ. For example, to match the household data to the women's data, first rename HV001 to V001 and HV002 to V002, or create a copy of HV001 in V001 and a copy of HV002 in V002 in the household data before merging.Īll statistical packages (SPSS, SAS, STATA) have commands that allow merging files, but regardless of the package the following steps are necessary:ġ. With software that requires the variables that are used for merging to have the same name in both files it will be necessary to either rename or to create copies of the matching variables in one file to match the names in the other file being used. Notice that there is no relationship between children and men because children come from the birth history, which is asked to women. They also can be appended to men, to create couples. from d1 full outer join d2 on d1.indid d2.indid quit I believe that should give all combinations of matching rows. The SAS code needed to merge the files as described above is: PROC SORT OATAONE: BY IO: RUN PROC SORT OATATWO:BY IO: RUN: OATA ONE TWO tllRONE TWO RUN: ‘ This code is simple enough, but it could be made even simpler. Women variables can be appended to their children. 1 If you want to identify the 'correctly' paired rows after the merge, then doing a many-to-many merge now is perhaps your best bet:- proc sql create table out as select d1., d2. This table shows that household variables can be appended to women, men and children. In the cells intersecting the rows and columns, variables from the base files used to match the secondary file are listed. In the columns, the secondary files along with the variables to be used as keys or matching variables are listed. The following reference table shows the variables required to match different files. The files can be more easily merged using variables HV001 with V001 and HV002 with V002. For example, it is not possible to merge the household and women’s files using HHID and CASEID because CASEID has three extra characters identifying the women’s line number. Next, we provide an example to do a left join with the MERGE statement.When merging files it is generally easier to use the original variables rather than the ID variables. Namely, if you want to perform a left join on two or more large tables, the MERGE statement is faster than the SQL procedure. For example, you can’t merge two tables with the column names first_name and firstname.Īll these extra requirements come with a benefit. You can do this with the PROC SORT procedure.Īdditionally (and also in contrast to the PROC SQL procedure), the column names to find matching records must be identical. In contrast to the PROC SQL procedure, the MERGE statement requires that the input tables must be ordered. For example, that you want to keep all records from the left table plus the matching records from the right table. The IF statement specifies the type of join. After the MERGE statement follows a BY statement to specify the columns you want to match.įinally, to create a (left) join, you need an IF statement. In a SAS Data Step, you start a join with the MERGE statement followed by the names of the tables you want to combine. METHOD 2: Left Join Table with a SAS Data StepĪlternatively, you can use a SAS Data Step to join data from two or more data sets.
0 Comments
Read More
Leave a Reply. |