Hello,
I have two dataset and in each there are two diff dates, one which reports end of the month and other end of the business week
data a: ( reports end of the month date)
ID | Date | Val |
1 | 1/31/2012 | -0.47 |
1 | 2/29/2012 | 0.73 |
1 | 3/31/2012 | -0.948 |
1 | 4/30/2012 | -0.74 |
1 | 5/31/2012 | 6 |
1 | 6/30/2012 | -4 |
1 | 7/31/2012 | 2 |
1 | 8/31/2012 | -2 |
1 | 9/30/2012 | -0.7 |
1 | 10/31/2012 | -1.063 |
1 | 11/30/2012 | 1.46 |
1 | 12/31/2012 | 3.3 |
data b: ( reports end of the month during closing of the business week day.. no weekends)
ID | Date | closing |
1 | 1/31/2012 | 9.52 |
1 | 2/29/2012 | 9.59 |
1 | 3/30/2012 | 9.5 |
1 | 4/30/2012 | 9.43 |
1 | 5/31/2012 | 10 |
1 | 6/29/2012 | 9.56 |
1 | 7/31/2012 | 9.77 |
1 | 8/31/2012 | 9.57 |
1 | 9/28/2012 | 9.5 |
1 | 10/31/2012 | 9.4 |
1 | 11/30/2012 | 9.54 |
1 | 12/31/2012 | 9.78 |
with the current code, when I join the two dataset I get:
Data sample;
Merge a (in=x) b (in=z);
by id date;
run;
ID | Date | Val |
1 | 1/31/2012 | 9.52 |
1 | 2/29/2012 | 9.59 |
1 | 3/30/2012 | 9.5 |
1 | 3/31/2012 | . |
1 | 4/30/2012 | 9.43 |
1 | 5/31/2012 | 10 |
1 | 6/29/2012 | 9.56 |
1 | 6/30/2012 | . |
1 | 7/31/2012 | 9.77 |
1 | 8/31/2012 | 9.57 |
1 | 9/28/2012 | 9.5 |
1 | 9/30/2012 | . |
1 | 10/31/2012 | 9.4 |
1 | 11/30/2012 | 9.54 |
1 | 12/31/2012 | 9.78 |
But what I want is:
ID | Date | Val | closing |
1 | 2012/01 | -0.47 | 9.52 |
1 | 2012/02 | 0.73 | 9.59 |
1 | 2012/03 | -0.948 | 9.5 |
1 | 2012/04 | -0.74 | 9.43 |
1 | 2012/05 | 6 | 10 |
1 | 2012/06 | -4 | 9.56 |
1 | 2012/07 | 2 | 9.77 |
1 | 2012/08 | -2 | 9.57 |
1 | 2012/09 | -0.7 | 9.5 |
1 | 2012/10 | -1.063 | 9.4 |
1 | 2012/11 | 1.46 | 9.54 |
1 | 2012/12 | 3.3 | 9.78 |
It appears that your rule is to match the two sets where Month and Year align.
I may be better to use an SQL join than a data step merge since the values aren't always equal.
data a; input id date mmddyy10. val; format date mmddyy10.; datalines; 1 1/31/2012 -0.47 1 2/29/2012 0.73 1 3/31/2012 -0.948 1 4/30/2012 -0.74 1 5/31/2012 6 1 6/30/2012 -4 1 7/31/2012 2 1 8/31/2012 -2 1 9/30/2012 -0.7 1 10/31/2012 -1.063 1 11/30/2012 1.46 1 12/31/2012 3.3 ; run; data b; input id date mmddyy10. closing; format date mmddyy10.; datalines; 1 1/31/2012 9.52 1 2/29/2012 9.59 1 3/30/2012 9.5 1 4/30/2012 9.43 1 5/31/2012 10 1 6/29/2012 9.56 1 7/31/2012 9.77 1 8/31/2012 9.57 1 9/28/2012 9.5 1 10/31/2012 9.4 1 11/30/2012 9.54 1 12/31/2012 9.78 ; run; proc sql; create table want as select a.id,a.date ,a.val, b.closing from a left join b on a.id = b.id and intnx('month',a.date,0,'B') = intnx('month',b.date,0,'B') ; quit;
It appears that your rule is to match the two sets where Month and Year align.
I may be better to use an SQL join than a data step merge since the values aren't always equal.
data a; input id date mmddyy10. val; format date mmddyy10.; datalines; 1 1/31/2012 -0.47 1 2/29/2012 0.73 1 3/31/2012 -0.948 1 4/30/2012 -0.74 1 5/31/2012 6 1 6/30/2012 -4 1 7/31/2012 2 1 8/31/2012 -2 1 9/30/2012 -0.7 1 10/31/2012 -1.063 1 11/30/2012 1.46 1 12/31/2012 3.3 ; run; data b; input id date mmddyy10. closing; format date mmddyy10.; datalines; 1 1/31/2012 9.52 1 2/29/2012 9.59 1 3/30/2012 9.5 1 4/30/2012 9.43 1 5/31/2012 10 1 6/29/2012 9.56 1 7/31/2012 9.77 1 8/31/2012 9.57 1 9/28/2012 9.5 1 10/31/2012 9.4 1 11/30/2012 9.54 1 12/31/2012 9.78 ; run; proc sql; create table want as select a.id,a.date ,a.val, b.closing from a left join b on a.id = b.id and intnx('month',a.date,0,'B') = intnx('month',b.date,0,'B') ; quit;
That worked!!! Can you please explain me this
intnx('month',a.date,0,'B') = intnx('month',b.date,0,'B')
Thanks!
You need GROUPFORMAT option.
data a;
input id date mmddyy10. val;
format date mmddyy10.;
datalines;
1 1/31/2012 -0.47
1 2/29/2012 0.73
1 3/31/2012 -0.948
1 4/30/2012 -0.74
1 5/31/2012 6
1 6/30/2012 -4
1 7/31/2012 2
1 8/31/2012 -2
1 9/30/2012 -0.7
1 10/31/2012 -1.063
1 11/30/2012 1.46
1 12/31/2012 3.3
;
run;
data b;
input id date mmddyy10. closing;
format date mmddyy10.;
datalines;
1 1/31/2012 9.52
1 2/29/2012 9.59
1 3/30/2012 9.5
1 4/30/2012 9.43
1 5/31/2012 10
1 6/29/2012 9.56
1 7/31/2012 9.77
1 8/31/2012 9.57
1 9/28/2012 9.5
1 10/31/2012 9.4
1 11/30/2012 9.54
1 12/31/2012 9.78
;
run;
Data sample;
Merge a (in=x) b (in=z);
by id date groupformat;
format date monyy7.;
run;
%let start=%sysfunc(intnx(month,%sysfunc(today()),-60));
%put &start;
...........
if date > &start ;
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.