Dataset from Merge creating more rows than in original statement?

JonathanWarrick — Tue, 30 Nov 2010 21:22:31 GMT

I am trying to merge a table that has values in the TIER column with a new table I received that has updated some of the TIER fields. Just to get a quick look at which changed and which didn't, I wanted a simple table to compare the two, but noticed when I merged the datasets, the number of records increased by 13. I am trying to figure out why this is the case. Additionally, I noticed the warning that says there are more than one datasets with repeats of BY values, so that is probably a reason. The New_Tiers dataset only has two variables - ID and Tier_Update. Here is the log:

3633 data portal_&begin_date._&end_date._b1;
3634 merge portal_&begin_date._&end_date._b (rename = (tier=tier1) in=in1) New_Tiers (rename = (tier_update=tier2) in=in2);
3635 by ID;
3636 if in1;
3637 run;

NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: There were 1085757 observations read from the data set WORK.PORTAL_090109_103110_B.
NOTE: There were 617 observations read from the data set WORK.NEW_TIERS.
NOTE: The data set WORK.PORTAL_090109_103110_B1 has 1085770 observations and 71 variables.

Any explanation on the repeat BY values and the 13 additional records popping up would be greatly appreciated. Message was edited by: JonathanWarrick

Re: Dataset from Merge creating more rows than in original statement?

ArtC — Tue, 30 Nov 2010 23:13:22 GMT

When merging, the BY statement variables are used to align the rows of the incoming tables. For the merge to be successful the BY variables should form a unique key (identify down to the row level) in all but at most one data set. Here you have two data sets and one BY variable (ID). The message tells me that ID is not unique in either data set (neither identifies down to the row level). Another BY variable is needed to successfully merge.

The increased number of rows is a related but different issue. When a merge takes place the resulting table will have at least one row for each ID variable from either data set. In your case since you are selecting for observations in the first data set (if in1;) the extra obs must be coming from repeated values of ID in the second data set.

topic Re: Dataset from Merge creating more rows than in original statement? in SAS Procedures

Dataset from Merge creating more rows than in original statement?

Re: Dataset from Merge creating more rows than in original statement?