DATA Step, Macro, Functions and more

Data Merge not merging

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Data Merge not merging

Hi

I have trying to merge two data sets as indicated below. For the variable I am looking for merge (CohortYearTotalAge), the merge is merging the value for the first record in the sort, but not the remaining. Anyone see anything by any chance? I tried it several ways.

Paul

1st data set:

proc sort data=surrenderTprCount2;

by County Year AgeRange Filing;

run;

2nd data set:

proc sort data=s1Permanency1c;

by County Year AgeRange Filing;

run;

Merge:

data s1Permanency2;

merge s1Permanency1c surrenderTprCount2;

by County Year AgeRange Filing;

run;

Part of the result:

CountyYearAgeRangeFilingExitExitMonthCategoryCohortYearTotalCohortYearTotalAgeDistributiveNumberCumulativeNumber
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll01218815700
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll0.512188463211
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll112188463201
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll212188463201
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll312188463212
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll412188463235
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll512188463227
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll6121884632411
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll121218846321021
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll181218846322647
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll241218846322370
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll301218846322696
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll3612188463218114
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll4212188463215129
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll481218846328137
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll541218846325142
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll601218846324146
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll661218846321147
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll721218846324151
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll781218846321152
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll841218846320152
1 - New York State20061 - 0 to 32 - AS Filed, No TPRAll871218846321153

Accepted Solutions
Solution
‎01-28-2014 04:47 PM
Super User
Posts: 5,513

Re: Data Merge not merging

Most likely, the variable in question appears in both incoming data sets when it should only appear in one.  Drop it from one of the data sets.

View solution in original post


All Replies
Super User
Posts: 5,431

Re: Data Merge not merging

Merging data sets without unique by variables (key) is risky business.

By looking at your result, my guess is that you have 22 rows from the first table, and two rows from the second.

What is the desired result?

Data never sleeps
Solution
‎01-28-2014 04:47 PM
Super User
Posts: 5,513

Re: Data Merge not merging

Most likely, the variable in question appears in both incoming data sets when it should only appear in one.  Drop it from one of the data sets.

Regular Contributor
Posts: 216

Re: Data Merge not merging

Posted in reply to Astounding

Hi Astounding

That was it. It was in both, so I had to do a two step merge to accommodate what I was doing.

Thanks a lot!

Paul

Super User
Posts: 5,513

Re: Data Merge not merging

Great!  Also note for future use, you can do this in one step along these lines:

data want;

merge have1 (drop=CohortYearTotalAge)

           have2;

by County Year AgeRange Filing;

run;


Regular Contributor
Posts: 216

Re: Data Merge not merging

Posted in reply to Astounding

Actually its not as much a technical aspect in needing the two steps. Its that dropping the column caused another issue in the merge.

Paul

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 296 views
  • 0 likes
  • 3 in conversation