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

Note

Throughout this tutorial, the pandas DataFrame will be displayed by calling df.head(), which displays the first N (default 5) rows of the DataFrame. This is often used in interactive work (e.g. Jupyter notebook or terminal) - the equivalent in SAS would be:

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

Data Structures¶

General Terminology Translation¶

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

`DataFrame` / `Series`¶

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.

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 row 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.

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 [3]: df = pd.DataFrame({
   ...:           'x': [1, 3, 5],
   ...:           'y': [2, 4, 6]})
   ...: 

In [4]: df
Out[4]: 
   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 [5]: url = 'https://raw.github.com/pandas-dev/pandas/master/pandas/tests/data/tips.csv'

In [6]: tips = pd.read_csv(url)
---------------------------------------------------------------------------
gaierror                                  Traceback (most recent call last)
/usr/lib64/python3.4/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
   1182             try:
-> 1183                 h.request(req.get_method(), req.selector, req.data, headers)
   1184             except OSError as err: # timeout error

/usr/lib64/python3.4/http/client.py in request(self, method, url, body, headers)
   1136         """Send a complete request to the server."""
-> 1137         self._send_request(method, url, body, headers)
   1138 

/usr/lib64/python3.4/http/client.py in _send_request(self, method, url, body, headers)
   1181             body = body.encode('iso-8859-1')
-> 1182         self.endheaders(body)
   1183 

/usr/lib64/python3.4/http/client.py in endheaders(self, message_body)
   1132             raise CannotSendHeader()
-> 1133         self._send_output(message_body)
   1134 

/usr/lib64/python3.4/http/client.py in _send_output(self, message_body)
    962             message_body = None
--> 963         self.send(msg)
    964         if message_body is not None:

/usr/lib64/python3.4/http/client.py in send(self, data)
    897             if self.auto_open:
--> 898                 self.connect()
    899             else:

/usr/lib64/python3.4/http/client.py in connect(self)
   1278 
-> 1279             super().connect()
   1280 

/usr/lib64/python3.4/http/client.py in connect(self)
    870         self.sock = self._create_connection((self.host,self.port),
--> 871                                             self.timeout, self.source_address)
    872 

/usr/lib64/python3.4/socket.py in create_connection(address, timeout, source_address)
    497     err = None
--> 498     for res in getaddrinfo(host, port, 0, SOCK_STREAM):
    499         af, socktype, proto, canonname, sa = res

/usr/lib64/python3.4/socket.py in getaddrinfo(host, port, family, type, proto, flags)
    536     addrlist = []
--> 537     for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
    538         af, socktype, proto, canonname, sa = res

gaierror: [Errno -3] Temporary failure in name resolution

During handling of the above exception, another exception occurred:

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

~/rpmbuild/BUILD/pandas-0.22.0/pandas/io/parsers.py in parser_f(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, skip_footer, doublequote, delim_whitespace, as_recarray, compact_ints, use_unsigned, low_memory, buffer_lines, memory_map, float_precision)
    707                     skip_blank_lines=skip_blank_lines)
    708 
--> 709         return _read(filepath_or_buffer, kwds)
    710 
    711     parser_f.__name__ = name

~/rpmbuild/BUILD/pandas-0.22.0/pandas/io/parsers.py in _read(filepath_or_buffer, kwds)
    431     compression = _infer_compression(filepath_or_buffer, compression)
    432     filepath_or_buffer, _, compression = get_filepath_or_buffer(
--> 433         filepath_or_buffer, encoding, compression)
    434     kwds['compression'] = compression
    435 

~/rpmbuild/BUILD/pandas-0.22.0/pandas/io/common.py in get_filepath_or_buffer(filepath_or_buffer, encoding, compression)
    188 
    189     if _is_url(filepath_or_buffer):
--> 190         req = _urlopen(filepath_or_buffer)
    191         content_encoding = req.headers.get('Content-Encoding', None)
    192         if content_encoding == 'gzip':

