wrds

1. MERGE STRATEGY — Is hash or sort-merge appropriate? Justify the choice.
2. WHERE CLAUSES — Are all date/string filters index-friendly? No functions on indexed columns.
3. PARALLELISM — Can this job run as an SGE array? Year-by-year is always parallelizable.
4. SQL OPTIMIZATION — For PROC SQL: pass-through opportunity? Indexed join columns?

Writing SAS code that forces full table scans when indexes exist is NOT HELPFUL — the user's job runs 100x slower than necessary and may timeout.

SAS Code Validation Checklist

Before EVERY SAS program execution:

For merges/joins:

• Small lookup + large fact table → hash object (not PROC SORT + DATA merge)
• Hash uses defineKey/defineData/defineDone pattern correctly
• h.output() uses double quotes for macro resolution (not single quotes)
• call missing() initializes hash data variables for non-matches
• Both tables >50M rows → sort-merge is justified (document why)

For WHERE clauses (CRITICAL):

• NO year(date), month(date), datepart(dt) wrapping indexed columns
• Date filters use BETWEEN "01jan&year."d AND "31dec&year."d range pattern
• String filters avoid upcase(), substr() on indexed columns
• Compound date filters collapsed to single range (not year() = X AND quarter() = Y)

For batch processing:

• Multi-year jobs use SGE array (#$ -t start-end) not sequential loop
• Year passed via -sysparm (not -set or %sysget)
• Per-year log files (not single shared log)
• Memory allocation appropriate for workload (#$ -l m_mem_free=4G minimum)
• Single-year benchmark run completed before full array submission

For PROC SQL:

• Join columns are not wrapped in functions
• calculated keyword used for computed column references in HAVING
• Pass-through SQL considered for direct WRDS PostgreSQL queries
• No redundant subqueries that could be hash lookups

For macros:

• Macro variables terminated with period (&year. not &year)
• Double quotes used where macro resolution is needed
• options mprint mlogic symbolgen used during development

SAS Performance Facts

• Hash lookup joins are ~10x faster than PROC SORT + MERGE and need no sorting; PROC SQL still sorts for joins. The hash is 5 extra lines — choosing sort-merge for a lookup join makes the user's job slower for your convenience.
• year(date) (or any function) on an indexed column forces a full table scan over millions of rows; BETWEEN with date literals uses the index.
• Sequential multi-year jobs run ~18x slower than the SGE array (18 years × 3 minutes = 54 minutes sequential vs 3 minutes parallel) — "I'll parallelize later" is anti-efficient on its own terms.
• Single quotes in h.output(dataset: '...') block macro resolution — the output dataset name comes out wrong. Always double quotes.
• %sysget is unreliable under SGE — it may return blank silently. Pass the year via -sysparm + &sysparm..

SAS Red Flags - STOP Immediately If You're About To:

• Write where year(date) = anything → STOP. Use BETWEEN with date literals.
• Write proc sort; data; merge for a lookup join → STOP. Use hash object.
• Write a %do year = start %to end loop → STOP. Use SGE array job.
• Use single quotes in h.output(dataset: '...') → STOP. Use double quotes.
• Submit a full array job without testing one year first → STOP. Benchmark first.
• Use -set or %sysget for SGE task parameters → STOP. Use -sysparm.

SAS Reference

See references/sas-etl.md for complete patterns:

• Hash object merge (basic, multidata, accumulator)
• Index-friendly WHERE clause quick reference table
• SGE array job templates with memory and logging
• PROC SQL pass-through and optimization
• Macro quoting and debugging

Quick Reference: Table Names

Connection

Initialize PostgreSQL connection to WRDS:

import psycopg2

conn = psycopg2.connect(
    host='wrds-pgdata.wharton.upenn.edu',
    port=9737,
    database='wrds',
    sslmode='require'
    # Credentials from ~/.pgpass
)

Configure authentication via ~/.pgpass with chmod 600:

wrds-pgdata.wharton.upenn.edu:9737:wrds:USERNAME:PASSWORD

Connect via SSH tunnel:

ssh wrds

This uses ~/.ssh/wrds_rsa for authentication.

Critical Filters

Compustat Standard Filters

Always include for clean fundamental data:

WHERE indfmt = 'INDL'
  AND datafmt = 'STD'
  AND popsrc = 'D'
  AND consol = 'C'

CRSP v2 Common Stock Filter

Equivalent to legacy shrcd IN (10, 11):

df = df.loc[
    (df.sharetype == 'NS') &
    (df.securitytype == 'EQTY') &
    (df.securitysubtype == 'COM') &
    (df.usincflg == 'Y') &
    (df.issuertype.isin(['ACOR', 'CORP']))
]

Form 4 Transaction Types

WHERE acqdisp = 'D'  -- Dispositions
  AND trancode IN ('S', 'D', 'G', 'F')  -- Sales, Dispositions, Gifts, Tax

Parameterized Queries

Always use parameterized queries (never string formatting):

Use scalar parameter binding for single values:

cursor.execute("""
    SELECT gvkey, conm FROM comp.company WHERE gvkey = %s
""", (gvkey,))

Use ANY() for list parameters:

cursor.execute("""
    SELECT * FROM comp.funda WHERE gvkey = ANY(%s)
""", (gvkey_list,))

Additional Resources

Reference Files

Detailed query patterns and table documentation:

• references/compustat.md - Compustat tables, ExecuComp, financial variables
• references/crsp.md - CRSP stock data, CCM linking, v2 format
• references/insider-form4.md - Thomson Reuters Form 4, rolecodes, insider types
• references/iss-compensation.md - ISS Incentive Lab, peer companies, compensation
• references/formd.md - Form D / Reg D (canonical): two sources (WRDS wrds_vc_formd + SEC EDGAR TSV/XML), grain & keys, denormalization gotcha, exemption + industry codes, post-2020 gap, validated benchmarks
• references/edgar.md - SEC EDGAR filings, URL construction, DCN vs accession numbers
• references/connection.md - Connection pooling, caching, error handling
• references/taq.md - TAQ: master files, IID, raw tick processing (NBBO, VWAP, closing auctions), CRSP–TAQ merge, era transition (legacy vs millisecond)
• references/sas-etl.md - SAS hash objects, index-friendly WHERE, SGE array jobs, PROC SQL optimization
• references/postgres-vs-sas.md - Decision guide: when to use PostgreSQL vs SAS for WRDS ETL (benchmarks, constraints, hybrid pattern)
• references/fjc.md - FJC Integrated Database: civil/criminal case data, NOS codes, securities litigation queries, firm linking
• references/sdc-issuances.md - SDC New Issues: IPOs, SEOs, 144A equity, debt offerings — schema discovery, cleaning filters, CRSP/Compustat linking
• references/fisd-bonds.md - FISD/Mergent: corporate bond issuances, IG vs HY, 144A vs registered, rating classification, TRACE linking
• references/sdc-ma.md - SDC M&A: deal counts, PE/LBO vs strategic buyer, deal status codes, public vs private target
• references/fund-formation.md - Fund formation: Form D (pooled investment funds), EDGAR N-2 (closed-end fund IPOs), Form ADV (RIA registrations)
• references/pitchbook.md - PitchBook: schema architecture, dealsize/fundsize in USD millions, dealdate outliers, CIK crosswalk, fund performance (wrds_fund_returns), PE/VC/fund formation patterns
• references/proxy-advisors.md - Proxy-advisor customer identification: 485BPOS/485APOS body scan for ISS/Glass Lewis/Egan-Jones name variants; CRSP MFDB lift to mgmt_cd × year; validates against chongshu published CSV

Example Files

Working code from real projects:

• examples/form4_disposals.py - Insider trading analysis (from SVB project)
• examples/wrds_connector.py - Connection pooling pattern
• examples/formd_regd.ipynb - Form D / Reg D: dedup validation, SEC TSV download, exemption trend charts
• examples/sdc_issuances_eda.ipynb - SDC New Issues: annual IPO/SEO/debt counts, 144A share, IG vs HY breakdown
• examples/sdc_ma_eda.ipynb - SDC M&A: annual deal counts, PE/LBO vs strategic, public vs private target trends
• examples/fund_formation_eda.ipynb - Fund formation: Form D 3C.1/3C.7 counts, EDGAR N-2 closed-end fund IPOs, Form ADV RIA registrations
• examples/pitchbook_eda.ipynb - PitchBook: PE deal activity, VC rounds by stage, fund formation by vintage, IRR/TVPI by strategy
• examples/voting_ownership_pipeline/ - Self-contained hybrid SAS+Python pipeline: ISS votes, 13-F inst. ownership, MF holdings via MFLINKS, merged panel. Canonical example of PostgreSQL vs SAS decision-making on WRDS. See README.md for architecture and usage.

Scripts

• scripts/test_connection.py - Validate WRDS connectivity

Local Sample Notebooks

WRDS-provided samples at ~/resources/wrds-code-samples/:

• ResearchApps/CCM2025.ipynb - Modern CRSP-Compustat merge
• ResearchApps/ff3_crspCIZ.ipynb - Fama-French factor construction
• comp/sas/execcomp_ceo_screen.sas - ExecuComp patterns

Date Awareness

When querying historical data, leverage current date context for dynamic range calculations.

Current date is automatically available via datetime.now(). Apply this to:

• Data range validation (e.g., "get data for last 5 years")
• Fiscal year calculations
• Event study windows

Implement dynamic date ranges in queries:

from datetime import datetime, timedelta

# Query last 5 years of data
end_date = datetime.now()
start_date = end_date - timedelta(days=5*365)

query = """
SELECT * FROM comp.funda
WHERE datadate BETWEEN %s AND %s
"""
df = pd.read_sql(query, conn, params=(start_date, end_date))

Always incorporate current date awareness in date-dependent queries to ensure results remain fresh across time.