merge by date does not work

xyxu — Fri, 12 Oct 2018 02:09:29 GMT

I am trying to merge two datasets, data1 and data2, based on two variables, ID and date_month. In both datasets, the two variables have identical length, format and informat. However, the simple merge code as follows does not work, because the same ID-date_month show up as separate observations in the outcome.

data want;
	merge data1 data2;
	by ID date_month;
run;

I think the issue is with variable date_month. It is a YYMMN6. format variable converted from a YYMMDD10. date variable with command

date_month = date;
format date_month yymmn6.;

Is there any date feature in SAS that gives rise to my problem?

P.S.: It worked perfectly if I convert date as follows

date_month = intnx('month', date, 0, 'e');

Re: merge by date does not work

ChrisNZ — Fri, 12 Oct 2018 02:40:39 GMT

You gave the answer to your own question:

It worked perfectly if I convert date as follows

DATE_MONTH = intnx('month', DATE, 0, 'e');

The values are not the same. They are displayed the same when using a format that only shows the month, but one is a day somewhere in the month while the other is the last day.

1. To show the actual value, format as date9.

2. To merge, align the values or use proc sql:

join ...  on intnx('month', data1.DATE, 0, 'e') = intnx('month', data2.DATE, 0, 'e');

topic Re: merge by date does not work in SAS Programming

merge by date does not work

Re: merge by date does not work