/usr/lib64/python3.4/urllib/request.py in urlopen(url, data, timeout, cafile, capath, cadefault, context)
    159     else:
    160         opener = _opener
--> 161     return opener.open(url, data, timeout)
    162 
    163 def install_opener(opener):

/usr/lib64/python3.4/urllib/request.py in open(self, fullurl, data, timeout)
    462             req = meth(req)
    463 
--> 464         response = self._open(req, data)
    465 
    466         # post-process response

/usr/lib64/python3.4/urllib/request.py in _open(self, req, data)
    480         protocol = req.type
    481         result = self._call_chain(self.handle_open, protocol, protocol +
--> 482                                   '_open', req)
    483         if result:
    484             return result

/usr/lib64/python3.4/urllib/request.py in _call_chain(self, chain, kind, meth_name, *args)
    440         for handler in handlers:
    441             func = getattr(handler, meth_name)
--> 442             result = func(*args)
    443             if result is not None:
    444                 return result

/usr/lib64/python3.4/urllib/request.py in https_open(self, req)
   1224         def https_open(self, req):
   1225             return self.do_open(http.client.HTTPSConnection, req,
-> 1226                 context=self._context, check_hostname=self._check_hostname)
   1227 
   1228         https_request = AbstractHTTPHandler.do_request_

/usr/lib64/python3.4/urllib/request.py in do_open(self, http_class, req, **http_conn_args)
   1183                 h.request(req.get_method(), req.selector, req.data, headers)
   1184             except OSError as err: # timeout error
-> 1185                 raise URLError(err)
   1186             r = h.getresponse()
   1187         except:

URLError: <urlopen error [Errno -3] Temporary failure in name resolution>

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

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.

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 similar vectorized operations by specifying the individual Series in the DataFrame. New columns can be assigned in the same way.

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

NameError: name 'tips' is not defined

In [9]: tips['new_bill'] = tips['total_bill'] / 2.0
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-9-148d24098b60> in <module>()
----> 1 tips['new_bill'] = tips['total_bill'] / 2.0

NameError: name 'tips' is not defined

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

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 [11]: tips[tips['total_bill'] > 10].head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-11-0d501c078554> in <module>()
----> 1 tips[tips['total_bill'] > 10].head()

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 [12]: tips['bucket'] = np.where(tips['total_bill'] < 10, 'low', 'high')
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-12-dc36aec0e1e5> in <module>()
----> 1 tips['bucket'] = np.where(tips['total_bill'] < 10, 'low', 'high')

NameError: name 'tips' is not defined

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

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 and custom offsets) - see the timeseries documentation for more details.

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

NameError: name 'tips' is not defined

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

NameError: name 'tips' is not defined

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

NameError: name 'tips' is not defined

In [17]: tips['date2_month'] = tips['date2'].dt.month
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-17-45457f2388b5> in <module>()
----> 1 tips['date2_month'] = tips['date2'].dt.month

NameError: name 'tips' is not defined

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

NameError: name 'tips' is not defined

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

NameError: name 'tips' is not defined

In [20]: tips[['date1','date2','date1_year','date2_month',
   ....:       'date1_next','months_between']].head()
   ....: 
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-20-e5a10208e0f6> in <module>()
----> 1 tips[['date1','date2','date1_year','date2_month',
      2       'date1_next','months_between']].head()
      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
In [21]: tips[['sex', 'total_bill', 'tip']].head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-21-3fd2a7fe5ed5> in <module>()
----> 1 tips[['sex', 'total_bill', 'tip']].head()

NameError: name 'tips' is not defined

# drop
In [22]: tips.drop('sex', axis=1).head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-22-0aa2dc1f187e> in <module>()
----> 1 tips.drop('sex', axis=1).head()

NameError: name 'tips' is not defined

