Data Merge not merging

[Paul_NYS]

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:

County	Year	AgeRange	Filing	Exit	ExitMonthCategory	CohortYearTotal	CohortYearTotalAge	DistributiveNumber	CumulativeNumber
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	0	12188	157	0	0
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	0.5	12188	4632	1	1
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	1	12188	4632	0	1
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	2	12188	4632	0	1
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	3	12188	4632	1	2
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	4	12188	4632	3	5
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	5	12188	4632	2	7
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	6	12188	4632	4	11
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	12	12188	4632	10	21
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	18	12188	4632	26	47
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	24	12188	4632	23	70
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	30	12188	4632	26	96
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	36	12188	4632	18	114
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	42	12188	4632	15	129
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	48	12188	4632	8	137
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	54	12188	4632	5	142
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	60	12188	4632	4	146
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	66	12188	4632	1	147
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	72	12188	4632	4	151
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	78	12188	4632	1	152
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	84	12188	4632	0	152
1 - New York State	2006	1 - 0 to 3	2 - AS Filed, No TPR	All	87	12188	4632	1	153

[LinusH]

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?

[Astounding · Accepted solution]

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.

[Paul_NYS]

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

[Astounding]

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;

[Paul_NYS]

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