Merging Data Error

[krg1140]

I have been instructed to:

1. Create a new dataset called “Complete_Data” by merging the datasets “Audit_Data” and “Firm_Data” on the variables GVKEY and FISCAL_YEAR_ENDED.

As always this is my code:
data reg.Complete_Data;
merge reg.Firm_data reg.Audit_data;
by gvkey fiscal_year_ended;
run;

but I'm getting an error that reads ERROR: BY variables are not properly sorted on reg.Audit_Data

What does this mean and how can I alleviate this problem?

[SASKiwi]

The datasets you are merging on aren't sorted in the order you are trying to merge. Sort them first like this:

proc sort data = reg.Firm_data ;
  by gvkey fiscal_year_ended;
run;

[mkeintz]

When you MERGE datasets, they are typically assumed to be sorted by the merge key(s).  This common usage of merge reads each dataset sequentially, which offers a lot of speed advantages.  This is why @SASKiwi told you to sort the relevant files prior to the merge step.

True, merge can also work with unsorted data, but only if the merged datasets are indexed by the single merge key  (or has a compound index on the multiple merge keys).

Avoiding pre-sorting is often why many programmers use PROC SQL to join files, but this approach does not have access to some of the tools available in merge (like the first. and last. dummy variables, or lag functions).

[mkeintz]

Sorry, duplicate postings from malfunctioning browser (posting gave false negative, eventually generating "504 Error, request could not be satisfied").

When you MERGE datasets, they are typically assumed to be sorted by the merge key(s).  This common usage of merge reads each dataset sequentially, which offers a lot of speed advantages.  This is why @SASKiwi told you to sort the relevant files prior to the merge step.

True, merge can also work with unsorted data, but only if the merged datasets are indexed by the single merge key  (or has a compound index on the multiple merge keys).

Avoiding pre-sorting is often why many programmers use PROC SQL to join files, but this approach does not have access to some of the tools available in merge (like the first. and last. dummy variables, or lag functions.

[mkeintz]

Sorry, duplicate postings from malfunctioning browser user:

When you MERGE datasets, they are typically assumed to be sorted by the merge key(s).  This common usage of merge reads each dataset sequentially, which offers a lot of speed advantages.  This is why @SASKiwi told you to sort the relevant files prior to the merge step.

True, merge can also work with unsorted data, but only if the merged datasets are indexed by the single merge key  (or has a compound index on the multiple merge keys).

Avoiding pre-sorting is often why many programmers use PROC SQL to join files, but this approach does not have access to some of the tools available in merge (like the first. and last. dummy variables, or lag functions.