# rename
In [23]: tips.rename(columns={'total_bill':'total_bill_2'}).head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-23-ee45ae48cb6c> in <module>()
----> 1 tips.rename(columns={'total_bill':'total_bill_2'}).head()

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 objects have a sort_values() method, which takes a list of columns to sort by.

In [24]: tips = tips.sort_values(['sex', 'total_bill'])
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-24-5419d1fabdcc> in <module>()
----> 1 tips = tips.sort_values(['sex', 'total_bill'])

NameError: name 'tips' is not defined

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

NameError: name 'tips' is not defined

String Processing¶

Length¶

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;

Python determines the length of a character string with the len function. len includes trailing blanks. Use len and rstrip to exclude trailing blanks.

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

NameError: name 'tips' is not defined

In [27]: tips['time'].str.rstrip().str.len().head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-27-61f535a15695> in <module>()
----> 1 tips['time'].str.rstrip().str.len().head()

NameError: name 'tips' is not defined

Find¶

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;

Python determines the position of a character in a string with the find function. find searches for the first position of the substring. If the substring is found, the function returns its position. Keep in mind that Python indexes are zero-based and the function will return -1 if it fails to find the substring.

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

NameError: name 'tips' is not defined

Substring¶

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 [29]: tips['sex'].str[0:1].head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-29-560ab9d35724> in <module>()
----> 1 tips['sex'].str[0:1].head()

NameError: name 'tips' is not defined

Scan¶

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;

Python extracts a substring from a string based on its text by using regular expressions. There are much more powerful approaches, but this just shows a simple approach.

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

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

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

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

Upcase, Lowcase, and Propcase¶

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 Python functions are upper, lower, and title.

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

In [35]: firstlast['string_up'] = firstlast['String'].str.upper()

In [36]: firstlast['string_low'] = firstlast['String'].str.lower()

In [37]: firstlast['string_prop'] = firstlast['String'].str.title()

In [38]: firstlast
Out[38]: 
       String   string_up  string_low string_prop
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 [39]: df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'],
   ....:                     'value': np.random.randn(4)})
   ....: 

In [40]: df1
Out[40]: 
  key     value
0   A -0.857326
1   B  1.075416
2   C  0.371727
3   D  1.065735

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

In [42]: df2
Out[42]: 
  key     value
0   B -0.227314
1   D  2.102726
2   D -0.092796
3   E  0.094694

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. Note that the data does not have to be sorted ahead of time, and different join types are accomplished via the how keyword.

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

In [44]: inner_join
Out[44]: 
  key   value_x   value_y
0   B  1.075416 -0.227314
1   D  1.065735  2.102726
2   D  1.065735 -0.092796

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

In [46]: left_join
Out[46]: 
  key   value_x   value_y
0   A -0.857326       NaN
1   B  1.075416 -0.227314
2   C  0.371727       NaN
3   D  1.065735  2.102726
4   D  1.065735 -0.092796

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

In [48]: right_join
Out[48]: 
  key   value_x   value_y
0   B  1.075416 -0.227314
1   D  1.065735  2.102726
2   D  1.065735 -0.092796
3   E       NaN  0.094694

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

In [50]: outer_join
Out[50]: 
  key   value_x   value_y
0   A -0.857326       NaN
1   B  1.075416 -0.227314
2   C  0.371727       NaN
3   D  1.065735  2.102726
4   D  1.065735 -0.092796
5   E       NaN  0.094694

Missing Data¶

Like SAS, pandas has a representation for missing data - which is 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 [51]: outer_join
Out[51]: 
  key   value_x   value_y
0   A -0.857326       NaN
1   B  1.075416 -0.227314
2   C  0.371727       NaN
3   D  1.065735  2.102726
4   D  1.065735 -0.092796
5   E       NaN  0.094694

In [52]: outer_join['value_x'] + outer_join['value_y']
Out[52]: 
0         NaN
1    0.848102
2         NaN
3    3.168461
4    0.972939
5         NaN
dtype: float64

