NHANES Dietary Web Tutorial: Merge & Append Dataset: Example 4

*-------------------------------------------------------------------------;
* Use the PROC SORT procedure and BY statement to sort the supplement 4   ;
* file (DSQ4_c). Merge this file with the SUP123 file from the previous ;
* step by using the PROC SQL procedure and the unique identifier,         ;
* DSDSUPID. This merge will create a dataset called ALLSUP by adjoining ;
* values from the supplement 4 file to the supplement 123 file, regardless;
* of the number of observations for DSDQUPID in each file.                ;
*                                                                         ;
* For more information on PROC SQL you may want to consult the SAS manual ;
*-------------------------------------------------------------------------;

proc sort data =NH.DSQ4_C out <=DSQ4_C;
by DSDSUPID;
run ;

proc sql ;
    create table ALLSUP as
        select *
        from DSQ4_C as H,
        SUP123 as M
        where H.DSDSUPID=M.DSDSUPID;
run ;

*-------------------------------------------------------------------------;
* Use the PROC CONTENTS procedure to list the contents of the SUP123      ;
* dataset — the new merged dataset. Use the VARNUM option to list the    ;
* variables according to their position in the dataset.                   ;
*                                                                         ;
* Use the PROC MEANS procedure to determine the number of non-missing     ;
* values (N), number of missing values (Nmiss), minimum (min) and maximum ;
*(max) values, to two decimal places (maxdec=2), for the variables in the ;
* merged dataset.                                                         ;
*-------------------------------------------------------------------------;

proc contents
data = ALLSUP
varnum ;

proc means data = ALLSUP N Nmiss min max maxdec = 2 ;
run ;

The SAS System

The CONTENTS Procedure

Data Set Name        WORK.ALLSUP                   Observations          84489
Member Type          DATA                          Variables             31
Engine               V9                            Indexes               0
Created              Friday, March 28,             Observation Length    248
                     2008 09:48:37 AM
Last Modified        Friday, March 28,             Deleted Observations 0
                     2008 09:48:37 AM
Protection                                         Compressed            NO
Data Set Type                                      Sorted                NO
Label
Data Representation WINDOWS_32
Encoding             wlatin1 Western (Windows)

                      Engine/Host Dependent Information

Data Set Page Size          16384
Number of Data Set Pages    1301
First Data Page             1
Max Obs per Page            65
Obs in First Data Page      46
Number of Data Set Repairs 0
File Name                   C:\DOCUME~1\evo2\LOCALS~1\Temp\SAS Temporary
                            Files\_TD1520\allsup.sas7bdat
Release Created             9.0101M3
Host Created                XP_PRO

The SAS System

The CONTENTS Procedure

Variables in Creation Order

   #    Variable    Type    Len    Label
   1    DSDSUPID    Char     10    Supplement ID number
   2    DSDSUPP     Char     11    Supplement name
   3    DSDINGID    Char      8    Ingredient ID number
   4    DSDINGR     Char      9    Ingredient name
   5    DSDOPER     Char      2    Ingredient operator
   6    DSDQTY      Num       8    Ingredient quantity
   7    DSDUNIT     Num       8    Ingredient unit
   8    DSDCAT      Num       8    Ingredient category
   9    DSDBLFLG    Num       8    Blend flag
10    SEQN        Num       8    Respondent sequence number
11    DSD010      Num       8    Any Dietary Supplements Taken?
12    DSD010AN    Num       8    Any Antacids Taken?
13    DSDCOUNT    Num       8    Total # of Dietary Supplements Taken
14    DSDANCNT    Num       8    Total # of Antacids Taken
15    DSD070      Num       8    Was container seen?
16    DSDMTCH     Num       8    Matching code
17    DSD090      Num       8    How Long supplement taken (days)?
18    DSD103      Num       8    Days Supplement taken, past 30 days
19    DSD122Q     Num       8    Quantity of supplement taken daily
20    DSD122U     Num       8    Dosage form
21    DSDANTA     Num       8    Antacid reported as a dietary supplement
22    DSDSRCE     Num       8    Supplement information source
23    DSDTYPE     Num       8    Formulation type
24    DSDSERVQ    Num       8    Serving size quantity
25    DSDSERVU    Num       8    Serving size unit
26    DSDSERVA    Num       8    Alternative serving size
27    DSDCNTV     Num       8    Count of vitamins in the supplement
28    DSDCNTM     Num       8    Count of minerals in the supplement
29    DSDCNTA     Num       8    Count of amino acids in the supplement
30    DSDCNTB     Num       8    Count of botanicals in the supplement
31    DSDCNTO     Num       8    Count of other ingredients in supplement

The SAS System

The MEANS Procedure

                                                               N
Variable Label                                         N   Miss       Minimum     Maximum
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
DSDQTY    Ingredient quantity                       84489      0          0.00     50000.00
DSDUNIT   Ingredient unit                           84489      0          1.00        31.00
DSDCAT    Ingredient category                       84050    439          1.00         5.00
DSDBLFLG Blend flag                                84489      0          1.00         2.00
SEQN      Respondent sequence number                84489      0      21010.00     31126.00
DSD010    Any Dietary Supplements Taken?            84489      0          1.00         2.00
DSD010AN Any Antacids Taken?                       84489      0          1.00         2.00
DSDCOUNT Total # of Dietary Supplements Taken      84489      0          0.00        20.00
DSDANCNT Total # of Antacids Taken                 84489      0          0.00         3.00
DSD070    Was container seen?                       84489      0          1.00         2.00
DSDMTCH   Matching code                             84489      0          1.00         5.00
DSD090    How Long supplement taken (days)?         84489      0          1.00   9999999.00
DSD103    Days Supplement taken, past 30 days       83307   1182          1.00      9999.00
DSD122Q   Quantity of supplement taken daily        83720    769          0.05    999999.00
DSD122U   Dosage form                               83716    773          1.00        30.00
DSDANTA   Antacid reported as a dietary supplement 84489      0          0.00         2.00
DSDSRCE   Supplement information source             69770 14719          1.00        10.00
DSDTYPE   Formulation type                          84489      0          1.00         4.00
DSDSERVQ Serving size quantity                     84489      0          0.25        44.00
DSDSERVU Serving size unit                         84489      0          1.00        99.00
DSDSERVA Alternative serving size                   4154 80335          1.00        80.00
DSDCNTV   Count of vitamins in the supplement       84489      0          0.00        16.00
DSDCNTM   Count of minerals in the supplement       84489      0          0.00        71.00
DSDCNTA   Count of amino acids in the supplement    84489      0          0.00        22.00
DSDCNTB   Count of botanicals in the supplement     84489      0          0.00        60.00
DSDCNTO   Count of other ingredients in supplement 84489      0          0.00        35.00
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

What you will see when you review the output from this program:

The dataset, ALLSUP, contains 84,489 observations. Notice that the number of observations is greater than the number of participants because the SUP123 and Supplement 4 Files both contain more than one record per supplement in most cases.
Using the variable list provided by the PROC CONTENTS procedure, check that the variables in your original datasets are in the merged dataset. In this case, they all are.
The N column has the number of observations with non-missing data for each variable in the dataset.
The N miss column has the number of missing observations for each variable in the dataset.
The minimum column lists the minimum value for each variable in the dataset.
The maximum column lists the maximum value for each variable in the dataset.

Click here to close program output and highlights

Example 4 - Many-to-many merge using supplement ID number as the unique identifier

Program to Merge and Check Contents of Data

Sample Code

Output of Program