examples/Examples-Section2-SGF2020-Tutorial-Everything_Is_Better_With_Friends.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Everything is better with friends: Using SAS in Python applications with SASPy and open-source tools\n",
    "\n",
    "## Half-Day Tutorial &nbsp;&bullet;&nbsp;  SAS Global Forum 2020"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Section 2. SASPy Data Round Trip"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 2.1. Connect to a SAS Kernel\n",
    "\n",
    "<b><u>Instructions</u></b>: Click anywhere in the code cell immediately below, and run the cell using Shift-Enter. Then attempt the Exercises that follow, only looking at the explanatory notes for hints when needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from saspy import SASsession\n",
    "sas = SASsession(results='HTML')\n",
    "print(type(sas))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Line-by-Line Code Explanation**:\n",
    "\n",
    "* Line 1: Import the definition of a `SASsession` object from the `saspy` module. (A SAS session means a connection to a SAS kernel, which we can send SAS code to and then get back the execution results.)\n",
    " \n",
    "* Line 2: Create a `SASsession` object named `sas`, and set the output format of any results created within the SAS session to be HTML.\n",
    "\n",
    "* Line 3: Print the type of the `sas` object.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.1.1**. True or False: The results argument in the SASsession function is optional. (In other words, Line 2 could have instead been written as `sas = SASsession()`.)\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.1.2**. Write a single line of Python code to create a `SASsession` object having a different value for the argument `results`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "&nbsp;\n",
    "\n",
    "**Notes About Example 2.1**:\n",
    "1. As with the `DataFrame` object type used in Example 1.5 above, the `SASsession` type is not built into Python, which is why we first had to import its definition from the `saspy` module.\n",
    "\n",
    "2. The `SASsession` function can be passed several different arguments, with `results` used to specify how SAS code returns its execution results:\n",
    "  * `results='HTML'` means HTML results are returned, which we would typically get in SAS using an `ODS HTML` output destination. This is also the default value, which means the value used if no `results` argument is passed to `SASsession`.\n",
    "  * `results='TEXT'` means plain-text results are returned, which we would typically get in SAS using an `ODS LISTING` output destination.\n",
    "  * `results='Pandas'` (the default) means results are returned as datasets and imported to Pandas DataFrames. This is similar to using the `ODS OUTPUT` destination in SAS.\n",
    "\n",
    "\n",
    "3. All subsequent exercises in this Section assume the object `sas` exists.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 2.2. Load a SAS dataset into a DataFrame\n",
    "\n",
    "<b><u>Instructions</u></b>: Click anywhere in the code cell immediately below, and run the cell using Shift-Enter. Then attempt the Exercises that follow, only looking at the explanatory notes for hints when needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fish_df_smelt_only = sas.sasdata2dataframe(\n",
    "    table='fish',\n",
    "    libref='sashelp',\n",
    "    dsopts={\n",
    "        'where' : ' Species = \"Smelt\" ',\n",
    "        'obs'   : 10,\n",
    "    },\n",
    ")\n",
    "print(type(fish_df_smelt_only))\n",
    "print()\n",
    "print(fish_df_smelt_only.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Line-by-Line Code Explanation**:\n",
    "\n",
    "* Lines 1-8: Create a DataFrame object named `fish_df_smelt_only` with dimensions 10x7 (10 rows and 7 columns) from the SAS dataset sashelp.fish by subsetting to rows where the column `Species` has the value `Smelt`.\n",
    "\n",
    "* Line 9: Print the type of object `fish_df_smelt_only`.\n",
    "\n",
    "* Line 10: Print a blank line.\n",
    "\n",
    "* Line 11: Print the first 5 rows of `fish_df_smelt_only`.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.2.1**. By default, the `head` method returns the first &lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar; rows in a dataset.\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.2.2**. True or False: The `head` method (without an argument) always returns the same number of rows in a dataset.\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.2.3**. Write several lines of Python code to create a DataFrame object from the SAS dataset sashelp.fish, but limiting the rows using a different value for `species` (make up a value, if you don't already know this dataset, or see the output in Example 2.3 below)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "&nbsp;\n",
    "\n",
    "**Notes About Example 2.2**:\n",
    "1. `sasdata2dataframe` is a method of a `SASsession` object, which allows the contents of a SAS dataset (meaning a physical file living on disk) to be copied into memory as a DataFrame object. The resulting DataFrame has rows labelled by non-negative integers: The first row is labelled as 0, the second as 1, and so on, just like elements in a list. However, as we'll see below, the row labels can also be given by an index column.\n",
    "\n",
    "2. The `dsopts` argument for `sasdata2dataframe` allows dataset options to be passed to SAS, which subset the dataset before it's converted to a DataFrame. In the above example, we've only used the dataset options `where` and `obs`, but it's also possible to pass through additional options like `keep` and `drop`, which accept lists of columns to keep or drop.\n",
    "\n",
    "3. When used without an argument, the `head` method returns the first 5 rows of a DataFrame (or the entire DataFrame, if there are 5 or fewer rows). We can also control the number of rows; e.g., `fish_df_smelt_only.head(3)` returns the first 3 rows.\n",
    "\n",
    "4. A parallel method called `tail` can also be used to return the last few rows in a DataFrame.\n",
    "\n",
    "5. The `sas` object represents a connection to a SAS session and was created in Exercise 2.1 above.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 2.3. Manipulate a DataFrame\n",
    "\n",
    "<b><u>Instructions</u></b>: Click anywhere in the code cell immediately below, and run the cell using Shift-Enter. Then attempt the Exercises that follow, only looking at the explanatory notes for hints when needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fish_df = sas.sasdata2dataframe(table='fish',libref='sashelp')\n",
    "fish_df_g   = fish_df.groupby('Species')\n",
    "fish_df_gs  = fish_df_g['Weight']\n",
    "fish_df_gsa = fish_df_gs.agg(['count', 'std', 'mean', 'min', 'max'])\n",
    "print(fish_df_gsa)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Line-by-Line Code Explanation**:\n",
    "\n",
    "* Line 1: Create a DataFrame object named `fish_df` with dimensions 159x7, comprising all 159 rows and 7 columns of the SAS dataset sashelp.fish.\n",
    "\n",
    "* Line 2: Group the rows of `fish_df` by the values in column `Species`. (This can be thought of as follows: For each possible value of `Species`, create a DataFrame having just the corresponding rows.)\n",
    "\n",
    "* Line 3: Subset to just the column `Weight` in the groupings.\n",
    "\n",
    "* Line 4: Apply aggregation functions for counting number of records, standard deviation, mean, minimum, and maximum to the values of `Weight` that have been grouped by values of `Species`.\n",
    "\n",
    "* Line 5: Print the results of the aggregations.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.3.1**. The DataFrame `groupby` method is like a &lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar; statement in a PROC MEANS step in SAS.\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.3.2**. After a `groupby` method has been used on a DataFrame, subsetting to a specific column is like a &lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;&lowbar;  statement in a PROC MEANS step in SAS.\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.3.3**. Write several lines of Python code to create a DataFrame object from the SAS dataset sashelp.class, and then imitate a PROC MEANS step to get the median of `Height` when grouped by `Sex`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "&nbsp;\n",
    "\n",
    "**Notes about Example 2.3**:\n",
    "1. When the `sasdata2dataframe` method is used without an `dsopts` argument, the entire SAS dataset is copied into memory as a DataFrame.\n",
    "\n",
    "2. In the output, notice that the left-most column `Species` is actually an index column, which is a byproduct of using the `groupby` method. In other words, we can think of the rows of `fish_df_gsa` as being labelled by values of `Species`. This is different from the output in Example 2.2, where the rows of `fish_df_smelt_only` were labelled by non-negative integers since `fish_df_smelt_only` doesn't have an index column.\n",
    "\n",
    "3. The SAS equivalent of this example is as follows:\n",
    "    ```\n",
    "    proc means data=sashelp.fish std mean min max;\n",
    "        class species;\n",
    "        var Weight;\n",
    "    run;\n",
    "    ```\n",
    "    \n",
    "   However, while PROC MEANS operates on SAS datasets row-by-row from disk, DataFrames are stored entirely in main memory. This allows any number of DataFrame operations to be combined for on-the-fly reshaping using \"method chaining.\" In other words, `fish_df_gsa` could have instead been created with the following one-liner, which avoids the need for intermediate DataFrames (and thus executes much more quickly):\n",
    "   ```\n",
    "    fish_df_gsa = fish_df.groupby('Species')['Weight'].agg(\n",
    "        ['count', 'std', 'mean', 'min', 'max']\n",
    "    )\n",
    "    ```\n",
    "\n",
    "4. Exercise 2.4 below assumes `fish_df_gsa` exists.\n",
    "\n",
    "5. The `sas` object represents a connection to a SAS session and was created in Exercise 2.1 above.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example 2.4. Load a DataFrame into a SAS dataset\n",
    "\n",
    "<b><u>Instructions</u></b>: Click anywhere in the code cell immediately below, and run the cell using Shift-Enter. Then attempt the Exercises that follow, only looking at the explanatory notes for hints when needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sas.dataframe2sasdata(fish_df_gsa, table=\"fish_sds_gsa\", libref=\"Work\")\n",
    "sas_submit_return_value = sas.submit(\n",
    "    '''\n",
    "        PROC PRINT DATA=fish_sds_gsa;\n",
    "        RUN;\n",
    "    ''',\n",
    "    results='TEXT'\n",
    ")\n",
    "sas_submit_results = sas_submit_return_value['LST']\n",
    "print(sas_submit_results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Line-by-Line Code Explanation:**\n",
    "\n",
    "* Line 1 : Convert the pandas DataFrame `fish_df_gsa` (a memory-resident rectangular array of values created in Example 2.3) into a SAS dataset (a physical file on disk), and store the result in the Work library (a physical location on disk).\n",
    "\n",
    "* Lines 2-8: Apply the PRINT procedure to SAS dataset `fish_df_gsa` (by loading the dataset from disk row by row), and return the results in TEXT format. (Note that lines 3-6 define a single string object that has been triple-quoted, allowing it to contain embedded line breaks.)\n",
    "\n",
    "* Lines 9-10: Extract the output from the dictionary returned by the `submit` method, and print it.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.4.1**. True or False: Information can be lost when the `dataframe2sasdata` method is used to transform a DataFrame into a SAS dataset.\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.4.2**. True or False: The `submit` method of a `SASsession` object allows arbitrary SAS code to be submitted directly to the SAS kernel.\n",
    "\n",
    "&nbsp;\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise 2.4.3**. Write several lines of Python code to convert the DataFrame `fish_df` created in Example 2.3 to a SAS dataset, and then use SAS's CONTENTS procedure directly on the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "&nbsp;\n",
    "\n",
    "**Notes about Example 2.4**:\n",
    "\n",
    "1. Comparing the output of Examples 2.3 and 2.4, notice that the index column `Species` has been lost. Properties of a DataFrame without a SAS equivalences are not preserved when the method `dataframe2sasdata` is used to convert to a SAS dataset.\n",
    "\n",
    "2. Python strings can be defined with one of three quoting conventions: \n",
    "  * single quotes, as in 'Hello, World!'\n",
    "  * double quotes, as in \"Hello, World!\"\n",
    "  * triple quotes, as in '''Hello, World!'''\n",
    "  \n",
    "  All three styles are interchangeable for single-line strings. However, unlike single- and double-quoted strings, triple-quoted strings can contain embedded line breaks.\n",
    "  \n",
    "3. The first argument to the `submit` method is a string containing arbitrary SAS code to be passed directly to the SAS kernel. After the SAS kernel executes the code, a dictionary is returned with the following two key-value pairs:\n",
    "   - `sas_submit_return_value['LST']` is a string comprising the results from executing PROC PRINT, which will be in plain text because the results='TEXT' was used\n",
    "   - `sas_submit_return_value['LOG']` is a string comprising the plain-text log resulting from executing PROC PRINT\n",
    "\n",
    "\n",
    "4. The second argument to the `submit` method is optional. When used, it allows the output style of the `SASsession` object `sas` to be overridden.\n",
    "\n",
    "5. The `sas` object represents a connection to a SAS session and was created in Exercise 2.1 above.\n",
    "\n",
    "&nbsp;"
   ]
  },
  {
   "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.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}