topic Re: Merging Data Error in SAS Programming

Merging Data Error

krg1140 — Sat, 27 Mar 2021 01:17:46 GMT

I have been instructed to:

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?

Re: Merging Data Error

SASKiwi — Sat, 27 Mar 2021 01:23:06 GMT

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;

Re: Merging Data Error

mkeintz — Sat, 27 Mar 2021 13:17:23 GMT

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).

Re: Merging Data Error

mkeintz — Sat, 27 Mar 2021 16:19:09 GMT

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

~~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).~~

Re: Merging Data Error

mkeintz — Sat, 27 Mar 2021 16:16:25 GMT

Sorry, duplicate postings from malfunctioning browser user:

~~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).~~