How to Collapse Multiple Records into a Single Record
By Bryan K. Beverly,
BAE Systems Information Technology

PROBLEM: In data processing, it is often necessary to reconcile records from several time points into a single record that contains the data from all time points.

SOLUTION: One way of collapsing multiple records into a single record is demonstrated below. Using an input file consisting of multiple records per ID (data set BEFORE_DB), the code:

1) sorts the file by ID
2) splits the sorted file into master and transaction files
3) uses the UPDATE statement to merge the transaction records into the master records

CAVEAT: This approach may have to be tweaked based upon the number of sort keys or if there are special requirements for handling missing values.
/* INPUT DATA SET
*/data BEFORE_DB;
  id=1;
  var1=1;
  var2=.;
  var3=.;
  output;
  id=1;
  var1=.;
  var2=1;
  var3=.;
  output;
  id=1;
  var1=.;
  var2=.;
  var3=1;
  output;
  id=2;
  var1=2;
  var2=.;
  var3=2;
  output;
  id=2;
  var1=.;
  var2=2;
  var3=.;
  output;
  id=2;
  var1=.;
  var2=.;
  var3=.;
  output;run;
/* STEP 1 - Sort the file by ID */
proc sort data=BEFORE_DB out=SORTED_DB;
  by id;
run;
/* STEP 2 - Put all of the first occurrences */
/* into a master data set and all of the others */
/* into a transaction data set */
data MASTER TRANS;
  set SORTED_DB;
  by id;
  if first.id  then output MASTER;
  else output TRANS;
run;
/* STEP 3 - Use the UPDATE statement to put all */
/* data on the same records. */
/* The MODIFY statement is also an option based */
/* upon how much control you need for missing */
/* variables - or for overwriting fields where */
/* new values are updating old values *
data AFTER_DB;
  update MASTER TRANS;
  by id;
run;

proc print data=BEFORE_DB;
  title 'Before Collapse';
run;

proc print data=AFTER_DB;
  title 'After Collapse';
run;

SAS and all other SAS Institute Inc. product or service names are registered trademarks \or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies. Copyright © 2007, SAS Institute Inc. All rights reserved.