SASCERTGURU: Proc append in sas

Here we are going to discuss use of ‘FORCE’ option in PROC APPEND.

FORCE option: As per its name, it forces PROC APPEND to concatenate or add or append the data sets in some conditions in which the append procedure would normally fails like: when data sets contains different variables, formats, Lengths and labels.

PROC APPEND without FORCE option (Using different variables in datasets):

Suppose we have two datasets named as Primary and Secondary.

Primary Dataset – 3 Variables (X, Y,Z ) – 252000 observations

Secondary Dataset – 2 Variables (X, Y) - 48000 observations

Now we run the following code:

Proc Append Base=Primary Data=Secondary;

Quit;

We would get error due to non matching variables in Datasets. Error would be thrown only if Base data set would have different rather than secondary dataset.

Case: 1:-

Now if we use FORCE option then it will force to run PROC APPEND and FORCE would drop or truncate the variable as per the Base dataset (Primary).

Proc Append Base=Primary Data=Secondary FORCE;

Quit;

Data would be added to the primary dataset after truncating or dropping the extra variable in secondary dataset as per primary dataset.

Case: 2:- What if our Secondary dataset would have fewer variables than Primary dataset?

FORCE option would force to run PROC APPEND and it will assign missing values for the missing variable’s observation in appended dataset from where secondary dataset have values.

Primary dataset – 3 variables (X, Y, Z) – 252000 observations

Secondary dataset – 2 Variables (X, Y) – 48000 observations

Code:

Proc Append Base=Primary Data=Secondary FORCE;

Quit;

Log:

Result:

FORCE option in PROC APPEND has concatenated both dataset and assigned missing values to missing variables observation in secondary data set with respect to primary data set.

We are familiar with PROC APPEND in SAS to upload data to the data warehouse and it’s attributes, Kindly refer to : Difference Between Set Statement And Proc Append In SAS For Appending Data

Hi,

As we all know that there are three ways to upload data into the Data Warehouse:

Append – Can say :Append to existing

Replace

Update/ Insert

Here, we are just going to explain one way only named as APPEND

What append means: We can say that, append is a process in which data would be added in to a primary table from last observation (from the bottom) from secondary table.

Here we are going to see the difference between two ways of appending the tables in Data warehouse and we would see that which one (Data Step or Proc Step) is more efficient.

Two ways to append the data are:

Set Statement ( In Data Step)

Proc Append ( In Proc Step)

Suppose we have two Data sets named as Primary and Secondary both have three variables and 252000,48000 observations respectively.

Dataset Primary – 3 Variables (X, Y, Z) – 252000 Observations

Dataset Secondary – 3 Variables (X, Y, Z) – 48000 Observations

Just Run a small piece of code:

Data Set_Append;

Set Primary Secondary;

Run;

Combined dataset SET_APPEND would have 300000 observations. If we see the log then we would get to know that SET Statements read both the data set. It means SAS processes the both data set. Log is displaying the CPU timing and Real timings which are 0.17 and 0.18 seconds respectively.

Now try to run a small piece of code again from SAS procedure named as PROC APPEND:

Proc Append Base=Practice Data=Secondary force;

Quit;

Now Primary dataset would have 300000 observations and as per log, Proc append is reading only secondary dataset, it means it is we have saved time from SAS which was in to read primary dataset. Log is displaying this information is clearly that CPU timing and Real timings which are 0.01 and 0.04 seconds respectively.

Some useful differences between Proc Append and Data Step SET Statement while using as Load Style APPEND for data warehouse:

Proc Append is faster than data step SET Statement

We can use proc append for only two tables or datasets

In Data step, Set statement, we can merge any number of tables or datasets

Proc Append uses formats, Informats and Labels from BASE= dataset

Set Statement uses defined formats, informats and labels explicitly

For Set Statement, if same variable is having different length in two or more datasets, then length would be defined as per the first dataset in SET statement

Proc Append, looks for the FORCE option to concatenate and, if length of the variable does not match with primary dataset or table

For description related to FORCE option in PROC APPEND, Kindly refer to:
Why We Use FORCE Option With PROC APPEND In SAS To Upload Data To DataWarehouse

Interview Question

Why We Use FORCE Option With PROC APPEND In SAS To Upload Data To DataWarehouse

Difference Between Set Statement And Proc Append In SAS For Appending Data