Merging two sets by same month but different days

[LucyDang]

Dear all, I have two data sets as follow:

1. set1: 

data set1;
input ticker $ EventDate EvtMonth AcMonth;
datalines;
AA 09262013 092013 092013
AA 09262013 092013 102013
AA 09262013 092013 112013 
AA 09262013 092013 122013
AA 09262013 092013 012014
AA 09262013 092013 022014
AA 09262013 092013 032014
AA 09262013 092013 042014
AA 09262013 092013 052014
AA 09262013 092013 062014
AA 09262013 092013 072014
AA 09262013 092013 082014
AA 09262013 092013 092014
;
run;

In set1, AcMonth is created based on EventDate using the following code. (Countmonth is from 1 to 12. Thus AcMonth lists 1-12 months for each of the event.)

do Countmonth = 0 to 12;
AccMonth = intnx ('month', EventDate, Countmonth);
format AccMonth yymmn6. ;
output;
end;

  2. Set2: 

data set2;
input ticker $ Date Returns AcMonth;
datalines;
AA 05302013 0.0002 052013
AA 06282013 -0.0003 062013
AA 07292013 0.0003 072013 
AA 08262013 0.0005 082013
AA 09272013 -0.0001 092013
AA 10302013 0.0004 102013
AA 11252013 -0.0001 112013
AA 12212013 -0.0002 122013
AA 01262014 0.0006 012014
AA 02252014 -0.00011 022014
AA 03292014 0.00035 032014
AA 04282014 0.0007 042014
AA 05292014 -0.0002 052014
AA 06292014 0.0003 062014
AA 07292014 0.0002 072014
AA 08292014 0.0001 082014
AA 09292014 -0.0003 092014
AA 10292014 0.0001 102014
AA 11292014 0.0005 112014
AA 12292014 0.0006 122014
AA 01292015 0.0007 012015
;
run;

In set2, the AcMonth is created based on variable "Date" with the code:

data set2;
set set2;
AcMonth =Date;
format AcMonth yymmn6. ;
run;

I want to create a merged data and use the code:

data merged;
merge set1 (in=a) set2(in=b);
by ticker AcMonth;
if a and b;
run;

The code runs successfully but no observation is written. I guess the reason is variable "AcMonth" in set1 and in set2 are different. Although AcMonth in both sets is in the format of MMYYYY, the AcMoth in set1 is formatted based on EventDate variable whereas AcMonth in set2 is formatted based on Date. And EventDate & Date are actually different days in a month of one particular year. 

How can I deal with this problem and successfully merge the two datasets?

Thank you so much!

[learsaas]

by ticker AcMonth groupformat;

[Astounding]

Formatting a variable does not change its value.  It merely changes how the variable is displayed.  So if you want to merge by AcMonth, the values have to match.  To make that happen, switch to:

data set2;
set set2;
AcMonth =intnx('month', Date, 0);
format AcMonth yymmn6. ;
run;

[Kurt_Bremser · Accepted solution]

As posted, your codes can't work, as you do not have valid SAS date values. Just count 9.2 million days from 1960-01-01 and see where that leaves you 😉

It's always good to test code before posting it here to see if it does what you want.

I guess you wanted this

data set1;
input ticker $ EventDate :mmddyy8.;
format
  EventDate mmddyyn8.
  AcMonth yymmn6.
;
EvtMonth = put(EventDate,yymmn6.);
do countmonth = 0 to 12;
  AcMonth = intnx('month',EventDate,countmonth,'b');
  output;
end;
drop countmonth;
datalines;
AA 09262013
;
run;

In your second dataset, do

AcMonth = intnx('month',date,0,'b');

so all period values are aligned to the first day of the respective month.

Or you make AcMonth character by using

put(date,yymmn6.);

[LucyDang]

Thank you, Kurt! I will try my best to successfully run the code before posting. However, sometimes I even don't know why my code does not work :(, so hopefully you may forgive me for this.