Topics in Data Management SAS Data Step Combining
Topics in Data Management SAS Data Step
Combining Data Sets I - SET Statement • Data available on common variables from different sources. Multiple datasets with common variable names, possibly different sampling/experimental units – Exam scores from students in various sections of STA 2023 – County level data from different state databases – Flight departure/arrival data from different months
Combining Data Sets I - SET Statement options nodate nonumber ps=54 ls=80; data one; input student $ 1 -8 idnum 9 -12 exam 1 14 -16 exam 2 18 -20 exam 3 22 -24; section=1; cards; Amy 1456 98 78 84 Zed 2234 68 84 75 ; run; data five; input student $ 1 -8 idnum 9 -12 exam 1 14 -16 exam 2 18 -20 exam 3 22 -24; section=5; cards; Alex 3410 74 68. Zach 4561 92 74 88 ; run; data all; set one five; run; proc print; run; quit;
Combining Data Sets I - SET Statement The SAS System Obs 1 2 3 4 student Amy Zed Alex Zach idnum 1456 2234 3410 4561 exam 2 exam 3 98 68 74 92 78 84 68 74 84 75. 88 section 1 1 5 5
Combining Data Sets II - MERGE Statement • Data on common sampling/experimental units, different variables/characteristics measured in different datasets. – County data from different government sources – Store sales data updated over time
Combining Data Sets II - MERGE Statement options nodate nonumber ps=54 ls=80; data s 2003; input store $ 1 -8 sales 03 10 -14; cards; Atlanta 1459 Zurich 1383 ; run; data s 2004; input store $ 1 -8 sales 04 10 -14; cards; Atlanta 1459 Zurich 1383 ; run; proc sort data=s 2003; by store; proc sort data=s 2004; by store; data s 0304; merge s 2003 s 2004; by store; run; proc print; run; quit; The SAS System Obs store sales 03 sales 04 1 2 Atlanta Zurich 1459 1383
Creating New Variables From Existing Ones • Creating Final Grade for Students (Exams 1 and 2 Each Count 30%, Exam 3 40%) – Total = (0. 3*Exam 1)+(0. 3*Exam 2)+(0. 4*Exam 3) • Obtaining Sales Growth (%) for stores – Grow 0403=100*(sales 04 -sales 03)/sales 03
Grades Example data all; set one five; total=(0. 3*exam 1)+(0. 3*exam 2)+(0. 4*exam 3); run; proc print; var student idnum total; run; quit; The SAS System Obs 1 2 3 4 student Amy Zed Alex Zach idnum total 1456 2234 3410 4561 86. 4 75. 6. 85. 0
Building Case Histories • Have multiple observations of same variable on individual units (not necessarily the same number across individuals). • Want to summarize the measurements for each individual and obtain single “record”. – Summary of all Delta flights for each ATL route to other cities for October 2004 – Arrest record for juveniles over a 5 year period – Sales histories for individual stores in a retail chain
Building Case Histories • Step 1: SORT dataset on the variable(s) that define(s) the individual units/cases. • Step 2: Set the previous dataset into a new one, using the same BY statement as in the SORT. – The new dataset “sees” the old dataset as a series of “blocks” of measurements by individual cases • Step 3: Define any variables you want to use to summarize cases in RETAIN statement. • Step 4: At beginning of each individual, reset variables in Step 3 (typically to 0) • Step 5: At end of each individual OUTPUT record
Example - Brookstone Store Sales&Inventory • 8 EXCEL Spreadsheets - 4 Quarters X 2 Measures • 520 stores observed over 52 weeks • Typical Spreadsheet Portion (4 stores X 6 weeks): Note that the company provides 13 columns representing the 13 weeks in the quarter for each store…not the way we want to analyze it. Also, got rid of commas in EXCEL before exporting to text file.
Reading the Data in SAS Data inv 1; infile ‘filename’; input storeid 6 -8 storename $ 10 -38 @; do week=1 to 13; input inv @; output; end; run; This creates 13 “observations” per store and single inv variable
Reading the Data in SAS SET MERGE
Building a Store Record for Year • Suppose Management wants following summary measures for each store: – – Total sales Average sales to inventory ratio Mean and standard deviation of sales Correlation between sales and inventory • We need the following quantities counted across weeks: – SALES, SALES 2, INV 2, SALES*INV, SALES/INV
SAS Code to Obtain Measures by Store (P 1) Data inv; set inv 1 -inv 4; run; proc sort; by storeid; run; Data sales; set sales 1 -sales 4; run; proc sort; by storeid; run; Data invsales; merge inv sales; by storeid; run; proc sort; by storeid; run; Data invsales 1; set invsales; by storeid; retain sumsales 2 suminv 2 salesxinv sales_inv;
If first. storeid then do; sumsales=0; sumsales 2=0; suminv 2=0; salesxinv=0; sales_inv=0; end; sumsales=sumsales+sales; sumsales 2=sumsales 2+(sales**2); suminv=suminv+inv; suminv 2=suminv 2+(inv**2); salesxinv=salesxinv+(sales*inv); sales_inv=sales_inv+(sales/inv); if last. storeid then do; totsales=sumsales; meansal_inv=sales_inv/52; meansales=totsales/52; varsales=(sumsales 2 -(sumsales**2)/51; stdsales=sqrt(varsales); varinv=(suminv 2 -(suminv**2)/51; stdinv=sqrt(varinv); covslinv=(salesxinv-(sumsales*suminv)/52)51; corrslinv=covslinv/(stdsales*stdinv); output; end; run;
- Slides: 16