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');
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');
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');
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.