{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Merging Data with PROC SQL\n",
"\n",
"`PROC SQL` is the most useful and versatile SAS procedure. When you use `PROC SQL`, SAS can leverage some of the efficiencies of SQL processing, which can improve the processing time of your queries. The flexibility and specificity of SAS SQL statements are often preferable to the SAS provided alternatives. \n",
"\n",
"`PROC SQL` steps are generally of the form:\n",
"\n",
"```sas\n",
"proc sql; \n",
" (create table NEW_TABLE as)\n",
" select VARIABLES\n",
" from SOURCES \n",
" (group by KEYS)\n",
" (order by KEYS)\n",
" (where);\n",
"quit;\n",
"```\n",
"\n",
"The statements contained in parentheses are optional. When you write your `PROC SQL` statements, do not include the parentheses. If you omit the `create table` line, the result of you `PROC SQL` step will be written to the log but no data will be written to the disk. The optional `group by` and `order by` lines instruct SAS to collapse the data to a single row by the specified keys and sort the data by the specified keys, respectively. "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Merging data in SAS using `PROC SQL` `JOINS`\n",
"\n",
"Generally for our purposes, when we want to merge together two datasets in SAS we use `JOIN` commands in a `PROC SQL` step. All SQL joins are akin to many-to-many joins implemented by the `joinby` function in Stata — these joins are not on their face equivalent to `1:1`, `1:m`, or `m:1` joins unless you ensure that the tables you are joining are unique on the join variables yourself.\n",
"\n",
"Conceptually, SQL joins involve a \"left\" table and one or more \"right\" tables to be joined. All joins are *Cartesian products* made on the the specified merge key variables. This means if there are two duplicate matching keys in the left table and two duplicate matching keys in the right table, there will be four duplicate keys in the output dataset. Another way to phrase this is that each observation is assigned all of the rows that it matches to. \n",
"\n",
"The most common join we use is the `LEFT JOIN` but all types are outlined below\n",
"\n",
"### LEFT JOIN\n",
"\n",
"![image](left_join.png \"LEFT JOIN\")\n",
"\n",
"A `LEFT JOIN` selects all the obervations in the left table regardless of their key values but only selects observations with matching key values from the right table. Variables from the right table in rows where there is no match are assigned missing values. A `LEFT JOIN` is **not** commutative (i.e. reversing the order of the data sets can produce different results).\n",
"\n",
"#### Example\n",
"\n",
"In this example, we merge on `state` from `out.kid_geos` using a `LEFT JOIN` onto `in.fake_micro` and save the resulting dataset in `out.left_join_example`. \n",
"\n",
"Here, we use the `select` statement to only keep certain variables from each dataset, labeled `A` or `B`. These labels are assignedbelow in the `from` and `left join` statements.\n",
"\n",
"The merge key here is `pik` and we require that `pik` is not null for the left table (`A`).\n",
"\n",
"```sas\n",
"\n",
"proc sql; \n",
" create table out.fake_micro_with_geo as\n",
" select A.pik, A.mom_pik, A.dad_pik, B.state\n",
" from out.fake_micro as A\n",
" left join out.kid_geos as B\n",
" on A.pik = B.pik and A.pik is not null;\n",
"quit;\n",
"\n",
"```\n",
"\n",
"The output data set `out.left_join_example` is:"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"popout"
]
},
"source": [
"```{margin} Note\n",
"The rows with missing `state` are rows that did not match to the right data set, they remain in the output data set but are assigned missing values of `state`.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"tags": [
"remove_input"
]
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>pik</th>\n",
" <th>mom_pik</th>\n",
" <th>dad_pik</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" <td>034</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>002</td>\n",
" <td>NaN</td>\n",
" <td>117</td>\n",
" <td>48.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>003</td>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" <td>81.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>004</td>\n",
" <td>NaN</td>\n",
" <td>101</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>005</td>\n",
" <td>NaN</td>\n",
" <td>058</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>006</td>\n",
" <td>010</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>007</td>\n",
" <td>172</td>\n",
" <td>NaN</td>\n",
" <td>1.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>008</td>\n",
" <td>NaN</td>\n",
" <td>017</td>\n",
" <td>37.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>009</td>\n",
" <td>NaN</td>\n",
" <td>029</td>\n",
" <td>2.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>010</td>\n",
" <td>NaN</td>\n",
" <td>142</td>\n",
" <td>38.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" pik mom_pik dad_pik state\n",
"0 001 NaN 034 24.0\n",
"1 002 NaN 117 48.0\n",
"2 003 001 NaN 81.0\n",
"3 004 NaN 101 24.0\n",
"4 005 NaN 058 NaN\n",
"5 006 010 NaN NaN\n",
"6 007 172 NaN 1.0\n",
"7 008 NaN 017 37.0\n",
"8 009 NaN 029 2.0\n",
"9 010 NaN 142 38.0"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pandas as pd\n",
"\n",
"df = pd.read_sas(\"../data/left_join_example.sas7bdat\", encoding=\"latin-1\");\n",
"df.head(n=10)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### RIGHT JOIN\n",
"\n",
"![image](right_join.png \"RIGHT JOIN\")\n",
"\n",
"A `RIGHT JOIN` selects all the observations in the right table. When observations do not match to the left table the key values are set to missing. A `RIGHT JOIN`, like a `LEFT JOIN`, is not commutative and is **not** the same as reversing the datasets in a `LEFT JOIN`.\n",
"\n",
"#### Example\n",
"\n",
"In the example below, we have the same setup as in the `LEFT JOIN` example, except that we use a `RIGHT JOIN`. \n",
"\n",
"**Note:** `out.kid_geos` is a subset of `out.fake_micro` so `pik` will never be missing in the output data set since all the keys in `out.kid_geos` appear in `out.fake_micro`.\n",
"\n",
"```sas\n",
"proc sql; \n",
" create table out.fake_micro_with_geo as\n",
" select A.pik, A.mom_pik, A.dad_pik, B.state\n",
" from out.fake_micro as A\n",
" right join out.kid_geos as B\n",
" on A.pik = B.pik and B.pik is not null;\n",
"quit;\n",
"\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{margin} Note \n",
"Unlike in the example above, `state` is never missing because the `RIGHT JOIN` restricts to observations that appear in `out.kid_geos`, where `state` is never missing. Note that `pik = 005` and `pik = 006` are missing now (see the `LEFT JOIN` output), this is because they do not appear in `out.kid_geos`.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"tags": [
"remove_input"
]
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>pik</th>\n",
" <th>mom_pik</th>\n",
" <th>dad_pik</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" <td>034</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>002</td>\n",
" <td>NaN</td>\n",
" <td>117</td>\n",
" <td>48.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>003</td>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" <td>81.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>004</td>\n",
" <td>NaN</td>\n",
" <td>101</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>007</td>\n",
" <td>172</td>\n",
" <td>NaN</td>\n",
" <td>1.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>008</td>\n",
" <td>NaN</td>\n",
" <td>017</td>\n",
" <td>37.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>009</td>\n",
" <td>NaN</td>\n",
" <td>029</td>\n",
" <td>2.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>010</td>\n",
" <td>NaN</td>\n",
" <td>142</td>\n",
" <td>38.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>012</td>\n",
" <td>NaN</td>\n",
" <td>126</td>\n",
" <td>61.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>013</td>\n",
" <td>NaN</td>\n",
" <td>180</td>\n",
" <td>3.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" pik mom_pik dad_pik state\n",
"0 001 NaN 034 24.0\n",
"1 002 NaN 117 48.0\n",
"2 003 001 NaN 81.0\n",
"3 004 NaN 101 24.0\n",
"4 007 172 NaN 1.0\n",
"5 008 NaN 017 37.0\n",
"6 009 NaN 029 2.0\n",
"7 010 NaN 142 38.0\n",
"8 012 NaN 126 61.0\n",
"9 013 NaN 180 3.0"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_sas(\"../data/right_join_example.sas7bdat\", encoding=\"latin-1\");\n",
"df.head(n=10)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### INNER JOIN\n",
"\n",
"![image](inner_join.png \"INNER JOIN\")\n",
"\n",
"An `INNER JOIN` selects all the observations that have keys in **both** the left and the right data set. \n",
"An `INNER JOIN` is a logical AND between the two tables. Unlike a `RIGHT JOIN` or a `LEFT JOIN`, an `INNER JOIN` is commutative \n",
"so tables can be joined in either order. \n",
"\n",
"#### Example\n",
"\n",
"In the example below, we have the same setup as in the previous examples, except that we use an `INNER JOIN`. \n",
"\n",
"```sas\n",
"proc sql; \n",
" create table out.fake_micro_with_geo as\n",
" select A.pik, A.mom_pik, A.dad_pik, B.state\n",
" from out.fake_micro as A\n",
" inner join out.kid_geos as B\n",
" on A.pik = B.pik and B.pik is not null;\n",
"quit;\n",
"\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"tags": [
"remove_input"
]
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>pik</th>\n",
" <th>mom_pik</th>\n",
" <th>dad_pik</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>138</td>\n",
" <td>149</td>\n",
" <td>NaN</td>\n",
" <td>2.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>177</td>\n",
" <td>NaN</td>\n",
" <td>003</td>\n",
" <td>87.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>146</td>\n",
" <td>013</td>\n",
" <td>004</td>\n",
" <td>71.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>104</td>\n",
" <td>187</td>\n",
" <td>005</td>\n",
" <td>21.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>083</td>\n",
" <td>025</td>\n",
" <td>007</td>\n",
" <td>13.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>115</td>\n",
" <td>NaN</td>\n",
" <td>008</td>\n",
" <td>28.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>170</td>\n",
" <td>157</td>\n",
" <td>009</td>\n",
" <td>65.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>051</td>\n",
" <td>058</td>\n",
" <td>NaN</td>\n",
" <td>35.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>054</td>\n",
" <td>034</td>\n",
" <td>NaN</td>\n",
" <td>99.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>111</td>\n",
" <td>004</td>\n",
" <td>NaN</td>\n",
" <td>2.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" pik mom_pik dad_pik state\n",
"0 138 149 NaN 2.0\n",
"1 177 NaN 003 87.0\n",
"2 146 013 004 71.0\n",
"3 104 187 005 21.0\n",
"4 083 025 007 13.0\n",
"5 115 NaN 008 28.0\n",
"6 170 157 009 65.0\n",
"7 051 058 NaN 35.0\n",
"8 054 034 NaN 99.0\n",
"9 111 004 NaN 2.0"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_sas(\"../data/inner_join_example.sas7bdat\", encoding=\"latin-1\");\n",
"df.head(n=10)"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"remove_input"
]
},
"source": [
"### FULL JOIN\n",
"\n",
"![image](full_join.png \"FULL JOIN\")\n",
"\n",
"A `FULL JOIN` selects all observations from both datasets and sets missing key values for observations that only appear in the right dataset.\n",
"A `FULL JOIN` is the logical OR between two datasets, but unlike an `INNER JOIN` it is not commutative because missing key values are \n",
"set based on the right dataset. We illustrate this below. \n",
"\n",
"#### Example\n",
"\n",
"In the example below, we have the same setup as in the previous examples, except that we use a `FULL JOIN` with `out.kid_geos` as the right dataset.\n",
"\n",
"```sas\n",
"proc sql; \n",
" create table out.fake_micro_with_geo as\n",
" select A.pik, A.mom_pik, A.dad_pik, B.state\n",
" from out.fake_micro as A\n",
" inner join out.kid_geos as B\n",
" on A.pik = B.pik and B.pik is not null;\n",
"quit;\n",
"\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"popout"
]
},
"source": [
"```{margin} Note \n",
"`out.kid_geos` is a subset of `out.fake_micro` so there will never be missing keys (`pik`). A `FULL JOIN` where the right dataset is a subset of the left dataset is equivalent to a `LEFT JOIN`. \n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"tags": [
"remove_input"
]
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>pik</th>\n",
" <th>mom_pik</th>\n",
" <th>dad_pik</th>\n",
" <th>state</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" <td>034</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>002</td>\n",
" <td>NaN</td>\n",
" <td>117</td>\n",
" <td>48.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>003</td>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" <td>81.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>004</td>\n",
" <td>NaN</td>\n",
" <td>101</td>\n",
" <td>24.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>005</td>\n",
" <td>NaN</td>\n",
" <td>058</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>006</td>\n",
" <td>010</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>007</td>\n",
" <td>172</td>\n",
" <td>NaN</td>\n",
" <td>1.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>008</td>\n",
" <td>NaN</td>\n",
" <td>017</td>\n",
" <td>37.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>009</td>\n",
" <td>NaN</td>\n",
" <td>029</td>\n",
" <td>2.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>010</td>\n",
" <td>NaN</td>\n",
" <td>142</td>\n",
" <td>38.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" pik mom_pik dad_pik state\n",
"0 001 NaN 034 24.0\n",
"1 002 NaN 117 48.0\n",
"2 003 001 NaN 81.0\n",
"3 004 NaN 101 24.0\n",
"4 005 NaN 058 NaN\n",
"5 006 010 NaN NaN\n",
"6 007 172 NaN 1.0\n",
"7 008 NaN 017 37.0\n",
"8 009 NaN 029 2.0\n",
"9 010 NaN 142 38.0"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_sas(\"../data/full_join_example.sas7bdat\", encoding=\"latin-1\");\n",
"df.head(n=10)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Reverse Example\n",
"\n",
"Now lets reverse the datasets to illustrate why a `FULL JOIN` is not commutative. \n",
"\n",
"```sas\n",
"proc sql; \n",
" create table out.full_join_example as\n",
" select A.pik, A.state, B.mom_pik, B.dad_pik\n",
" from out.kid_geos as A\n",
" full join out.fake_micro as B\n",
" on A.pik = B.pik and A.pik is not null;\n",
"quit;\n",
"\n",
"```"
]
},
{
"cell_type": "markdown",
"metadata": {
"tags": [
"popout"
]
},
"source": [
"```{margin} Note \n",
"We can see that observations 4 and 5 are missing keys (`pik`) because these observations appear in `out.fake_micro` but do not appear in `out.kid_geos`. These are the same observations that are missing `state` above.\n",
"```"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"tags": [
"remove_input"
]
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>pik</th>\n",
" <th>state</th>\n",
" <th>mom_pik</th>\n",
" <th>dad_pik</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>001</td>\n",
" <td>24.0</td>\n",
" <td>NaN</td>\n",
" <td>034</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>002</td>\n",
" <td>48.0</td>\n",
" <td>NaN</td>\n",
" <td>117</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>003</td>\n",
" <td>81.0</td>\n",
" <td>001</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>004</td>\n",
" <td>24.0</td>\n",
" <td>NaN</td>\n",
" <td>101</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>058</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>010</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>007</td>\n",
" <td>1.0</td>\n",
" <td>172</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>008</td>\n",
" <td>37.0</td>\n",
" <td>NaN</td>\n",
" <td>017</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>009</td>\n",
" <td>2.0</td>\n",
" <td>NaN</td>\n",
" <td>029</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>010</td>\n",
" <td>38.0</td>\n",
" <td>NaN</td>\n",
" <td>142</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" pik state mom_pik dad_pik\n",
"0 001 24.0 NaN 034\n",
"1 002 48.0 NaN 117\n",
"2 003 81.0 001 NaN\n",
"3 004 24.0 NaN 101\n",
"4 NaN NaN NaN 058\n",
"5 NaN NaN 010 NaN\n",
"6 007 1.0 172 NaN\n",
"7 008 37.0 NaN 017\n",
"8 009 2.0 NaN 029\n",
"9 010 38.0 NaN 142"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_sas(\"../data/full_join_example_reverse.sas7bdat\", encoding=\"latin-1\");\n",
"df.head(n=10)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### References\n",
"\n",
"[This](https://support.sas.com/resources/papers/proceedings/proceedings/sugi25/25/cc/25p109.pdf) file was referenced when creating this page. "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.7.3"
}
},
"nbformat": 4,
"nbformat_minor": 2
}