Data Merge not merging

Paul_NYS — Tue, 28 Jan 2014 21:21:09 GMT

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

Re: Data Merge not merging

LinusH — Tue, 28 Jan 2014 21:40:44 GMT

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?

Re: Data Merge not merging

Astounding — Tue, 28 Jan 2014 21:47:53 GMT

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.

Re: Data Merge not merging

Paul_NYS — Wed, 29 Jan 2014 17:21:30 GMT

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

Re: Data Merge not merging

Astounding — Wed, 29 Jan 2014 18:17:13 GMT

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;

Re: Data Merge not merging

Paul_NYS — Thu, 30 Jan 2014 21:31:02 GMT

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

topic Data Merge not merging in SAS Programming

Data Merge not merging

Re: Data Merge not merging

Re: Data Merge not merging

Re: Data Merge not merging

Re: Data Merge not merging

Re: Data Merge not merging