# Comparison with SAS

For potential users coming from SAS this page is meant to demonstrate how different SAS operations would be performed in pandas.

If you’re new to pandas, you might want to first read through 10 Minutes to pandas to familiarize yourself with the library.

As is customary, we import pandas and NumPy as follows:

    In [1]: import pandas as pd

    In [2]: import numpy as np

## Data structures

### General terminology translation

pandas | SAS
--- | ---
`DataFrame` | data set
column | variable
row | observation
groupby | BY-group
`NaN` | `.`

### `DataFrame`

A `DataFrame` in pandas is analogous to a SAS data set - a two-dimensional data source with labeled columns that can be of different types. As will be shown in this document, almost any operation that can be applied to a data set using SAS’s `DATA` step, can also be accomplished in pandas.

### `Series`

A `Series` is the data structure that represents one column of a `DataFrame`. SAS doesn’t have a separate data structure for a single column, but in general, working with a `Series` is analogous to referencing a column in the `DATA` step.

### `Index`

Every `DataFrame` and `Series` has an `Index` - which are labels on the rows of the data. SAS does not have an exactly analogous concept. A data set’s rows are essentially unlabeled, other than an implicit integer index that can be accessed during the `DATA` step (`_N_`).

In pandas, if no index is specified, an integer index is also used by default (first row = 0, second row = 1, and so on). While using a labeled `Index` or `MultiIndex` can enable sophisticated analyses and is ultimately an important part of pandas to understand, for this comparison we will essentially ignore the `Index` and just treat the `DataFrame` as a collection of columns. Please see the indexing documentation for much more on how to use an `Index` effectively.

### Copies vs. in place operations

Most pandas operations return copies of the `Series`/`DataFrame`. To make the changes “stick”, you’ll need to either assign to a new variable:

>     sorted_df = df.sort_values("col1")

or overwrite the original one:

>     df = df.sort_values("col1")

Note

You will see an `inplace=True` keyword argument available for some methods:

    df.sort_values("col1", inplace=True)

Its use is discouraged. More information.

## Data input / output

### Constructing a DataFrame from values

A SAS data set can be built from specified values by placing the data after a `datalines` statement and specifying the column names.

    data df;
        input x y;
        datalines;
        1 2
        3 4
        5 6
        ;
    run;

A pandas `DataFrame` can be constructed in many different ways, but for a small number of values, it is often convenient to specify it as a Python dictionary, where the keys are the column names and the values are the data.

    In [1]: df = pd.DataFrame({"x": [1, 3, 5], "y": [2, 4, 6]})

    In [2]: df
    Out[2]:
       x  y
    0  1  2
    1  3  4
    2  5  6

### Reading external data

Like SAS, pandas provides utilities for reading in data from many formats. The `tips` dataset, found within the pandas tests (csv) will be used in many of the following examples.

SAS provides `PROC IMPORT` to read csv data into a data set.

    proc import datafile='tips.csv' dbms=csv out=tips replace;
        getnames=yes;
    run;

