Data manipulation is often required as we organize, optimize, customize, and tweak our data before meaningful analysis can occur.  In my previous series, Coding for Data Quality, I covered data standardization, cleaning, and fuzzy matching techniques. Next, I’ll turn my attention to manipulating data. I’ll be using SAS 9.4 M8 to run my SAS code, but it will all run just as well in the SAS Viya Compute Server.  For data, I’m using Crime Data from 2020 to Present, sourced from data.gov.  In part 1, we’ll focus on appending data.   Appending vs. Concatenating Data   There are two basic methods for adding new records to existing data:   A concatenation reads through the original table, row-by-row, then read all rows from the update table or data sets. The result is a new   An append operation adds rows from an update table to the end of the original table. The result is a larger original   We don’t need to read the rows in the original table, so an append is generally more efficient than a concatenation.   Example code: /* Concatenate the 2020 and 2021 data */ data crime_concatenated;    set Crime_2020 Crime_2021; run; … /* Append the 2021 data to the 2020 data */ proc append base=crime             data=Crime_2021; run;   SAS Log excerpt: 15   data crime_concatenated; NOTE: The data set WORK.CRIME_CONCATENATED has 409125 observations and 21 variables. NOTE: DATA statement used (Total process time):       real time           0.14 seconds 25   proc append base=crime 26               data=Crime_2021; NOTE: The data set WORK.CRIME has 409125 observations and 21 variables. NOTE: PROCEDURE APPEND used (Total process time):       real time           0.08 seconds   The PROC APPEND step took 0.08 seconds to execute, about half the time required to run the DATA step concatenation.   SQL INSERT INTO   Another option is to use an SQL INSERT INTO statement to add rows to the end of an existing table. Example PROC SQL code:   proc sql; insert into crime    select * from crime_2021 ; quit; SAS Log excerpt: 41   /* Insert rows with PROC SQL */ 42   proc sql; 43   insert into crime 44      select * from crime_2021 45   ; NOTE: 209552 rows were inserted into WORK.CRIME. NOTE: PROCEDURE SQL used (Total process time):       real time          0.22 seconds The PROC FedSQL results are a bit longer still: NOTE: PROCEDURE FEDSQL used (Total process time):       real time           0.62 seconds   SQL INSERT INTO underperformed both PROC APPEND and the DATA step concatenation. I generally do not choose this method unless circumstances require the use of SQL.   Appending Tables with Disparate Columns   For a straight-forward addition of new rows to an existing table, the efficiency winner is clearly PROC APPEND. But what if the tables don’t contain all of the same columns?   Code: proc append base=crime             data=Crime_2022; run; SAS Log excerpt: NOTE: Appending WORK.CRIME_2022 to WORK.CRIME. WARNING: Variable Status_Desc was not found on BASE file. The variable will          not be added to the BASE file. ERROR: No appending done because of anomalies listed above. Use FORCE option        to append these files.   Well, that didn’t work!   Use the FORCE   If I don’t care about keeping the new column, I can use the FORCE option to append the new data without including the new column.   Code: proc append base=crime             data=Crime_2022 FORCE; run; SAS Log excerpt: NOTE: Appending WORK.CRIME_2022 to WORK.CRIME. WARNING: Variable AREA_NAME was not found on BASE file. The variable will          not be added to the BASE file. NOTE: FORCE is specified, so dropping/truncating will occur. NOTE: The data set WORK.CRIME has 643810 observations and 21 variables. NOTE: PROCEDURE APPEND used (Total process time):       real time           0.09 seconds   The extra column, AREA_NAME, is not included in the concatenated results.   Use the DROP= dataset option   I could have achieved the same result using a more familiar technique: the DROP= dataset option. Code: proc append base=crime             data=Crime_2022(drop=AREA_NAME); run; SAS Log excerpt: NOTE: Appending WORK.CRIME_2022 to WORK.CRIME. NOTE: There were 234685 observations read from the data set WORK.CRIME_2022. NOTE: 234685 observations added. NOTE: The data set WORK.CRIME has 643810 observations and 20 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 0.09 seconds That works, too, and the efficiency is comparable to using the FORCE option. But, what if I need to keep the new column? I have a couple of choices. Alter the base table structure, then append I could use PROC SQL to alter the base table structure and add the new column, then append the data.   Code: /* Alter the table to add a column */ proc sql;    alter table crime    add Status_Desc char(12); quit; /* Append the new data */ proc append base=crime             data=Crime_2022 force; run;   SAS Log Excerpt: 557  proc sql; 558  alter table crime 559     add Status_Desc char(12); NOTE: Table WORK.CRIME has been modified, with 22 columns. NOTE: PROCEDURE SQL used (Total process time):       real time           0.13 seconds 562  /* Append the new data */ 563  proc append base=crime 564              data=Crime_2022 force; 565  run; NOTE: The data set WORK.CRIME has 643810 observations and 22 variables. NOTE: PROCEDURE APPEND used (Total process time):       real time           0.09 seconds   Note that the ALTER TABLE took longer to run than you might have anticipated. Modifying the number of columns in a table requires physically re-writing the entire table. This process took 0.22 seconds overall.   Create an empty table and append   Another approach is to create a table structure and append all of the data to the empty table.   Code: /* Create an empty table that has all columns */ proc sql; create table crime_new like Crime_2022; quit; /* The append all data sets in sequence */ proc append base=crime_new             data=Crime; run; proc append base=crime_new             data=Crime_2022; run   SAS Log Excerpt: NOTE: Table WORK.CRIME_NEW created, with 0 rows and 21 columns. NOTE: PROCEDURE SQL used (Total process time):       real time          0.04 seconds 11   proc append base=crime 12               data=Crime_2020; 13   run; WARNING: Variable AREA_NAME was not found on DATA file. NOTE: The data set WORK.CRIME has 409125 observations and 21 variables. NOTE: PROCEDURE APPEND used (Total process time):       real time           0.11 seconds 17   proc append base=crime 18               data=Crime_2022; 19   run; NOTE: The data set WORK.CRIME has 643810 observations and 21 variables. NOTE: PROCEDURE APPEND used (Total process time):       real time           0.08 seconds     Total elapsed time was 0.23 seconds, which was about the same as the SQL INSERT INTO.   Use a DATA step concatenation   Code: data crime;    set crime Crime_2022; run; SAS Log Excerpt: 602  data crime; 603     set crime Crime_2022; 604  run; NOTE: The data set WORK.CRIME has 643810 observations and 22 variables. NOTE: DATA statement used (Total process time):       real time           0.15 seconds Note that, this time, the DATA step concatenation was more efficient, taking only 0.15 vs. 0.22 seconds. Often, if the data requires modification of any kind during append processing, a DATA step concatenation is the better choice. What other methods have you used to append one table to another in SAS?   Take-aways: Use PROC APPEND when the tables have all of the same columns When the tables do not all have the same columns, consider a DATA step concatenation. When the tables require modification or data manipulation during concatenation, a DATA step concatenation is usually most performant. PROC SQL INSERT INTO is usually the least performant method for appending rows to a table. References: APPEND Procedure Combining Queries with Set Operators FedSQL Expressions DATA Step SET SAS Tutorial | 5 Ways to Make Your SAS Code Run Faster In my next article, I’ll take a look at sorting data. Until then, may the SAS be with you!   Mark   PS: Download the ZIP file containing a PDF of this blog post and the code you see here at  bit.ly/DataManBaseSAS. I'll add to the zip file as new material posts, but the download link will remain the same.     Find more articles from SAS Global Enablement and Learning here. ... View more