In [53]: outer_join['value_x'].sum()
Out[53]: 2.72128653544262

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;

Which doesn’t work in in pandas. Instead, the pd.isna or pd.notna functions should be used for comparisons.

In [54]: outer_join[pd.isna(outer_join['value_x'])]
Out[54]: 
  key  value_x   value_y
5   E      NaN  0.094694

In [55]: outer_join[pd.notna(outer_join['value_x'])]
Out[55]: 
  key   value_x   value_y
0   A -0.857326       NaN
1   B  1.075416 -0.227314
2   C  0.371727       NaN
3   D  1.065735  2.102726
4   D  1.065735 -0.092796

pandas also provides a variety of methods to work with missing data - some of which would be challenging to express in SAS. For example, there are methods to drop all rows with any missing values, replacing missing values with a specified value, like the mean, or forward filling from previous rows. See the missing data documentation for more.

In [56]: outer_join.dropna()
Out[56]: 
  key   value_x   value_y
1   B  1.075416 -0.227314
3   D  1.065735  2.102726
4   D  1.065735 -0.092796

In [57]: outer_join.fillna(method='ffill')
Out[57]: 
  key   value_x   value_y
0   A -0.857326       NaN
1   B  1.075416 -0.227314
2   C  0.371727 -0.227314
3   D  1.065735  2.102726
4   D  1.065735 -0.092796
5   E  1.065735  0.094694

In [58]: outer_join['value_x'].fillna(outer_join['value_x'].mean())
Out[58]: 
0   -0.857326
1    1.075416
2    0.371727
3    1.065735
4    1.065735
5    0.544257
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 [59]: tips_summed = tips.groupby(['sex', 'smoker'])['total_bill', 'tip'].sum()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-59-26485120ebb1> in <module>()
----> 1 tips_summed = tips.groupby(['sex', 'smoker'])['total_bill', 'tip'].sum()

NameError: name 'tips' is not defined

In [60]: tips_summed.head()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-60-3d0a2a6150c9> in <module>()
----> 1 tips_summed.head()

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 groubpy provides a transform mechanism that allows these type of operations to be succinctly expressed in one operation.

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

NameError: name 'tips' is not defined

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

NameError: name 'tips' is not defined

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

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 [64]: tips.groupby(['sex','smoker']).first()
---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-64-b7fb17f3ee9b> 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

Table Of Contents

Search

Comparison with SAS¶

Data Structures¶

General Terminology Translation¶

`DataFrame` / `Series`¶

`Index`¶

Data Input / Output¶

Constructing a DataFrame from Values¶

Reading External Data¶

Exporting Data¶

Data Operations¶

Operations on Columns¶

Filtering¶

If/Then Logic¶

Date Functionality¶

Selection of Columns¶

Sorting by Values¶

String Processing¶

Length¶

Find¶

Substring¶

Scan¶

Upcase, Lowcase, and Propcase¶

Merging¶

Missing Data¶

GroupBy¶

Aggregation¶

Transformation¶

By Group Processing¶

Other Considerations¶

Disk vs Memory¶

Data Interop¶

Table Of Contents

Search

Comparison with SAS¶

Data Structures¶

General Terminology Translation¶

DataFrame / Series¶

Index¶

Data Input / Output¶

Constructing a DataFrame from Values¶

Reading External Data¶

Exporting Data¶

Data Operations¶

Operations on Columns¶

Filtering¶

If/Then Logic¶

Date Functionality¶

Selection of Columns¶

Sorting by Values¶

String Processing¶

Length¶

Find¶

Substring¶

Scan¶

Upcase, Lowcase, and Propcase¶

Merging¶

Missing Data¶

GroupBy¶

Aggregation¶

Transformation¶

By Group Processing¶

Other Considerations¶

Disk vs Memory¶

Data Interop¶

`DataFrame` / `Series`¶

`Index`¶