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 ;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.