The pandas method is `read_csv()`, which works similarly.

    In [3]: url = (
       ...:     "https://raw.github.com/pandas-dev/"
       ...:     "pandas/master/pandas/tests/io/data/csv/tips.csv"
       ...: )
       ...:

    In [4]: tips = pd.read_csv(url)
    ---------------------------------------------------------------------------
    ConnectionRefusedError                    Traceback (most recent call last)
    /usr/lib/python3.10/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
       1347             try:
    -> 1348                 h.request(req.get_method(), req.selector, req.data, headers,
       1349                           encode_chunked=req.has_header('Transfer-encoding'))

    /usr/lib/python3.10/http/client.py in request(self, method, url, body, headers, encode_chunked)
       1281         """Send a complete request to the server."""
    -> 1282         self._send_request(method, url, body, headers, encode_chunked)
       1283

    /usr/lib/python3.10/http/client.py in _send_request(self, method, url, body, headers, encode_chunked)
       1327             body = _encode(body, 'body')
    -> 1328         self.endheaders(body, encode_chunked=encode_chunked)
       1329

    /usr/lib/python3.10/http/client.py in endheaders(self, message_body, encode_chunked)
       1276             raise CannotSendHeader()
    -> 1277         self._send_output(message_body, encode_chunked=encode_chunked)
       1278

    /usr/lib/python3.10/http/client.py in _send_output(self, message_body, encode_chunked)
       1036         del self._buffer[:]
    -> 1037         self.send(msg)
       1038

    /usr/lib/python3.10/http/client.py in send(self, data)
        974             if self.auto_open:
    --> 975                 self.connect()
        976             else:

    /usr/lib/python3.10/http/client.py in connect(self)
       1446
    -> 1447             super().connect()
       1448

    /usr/lib/python3.10/http/client.py in connect(self)
        940         sys.audit("http.client.connect", self, self.host, self.port)
    --> 941         self.sock = self._create_connection(
        942             (self.host,self.port), self.timeout, self.source_address)

    /usr/lib/python3.10/socket.py in create_connection(address, timeout, source_address)
        844         try:
    --> 845             raise err
        846         finally:

    /usr/lib/python3.10/socket.py in create_connection(address, timeout, source_address)
        832                 sock.bind(source_address)
    --> 833             sock.connect(sa)
        834             # Break explicitly a reference cycle

    ConnectionRefusedError: [Errno 111] Connection refused

    During handling of the above exception, another exception occurred:

    URLError                                  Traceback (most recent call last)
    <ipython-input-4-8ab2297b7141> in <module>
    ----> 1 tips = pd.read_csv(url)

    /usr/lib/python3/dist-packages/pandas/util/_decorators.py in wrapper(*args, **kwargs)
        309                     stacklevel=stacklevel,
        310                 )
    --> 311             return func(*args, **kwargs)
        312
        313         return wrapper

    /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, error_bad_lines, warn_bad_lines, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options)
        584     kwds.update(kwds_defaults)
        585
    --> 586     return _read(filepath_or_buffer, kwds)
        587
        588

    /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in _read(filepath_or_buffer, kwds)
        480
        481     # Create the parser.
    --> 482     parser = TextFileReader(filepath_or_buffer, **kwds)
        483
        484     if chunksize or iterator:

    /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in __init__(self, f, engine, **kwds)
        809             self.options["has_index_names"] = kwds["has_index_names"]
        810
    --> 811         self._engine = self._make_engine(self.engine)
        812
        813     def close(self):

    /usr/lib/python3/dist-packages/pandas/io/parsers/readers.py in _make_engine(self, engine)
       1038             )
       1039         # error: Too many arguments for "ParserBase"
    -> 1040         return mapping[engine](self.f, **self.options)  # type: ignore[call-arg]
       1041
       1042     def _failover_to_python(self):

    /usr/lib/python3/dist-packages/pandas/io/parsers/c_parser_wrapper.py in __init__(self, src, **kwds)
         49
         50         # open handles
    ---> 51         self._open_handles(src, kwds)
         52         assert self.handles is not None
         53

    /usr/lib/python3/dist-packages/pandas/io/parsers/base_parser.py in _open_handles(self, src, kwds)
        220         Let the readers open IOHandles after they are done with their potential raises.
        221         """
    --> 222         self.handles = get_handle(
        223             src,
        224             "r",

    /usr/lib/python3/dist-packages/pandas/io/common.py in get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text, errors, storage_options)
        607
        608     # open URLs
    --> 609     ioargs = _get_filepath_or_buffer(
        610         path_or_buf,
        611         encoding=encoding,

    /usr/lib/python3/dist-packages/pandas/io/common.py in _get_filepath_or_buffer(filepath_or_buffer, encoding, compression, mode, storage_options)
        310         # assuming storage_options is to be interpreted as headers
        311         req_info = urllib.request.Request(filepath_or_buffer, headers=storage_options)
    --> 312         with urlopen(req_info) as req:
        313             content_encoding = req.headers.get("Content-Encoding", None)
        314             if content_encoding == "gzip":

    /usr/lib/python3/dist-packages/pandas/io/common.py in urlopen(*args, **kwargs)
        210     import urllib.request
        211
    --> 212     return urllib.request.urlopen(*args, **kwargs)
        213
        214

    /usr/lib/python3.10/urllib/request.py in urlopen(url, data, timeout, cafile, capath, cadefault, context)
        214     else:
        215         opener = _opener
    --> 216     return opener.open(url, data, timeout)
        217
        218 def install_opener(opener):

    /usr/lib/python3.10/urllib/request.py in open(self, fullurl, data, timeout)
        517
        518         sys.audit('urllib.Request', req.full_url, req.data, req.headers, req.get_method())
    --> 519         response = self._open(req, data)
        520
        521         # post-process response

    /usr/lib/python3.10/urllib/request.py in _open(self, req, data)
        534
        535         protocol = req.type
    --> 536         result = self._call_chain(self.handle_open, protocol, protocol +
        537                                   '_open', req)
        538         if result:

    /usr/lib/python3.10/urllib/request.py in _call_chain(self, chain, kind, meth_name, *args)
        494         for handler in handlers:
        495             func = getattr(handler, meth_name)
    --> 496             result = func(*args)
        497             if result is not None:
        498                 return result

    /usr/lib/python3.10/urllib/request.py in https_open(self, req)
       1389
       1390         def https_open(self, req):
    -> 1391             return self.do_open(http.client.HTTPSConnection, req,
       1392                 context=self._context, check_hostname=self._check_hostname)
       1393

    /usr/lib/python3.10/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
       1349                           encode_chunked=req.has_header('Transfer-encoding'))
       1350             except OSError as err: # timeout error
    -> 1351                 raise URLError(err)
       1352             r = h.getresponse()
       1353         except:

    URLError: <urlopen error [Errno 111] Connection refused>

    In [5]: tips
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-5-9034fb5f8272> in <module>
    ----> 1 tips

    NameError: name 'tips' is not defined

Like `PROC IMPORT`, `read_csv` can take a number of parameters to specify how the data should be parsed. For example, if the data was instead tab delimited, and did not have column names, the pandas command would be:

    tips = pd.read_csv("tips.csv", sep="\t", header=None)

    # alternatively, read_table is an alias to read_csv with tab delimiter
    tips = pd.read_table("tips.csv", header=None)

In addition to text/csv, pandas supports a variety of other data formats such as Excel, HDF5, and SQL databases. These are all read via a `pd.read_*` function. See the IO documentation for more details.

### Limiting output

By default, pandas will truncate output of large `DataFrame`s to show the first and last rows. This can be overridden by changing the pandas options, or using `DataFrame.head()` or `DataFrame.tail()`.

    In [1]: tips.head(5)
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-e2693e4d5ad6> in <module>
    ----> 1 tips.head(5)

    NameError: name 'tips' is not defined

The equivalent in SAS would be:

    proc print data=df(obs=5);
    run;

### Exporting data

The inverse of `PROC IMPORT` in SAS is `PROC EXPORT`

    proc export data=tips outfile='tips2.csv' dbms=csv;
    run;

Similarly in pandas, the opposite of `read_csv` is `to_csv()`, and other data formats follow a similar api.

    tips.to_csv("tips2.csv")

## Data operations

### Operations on columns

In the `DATA` step, arbitrary math expressions can be used on new or existing columns.

    data tips;
        set tips;
        total_bill = total_bill - 2;
        new_bill = total_bill / 2;
    run;

pandas provides vectorized operations by specifying the individual `Series` in the `DataFrame`. New columns can be assigned in the same way. The `DataFrame.drop()` method drops a column from the `DataFrame`.

    In [1]: tips["total_bill"] = tips["total_bill"] - 2
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-30a6f4d646c7> in <module>
    ----> 1 tips["total_bill"] = tips["total_bill"] - 2

    NameError: name 'tips' is not defined

    In [2]: tips["new_bill"] = tips["total_bill"] / 2
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-7e2a96df4894> in <module>
    ----> 1 tips["new_bill"] = tips["total_bill"] / 2

    NameError: name 'tips' is not defined

    In [3]: tips
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-3-9034fb5f8272> in <module>
    ----> 1 tips

    NameError: name 'tips' is not defined

    In [4]: tips = tips.drop("new_bill", axis=1)
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-4-2bfa6fa8e9bc> in <module>
    ----> 1 tips = tips.drop("new_bill", axis=1)

    NameError: name 'tips' is not defined

### Filtering

Filtering in SAS is done with an `if` or `where` statement, on one or more columns.

    data tips;
        set tips;
        if total_bill > 10;
    run;

    data tips;
        set tips;
        where total_bill > 10;
        /* equivalent in this case - where happens before the
           DATA step begins and can also be used in PROC statements */
    run;

DataFrames can be filtered in multiple ways; the most intuitive of which is using boolean indexing.

    In [1]: tips[tips["total_bill"] > 10]
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-4e5df2b556a0> in <module>
    ----> 1 tips[tips["total_bill"] > 10]

    NameError: name 'tips' is not defined

The above statement is simply passing a `Series` of `True`/`False` objects to the DataFrame, returning all rows with `True`.

    In [1]: is_dinner = tips["time"] == "Dinner"
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-a38011ae4c7d> in <module>
    ----> 1 is_dinner = tips["time"] == "Dinner"

    NameError: name 'tips' is not defined

    In [2]: is_dinner
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-49a5e8deec4a> in <module>
    ----> 1 is_dinner

    NameError: name 'is_dinner' is not defined

    In [3]: is_dinner.value_counts()
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-3-900d99c3802f> in <module>
    ----> 1 is_dinner.value_counts()

    NameError: name 'is_dinner' is not defined

    In [4]: tips[is_dinner]
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-4-ed12d6ad643e> in <module>
    ----> 1 tips[is_dinner]

    NameError: name 'tips' is not defined

### If/then logic

In SAS, if/then logic can be used to create new columns.

    data tips;
        set tips;
        format bucket $4.;

        if total_bill < 10 then bucket = 'low';
        else bucket = 'high';
    run;

The same operation in pandas can be accomplished using the `where` method from `numpy`.

    In [1]: tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-a8d4661e6618> in <module>
    ----> 1 tips["bucket"] = np.where(tips["total_bill"] < 10, "low", "high")

    NameError: name 'tips' is not defined

    In [2]: tips
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-9034fb5f8272> in <module>
    ----> 1 tips

    NameError: name 'tips' is not defined

### Date functionality

SAS provides a variety of functions to do operations on date/datetime columns.

    data tips;
        set tips;
        format date1 date2 date1_plusmonth mmddyy10.;
        date1 = mdy(1, 15, 2013);
        date2 = mdy(2, 15, 2015);
        date1_year = year(date1);
        date2_month = month(date2);
        * shift date to beginning of next interval;
        date1_next = intnx('MONTH', date1, 1);
        * count intervals between dates;
        months_between = intck('MONTH', date1, date2);
    run;

The equivalent pandas operations are shown below. In addition to these functions pandas supports other Time Series features not available in Base SAS (such as resampling and custom offsets) - see the timeseries documentation for more details.

    In [1]: tips["date1"] = pd.Timestamp("2013-01-15")
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-33d7f805e8ca> in <module>
    ----> 1 tips["date1"] = pd.Timestamp("2013-01-15")

    NameError: name 'tips' is not defined

    In [2]: tips["date2"] = pd.Timestamp("2015-02-15")
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-40a3130f91b4> in <module>
    ----> 1 tips["date2"] = pd.Timestamp("2015-02-15")

    NameError: name 'tips' is not defined

    In [3]: tips["date1_year"] = tips["date1"].dt.year
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-3-c9e20f269b76> in <module>
    ----> 1 tips["date1_year"] = tips["date1"].dt.year

    NameError: name 'tips' is not defined

    In [4]: tips["date2_month"] = tips["date2"].dt.month
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-4-ef1fc2805b92> in <module>
    ----> 1 tips["date2_month"] = tips["date2"].dt.month

    NameError: name 'tips' is not defined

    In [5]: tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-5-60c45d997a52> in <module>
    ----> 1 tips["date1_next"] = tips["date1"] + pd.offsets.MonthBegin()

    NameError: name 'tips' is not defined

    In [6]: tips["months_between"] = tips["date2"].dt.to_period("M") - tips[
       ...:     "date1"
       ...: ].dt.to_period("M")
       ...:
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-6-a4bc4ed24519> in <module>
    ----> 1 tips["months_between"] = tips["date2"].dt.to_period("M") - tips[
          2     "date1"
          3 ].dt.to_period("M")

    NameError: name 'tips' is not defined

    In [7]: tips[
       ...:     ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
       ...: ]
       ...:
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-7-606cac654d39> in <module>
    ----> 1 tips[
          2     ["date1", "date2", "date1_year", "date2_month", "date1_next", "months_between"]
          3 ]

    NameError: name 'tips' is not defined

### Selection of columns

SAS provides keywords in the `DATA` step to select, drop, and rename columns.

    data tips;
        set tips;
        keep sex total_bill tip;
    run;

    data tips;
        set tips;
        drop sex;
    run;

    data tips;
        set tips;
        rename total_bill=total_bill_2;
    run;

The same operations are expressed in pandas below.

#### Keep certain columns

    In [1]: tips[["sex", "total_bill", "tip"]]
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-88ea27a409ff> in <module>
    ----> 1 tips[["sex", "total_bill", "tip"]]

    NameError: name 'tips' is not defined

#### Drop a column

    In [2]: tips.drop("sex", axis=1)
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-e6cea0c561ee> in <module>
    ----> 1 tips.drop("sex", axis=1)

    NameError: name 'tips' is not defined

#### Rename a column

    In [1]: tips.rename(columns={"total_bill": "total_bill_2"})
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-89e2303a1fc4> in <module>
    ----> 1 tips.rename(columns={"total_bill": "total_bill_2"})

    NameError: name 'tips' is not defined

### Sorting by values

Sorting in SAS is accomplished via `PROC SORT`

    proc sort data=tips;
        by sex total_bill;
    run;

pandas has a `DataFrame.sort_values()` method, which takes a list of columns to sort by.

    In [1]: tips = tips.sort_values(["sex", "total_bill"])
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-9d59129f8932> in <module>
    ----> 1 tips = tips.sort_values(["sex", "total_bill"])

    NameError: name 'tips' is not defined

    In [2]: tips
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-9034fb5f8272> in <module>
    ----> 1 tips

    NameError: name 'tips' is not defined

## String processing

### Finding length of string

SAS determines the length of a character string with the LENGTHN and LENGTHC functions. `LENGTHN` excludes trailing blanks and `LENGTHC` includes trailing blanks.

    data _null_;
    set tips;
    put(LENGTHN(time));
    put(LENGTHC(time));
    run;

You can find the length of a character string with `Series.str.len()`. In Python 3, all strings are Unicode strings. `len` includes trailing blanks. Use `len` and `rstrip` to exclude trailing blanks.

    In [1]: tips["time"].str.len()
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-ef639d164546> in <module>
    ----> 1 tips["time"].str.len()

    NameError: name 'tips' is not defined

    In [2]: tips["time"].str.rstrip().str.len()
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-3ffb003cc2cc> in <module>
    ----> 1 tips["time"].str.rstrip().str.len()

    NameError: name 'tips' is not defined

### Finding position of substring

SAS determines the position of a character in a string with the FINDW function. `FINDW` takes the string defined by the first argument and searches for the first position of the substring you supply as the second argument.

    data _null_;
    set tips;
    put(FINDW(sex,'ale'));
    run;

You can find the position of a character in a column of strings with the `Series.str.find()` method. `find` searches for the first position of the substring. If the substring is found, the method returns its position. If not found, it returns `-1`. Keep in mind that Python indexes are zero-based.

    In [1]: tips["sex"].str.find("ale")
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-ce45837e4fed> in <module>
    ----> 1 tips["sex"].str.find("ale")

    NameError: name 'tips' is not defined

### Extracting substring by position

SAS extracts a substring from a string based on its position with the SUBSTR function.

    data _null_;
    set tips;
    put(substr(sex,1,1));
    run;

With pandas you can use `[]` notation to extract a substring from a string by position locations. Keep in mind that Python indexes are zero-based.

    In [1]: tips["sex"].str[0:1]
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-e5ebda285fba> in <module>
    ----> 1 tips["sex"].str[0:1]

    NameError: name 'tips' is not defined

### Extracting nth word

The SAS SCAN function returns the nth word from a string. The first argument is the string you want to parse and the second argument specifies which word you want to extract.

    data firstlast;
    input String $60.;
    First_Name = scan(string, 1);
    Last_Name = scan(string, -1);
    datalines2;
    John Smith;
    Jane Cook;
    ;;;
    run;

The simplest way to extract words in pandas is to split the strings by spaces, then reference the word by index. Note there are more powerful approaches should you need them.

    In [1]: firstlast = pd.DataFrame({"String": ["John Smith", "Jane Cook"]})

    In [2]: firstlast["First_Name"] = firstlast["String"].str.split(" ", expand=True)[0]

    In [3]: firstlast["Last_Name"] = firstlast["String"].str.rsplit(" ", expand=True)[0]

    In [4]: firstlast
    Out[4]:
           String First_Name Last_Name
    0  John Smith       John      John
    1   Jane Cook       Jane      Jane

### Changing case

The SAS UPCASE LOWCASE and PROPCASE functions change the case of the argument.

    data firstlast;
    input String $60.;
    string_up = UPCASE(string);
    string_low = LOWCASE(string);
    string_prop = PROPCASE(string);
    datalines2;
    John Smith;
    Jane Cook;
    ;;;
    run;

The equivalent pandas methods are `Series.str.upper()`, `Series.str.lower()`, and `Series.str.title()`.

    In [1]: firstlast = pd.DataFrame({"string": ["John Smith", "Jane Cook"]})

    In [2]: firstlast["upper"] = firstlast["string"].str.upper()

    In [3]: firstlast["lower"] = firstlast["string"].str.lower()

    In [4]: firstlast["title"] = firstlast["string"].str.title()

    In [5]: firstlast
    Out[5]:
           string       upper       lower       title
    0  John Smith  JOHN SMITH  john smith  John Smith
    1   Jane Cook   JANE COOK   jane cook   Jane Cook

## Merging

The following tables will be used in the merge examples:

    In [1]: df1 = pd.DataFrame({"key": ["A", "B", "C", "D"], "value": np.random.randn(4)})

    In [2]: df1
    Out[2]:
      key     value
    0   A  0.469112
    1   B -0.282863
    2   C -1.509059
    3   D -1.135632

    In [3]: df2 = pd.DataFrame({"key": ["B", "D", "D", "E"], "value": np.random.randn(4)})

    In [4]: df2
    Out[4]:
      key     value
    0   B  1.212112
    1   D -0.173215
    2   D  0.119209
    3   E -1.044236

In SAS, data must be explicitly sorted before merging. Different types of joins are accomplished using the `in=` dummy variables to track whether a match was found in one or both input frames.

    proc sort data=df1;
        by key;
    run;

    proc sort data=df2;
        by key;
    run;

    data left_join inner_join right_join outer_join;
        merge df1(in=a) df2(in=b);

        if a and b then output inner_join;
        if a then output left_join;
        if b then output right_join;
        if a or b then output outer_join;
    run;

pandas DataFrames have a `merge()` method, which provides similar functionality. The data does not have to be sorted ahead of time, and different join types are accomplished via the `how` keyword.

    In [1]: inner_join = df1.merge(df2, on=["key"], how="inner")

    In [2]: inner_join
    Out[2]:
      key   value_x   value_y
    0   B -0.282863  1.212112
    1   D -1.135632 -0.173215
    2   D -1.135632  0.119209

    In [3]: left_join = df1.merge(df2, on=["key"], how="left")

    In [4]: left_join
    Out[4]:
      key   value_x   value_y
    0   A  0.469112       NaN
    1   B -0.282863  1.212112
    2   C -1.509059       NaN
    3   D -1.135632 -0.173215
    4   D -1.135632  0.119209

    In [5]: right_join = df1.merge(df2, on=["key"], how="right")

    In [6]: right_join
    Out[6]:
      key   value_x   value_y
    0   B -0.282863  1.212112
    1   D -1.135632 -0.173215
    2   D -1.135632  0.119209
    3   E       NaN -1.044236

    In [7]: outer_join = df1.merge(df2, on=["key"], how="outer")

    In [8]: outer_join
    Out[8]:
      key   value_x   value_y
    0   A  0.469112       NaN
    1   B -0.282863  1.212112
    2   C -1.509059       NaN
    3   D -1.135632 -0.173215
    4   D -1.135632  0.119209
    5   E       NaN -1.044236

## Missing data

Both pandas and SAS have a representation for missing data.

pandas represents missing data with the special float value `NaN` (not a number). Many of the semantics are the same; for example missing data propagates through numeric operations, and is ignored by default for aggregations.

    In [1]: outer_join
    Out[1]:
      key   value_x   value_y
    0   A  0.469112       NaN
    1   B -0.282863  1.212112
    2   C -1.509059       NaN
    3   D -1.135632 -0.173215
    4   D -1.135632  0.119209
    5   E       NaN -1.044236

    In [2]: outer_join["value_x"] + outer_join["value_y"]
    Out[2]:
    0         NaN
    1    0.929249
    2         NaN
    3   -1.308847
    4   -1.016424
    5         NaN
    dtype: float64

    In [3]: outer_join["value_x"].sum()
    Out[3]: -3.5940742896293765

One difference is that missing data cannot be compared to its sentinel value. For example, in SAS you could do this to filter missing values.

    data outer_join_nulls;
        set outer_join;
        if value_x = .;
    run;

    data outer_join_no_nulls;
        set outer_join;
        if value_x ^= .;
    run;

In pandas, `Series.isna()` and `Series.notna()` can be used to filter the rows.

    In [1]: outer_join[outer_join["value_x"].isna()]
    Out[1]:
      key  value_x   value_y
    5   E      NaN -1.044236

    In [2]: outer_join[outer_join["value_x"].notna()]
    Out[2]:
      key   value_x   value_y
    0   A  0.469112       NaN
    1   B -0.282863  1.212112
    2   C -1.509059       NaN
    3   D -1.135632 -0.173215
    4   D -1.135632  0.119209

pandas provides a variety of methods to work with missing data. Here are some examples:

### Drop rows with missing values

    In [3]: outer_join.dropna()
    Out[3]:
      key   value_x   value_y
    1   B -0.282863  1.212112
    3   D -1.135632 -0.173215
    4   D -1.135632  0.119209

### Forward fill from previous rows

    In [4]: outer_join.fillna(method="ffill")
    Out[4]:
      key   value_x   value_y
    0   A  0.469112       NaN
    1   B -0.282863  1.212112
    2   C -1.509059  1.212112
    3   D -1.135632 -0.173215
    4   D -1.135632  0.119209
    5   E -1.135632 -1.044236

### Replace missing values with a specified value

Using the mean:

    In [1]: outer_join["value_x"].fillna(outer_join["value_x"].mean())
    Out[1]:
    0    0.469112
    1   -0.282863
    2   -1.509059
    3   -1.135632
    4   -1.135632
    5   -0.718815
    Name: value_x, dtype: float64

## GroupBy

### Aggregation

SAS’s `PROC SUMMARY` can be used to group by one or more key variables and compute aggregations on numeric columns.

    proc summary data=tips nway;
        class sex smoker;
        var total_bill tip;
        output out=tips_summed sum=;
    run;

pandas provides a flexible `groupby` mechanism that allows similar aggregations. See the groupby documentation for more details and examples.

    In [1]: tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-766ce4af4bd9> in <module>
    ----> 1 tips_summed = tips.groupby(["sex", "smoker"])[["total_bill", "tip"]].sum()

    NameError: name 'tips' is not defined

    In [2]: tips_summed
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-f0d5e7410005> in <module>
    ----> 1 tips_summed

    NameError: name 'tips_summed' is not defined

### Transformation

In SAS, if the group aggregations need to be used with the original frame, it must be merged back together. For example, to subtract the mean for each observation by smoker group.

    proc summary data=tips missing nway;
        class smoker;
        var total_bill;
        output out=smoker_means mean(total_bill)=group_bill;
    run;

    proc sort data=tips;
        by smoker;
    run;

    data tips;
        merge tips(in=a) smoker_means(in=b);
        by smoker;
        adj_total_bill = total_bill - group_bill;
        if a and b;
    run;

pandas provides a Transformation mechanism that allows these type of operations to be succinctly expressed in one operation.

    In [1]: gb = tips.groupby("smoker")["total_bill"]
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-1-b48820aab6ea> in <module>
    ----> 1 gb = tips.groupby("smoker")["total_bill"]

    NameError: name 'tips' is not defined

    In [2]: tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-2-edcbd24271f3> in <module>
    ----> 1 tips["adj_total_bill"] = tips["total_bill"] - gb.transform("mean")

    NameError: name 'tips' is not defined

    In [3]: tips
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-3-9034fb5f8272> in <module>
    ----> 1 tips

    NameError: name 'tips' is not defined

### By group processing

In addition to aggregation, pandas `groupby` can be used to replicate most other by group processing from SAS. For example, this `DATA` step reads the data by sex/smoker group and filters to the first entry for each.

    proc sort data=tips;
       by sex smoker;
    run;

    data tips_first;
        set tips;
        by sex smoker;
        if FIRST.sex or FIRST.smoker then output;
    run;

In pandas this would be written as:

    In [4]: tips.groupby(["sex", "smoker"]).first()
    ---------------------------------------------------------------------------
    NameError                                 Traceback (most recent call last)
    <ipython-input-4-b8b50dd17fd6> in <module>
    ----> 1 tips.groupby(["sex", "smoker"]).first()

    NameError: name 'tips' is not defined

## Other considerations

### Disk vs memory

pandas operates exclusively in memory, where a SAS data set exists on disk. This means that the size of data able to be loaded in pandas is limited by your machine’s memory, but also that the operations on that data may be faster.

If out of core processing is needed, one possibility is the dask.dataframe library (currently in development) which provides a subset of pandas functionality for an on-disk `DataFrame`

### Data interop

pandas provides a `read_sas()` method that can read SAS data saved in the XPORT or SAS7BDAT binary format.

    libname xportout xport 'transport-file.xpt';
    data xportout.tips;
        set tips(rename=(total_bill=tbill));
        * xport variable names limited to 6 characters;
    run;

    df = pd.read_sas("transport-file.xpt")
    df = pd.read_sas("binary-file.sas7bdat")

You can also specify the file format directly. By default, pandas will try to infer the file format based on its extension.

    df = pd.read_sas("transport-file.xpt", format="xport")
    df = pd.read_sas("binary-file.sas7bdat", format="sas7bdat")

XPORT is a relatively limited format and the parsing of it is not as optimized as some of the other pandas readers. An alternative way to interop data between SAS and pandas is to serialize to csv.

    # version 0.17, 10M rows

    In [8]: %time df = pd.read_sas('big.xpt')
    Wall time: 14.6 s

    In [9]: %time df = pd.read_csv('big.csv')
    Wall time: 4.86 s