SASPy Sample Program (1) Basic Functions¶
Eunji Oh (WRDS), June 2020¶
Requirements:¶
- Jupyter Lab
- WRDS Jupyter Hub (https://wrds-jupyter.wharton.upenn.edu)
- Python packages: wrds, saspy(version: 3.3.5 or higher), pandas
- Subscriptions for CRSP, COMPUSTAT
0. Select Python 3 Kernel (The default kernel on WRDS Jupyter Hub)¶
1. Import packages¶
In [1]:
import wrds
import saspy
from IPython.display import HTML
import pandas as pd
import datetime
2. SAS: Work with SASPy¶
2.1 Start a SAS Session¶
In [2]:
# Start a SAS session and check configuration information
sas = saspy.SASsession(cfgname='default') # Available configurations: 'default', 'sasu8'
sas
Out[2]:
2.2 Set a library reference¶
In [ ]:
# Set a library reference to home directory
home = sas.saslib(libref='home', path='~')
scratch_dir = '/scratch/' + 'your-instituion-nickname' # Please see: https://wrds-www.wharton.upenn.edu/pages/support/the-wrds-cloud/managing-data/storing-your-data-wrds-cloud/
scrt = sas.saslib(libref='scrt', path=scratch_dir)
In [4]:
# Check assinged libraries
assigned_librefs = sas.assigned_librefs() # Check all assigned libraries
print(assigned_librefs)
2.3 Access SAS Datasets¶
2.3.1 Access a SAS dataset¶
In [5]:
# Access SAS Datasets
## Set dataset options (SAS datastep options)
### Available options: where (str), keep (str or list), drop (str or list), firstobs (int or str), obs (int or str), format (dict)
options = {'where' : 'fyear = 2018 and indfmt="INDL" and datafmt="STD" and popsrc="D" and consol="C"',
'keep' : 'gvkey fyear fyr datadate indfmt datafmt popsrc consol at lt ni',
'format' : {'datadate':'YYMMDDN8.'}
}
sd = sas.sasdata(table='funda', libref='compa', dsopts=options)
2.3.2 SAS Data object methods¶
In [6]:
# Summarize
sd.describe()
Out[6]:
In [7]:
# First 5 obs
sd.head()
Out[7]:
In [8]:
# Last 5 obs
sd.tail()
Out[8]:
In [9]:
# Histogram
sd.hist(var='ni', title = 'Net Income histogram (Fiscal year 2018)', label = 'NI')
In [10]:
# To see the last SAS log
print(sas.lastlog())
In [11]:
# Sort
sd.sort(by = 'gvkey fyear', out='funda_copy') # This code raises an error!
By sd.sort, the syntax below runs in the SAS session.
- proc sort data=compa.funda(where=...) ; format datadate yymmddn8. ; out=funda_2018;run;
which is invalide sort procedure syntax!
This is because SASPy's _dsopts() method generates dataset options that cannot be used properly in proc sort as shown below.
In [12]:
sd._dsopts()
Out[12]:
Thus, use sort method for a SASdata object without dsopts to avoid the error or use submit method instead. Please see section 4 below for 'submit' method.
In [13]:
# Sort
new_sd = sas.sasdata(table='funda', libref='compa')
new_sd.sort(by = 'gvkey fyear', out = 'funda_copy') # This code does not raise any error!
print(sas.lastlog())
2.4 Exchange data between SAS Dataset and Pandas Dataframe¶
2.4.1 SASPy SAS Data Object to Pandas DataFrame¶
In [14]:
df_from_sd = sd.to_df()
df_from_sd.head()
type(df_from_sd), type(sd)
Out[14]:
2.4.2 From the dataset location (using table name and library reference name)¶
In [15]:
df_from_sas_ds = sas.sd2df(table = 'idx_index', libref = 'compa', method = 'memory') # method: [memory, CSV, DISK], DISK is recommended for large datasets
df_from_sas_ds.head()
Out[15]:
2.4.3 From Pandas Dataframe to a SAS dataset¶
In [16]:
db = wrds.Connection()
wrds_table = db.get_table(library = 'crsp', table = 'msi')
# Convert a pandas df to a SAS dataset
wrds_table_sd = sas.df2sd(df = wrds_table, table = 'wrds_table', libref='work')
# Check if 'wrds_table' is correctly created
print(sas.lastlog())
In [17]:
wrds_table_sd.head()
Out[17]:
3. Submit a SAS job¶
3.1 Submit SAS code¶
In [18]:
# Submit the job (Add code that gives both log and output)
sas_code = '''%let i=1; %let j=%eval(&i*100+3);
proc odstext;
title "SAS Output";
p "&j is calculated by the formula (i*100 + 3) where i is &i";
run;
'''
results = sas.submit(sas_code)
In [19]:
print(results['LOG'])
In [20]:
HTML(results['LST'])
Out[20]:
In [21]:
# Submit and print LOG
sas.submitLOG(sas_code)
In [22]:
# Submit and show OUTPUT
sas_code = '''proc means data=compa.funda; var at lt; where fyear=2018;run; quit;'''
sas.submitLST(sas_code)
3.2 Submit from a sas file (SAS %include)¶
In [ ]:
# Let's run a WRDS SAS macro for Event Study (SAS macro: https://wrds-www.wharton.upenn.edu/pages/support/applications/event-studies/event-study-research-application/)
# Run your SAS macro in the SASPy SAS Sessioin by submitting %include code
sas.submitLOG('''%include "~/SASPy_Application/SAS_macro/event_study.sas";''')
3.3 Jupyter Magic: Work like you were using SAS¶
The first line of the cell must start with "%%SAS session_name"
In [24]:
%%SAS sas
/* Get S&P 500 companies*/
proc sql;
create table crspd500 as
select a.*, b.ret, b.date
from crsp.dsp500list as a,
crsp.dsf as b
where a.permno=b.permno
and b.date >= a.start and b.date<= a.ending
order by date;
quit;
Out[24]:
4. Exchange macro variables¶
In [25]:
#From SAS to Python (int, float, str types are available)
sas.submitLST('''%let today = %sysfunc(today(),date9.);
%let num_var=10;
proc odstext;
title SAS Macro Variables;
mvar today num_var;
p "SAS Macro variable ''today'': "|| today;
p "SAS Macro variable ''num_var'': "|| num_var;
run;
''')
In [26]:
today = sas.symget('today')
num_var = sas.symget('num_var')
print ('Transfer SAS macro variables to Python session\n','today:', today, 'type:', type(today), 'num_var:', num_var, 'type:', type(num_var))
In [27]:
# Create and assign value to a SAS macro var from Python
tomorrow = datetime.date.today() + datetime.timedelta(days = 1)
sas.symput(name='tomorrow_py', value=tomorrow)
sas.submitLST('''proc odstext;
title "Python to SAS";
p "&tomorrow_py from Python, &today from SAS";
run;
''')
In [28]:
# Change value of a SAS macro var
sas.symput(name='tomorrow_py', value='06262020')
sas.submitLST('''proc odstext;
title "Change value from Python";
p "&tomorrow_py from Python, &today from SAS";
run;
''')
5. Connect to WRDS via PC-SAS Connect from your Local Computer (Skip if you work on WRDS Jupyter Hub)¶
In [ ]:
# PC-SAS connect
PC_SAS = '''%let wrds = wrds.wharton.upenn.edu 4016;
options comamid=TCP remote=wrds;
signon username="&username" password="&pw";
'''
prompt = {'username':True, 'pw': True}
sas.submitLOG(code=PC_SAS, prompt=prompt)
# Submit SAS code by 'submit' method
main_code = '''your main sas code to run on WRDS server'''
main_code_remote = ' '.join['rsubmit;', main_code, 'endrsubmit;']
sas.submit(main_code_remote)
# Disconnect from the remote SAS session. Note that this does not end your LOCAL SASPY SAS session.
sas.submit('''signoff;''')
6. Finish the sas session and WRDS db connection¶
In [29]:
# Finish the SAS
sas.endsas()
In [30]:
db.close()