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 ;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.