DATA Step, Macro, Functions and more

Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

[ Edited ]

I have two large files. The first one has monthly data and the second one quarterly. I would like to have table1 as a leading table and to merge it with table2. The date_q from table2 has to be at least three months before the MEND in table 1. So basically I need to take the value_q from the most recent quarter that at least three months before MEND.

data table1;
  infile datalines dsd truncover;
  input id:BEST12. MEND:DATE9. value_m:BEST12.;
  format id BEST12. MEND DATE9. value_m BEST12.;
datalines4;
14593,31MAR1999,2.143
14593,30APR1999,1.786
14593,31MAY1999,1.911
14593,30JUN1999,2.048
14593,31JUL1999,2.39
14593,31AUG1999,2.564
14593,30SEP1999,2.827
14593,31OCT1999,2.948
14593,30NOV1999,3.49
14593,31DEC1999,3.887
14593,31JAN2000,4.674
14593,29FEB2000,4.751
14593,31MAR2000,4.953
14593,30APR2000,5.393
14593,31MAY2000,4.902
14593,30JUN2000,4.902
14593,31JUL2000,4.902
14593,31AUG2000,5.102
14593,30SEP2000,4.071
14593,31OCT2000,2.98
14593,30NOV2000,2.98
14593,31DEC2000,1.393
14593,31JAN2001,1.5
14593,28FEB2001,1.67
14593,31MAR2001,1.686
14593,30APR2001,1.959
14593,31MAY2001,2.214
14593,30JUN2001,2.173
14593,31JUL2001,2.089
14593,31AUG2001,1.911
14593,30SEP2001,1.476
14593,31OCT2001,1.457
14593,30NOV2001,1.464
14593,31DEC2001,1.625
14593,31JAN2002,1.786
14593,28FEB2002,1.867
14593,31MAR2002,1.867
14593,30APR2002,2.054
14593,31MAY2002,2.107
14593,30JUN2002,1.853
14593,31JUL2002,1.54
14593,31AUG2002,1.54
14593,30SEP2002,1.405
14593,31OCT2002,1.262
14593,30NOV2002,1.262
14593,31DEC2002,1.238
14593,31JAN2003,1.25
14593,28FEB2003,1.25
14593,31MAR2003,1.277
14593,30APR2003,1.143
14593,31MAY2003,1.214
14593,30JUN2003,1.4
14593,31JUL2003,1.49
14593,31AUG2003,1.48
14593,30SEP2003,1.664
14593,31OCT2003,1.693
14593,30NOV2003,1.658
14593,31DEC2003,1.631
14593,31JAN2004,1.765
14593,29FEB2004,1.765
14593,31MAR2004,1.948
14593,30APR2004,2.208
14593,31MAY2004,2.221
14593,30JUN2004,2.421
14593,31JUL2004,2.538
14593,31AUG2004,2.567
14593,30SEP2004,2.714
14593,31OCT2004,3.471
14593,30NOV2004,4.414
14593,31DEC2004,4.987
14593,31JAN2005,6.036
14593,28FEB2005,6.248
14593,31MAR2005,6.5
14593,30APR2005,7.165
14593,31MAY2005,7.083
14593,30JUN2005,6.701
14593,31JUL2005,6.257
14593,31AUG2005,6.5
14593,30SEP2005,7.89
14593,31OCT2005,8.229
14593,30NOV2005,9.345
14593,31DEC2005,10.804
14593,31JAN2006,13.33
14593,28FEB2006,13.268
14593,31MAR2006,13.241
14593,30APR2006,12.464
14593,31MAY2006,12.408
14593,30JUN2006,11.775
14593,31JUL2006,10.991
14593,31AUG2006,11.055
14593,30SEP2006,12.077
14593,31OCT2006,12.681
14593,30NOV2006,13.305
14593,31DEC2006,13.893
14593,31JAN2007,15.381
14593,28FEB2007,15.518
14593,31MAR2007,15.619
14593,31JAN2007,16.48
14593,28FEB2007,16.48
14593,31MAR2007,16.462
14593,30APR2007,17.513
14593,31MAY2007,18.143
14593,30JUN2007,19.207
14593,31JUL2007,23.969
14593,31AUG2007,24.312
14593,30SEP2007,24.534
14593,31OCT2007,29.914
14593,30NOV2007,29.914
14593,31DEC2007,30.313
14593,31JAN2008,27.379
14593,29FEB2008,26.986
14593,31MAR2008,26.534
14593,30APR2008,28.168
14593,31MAY2008,29.91
14593,30JUN2008,31.208
14593,31JUL2008,30.32
14593,31AUG2008,30.121
14593,30SEP2008,26.671
14593,31OCT2008,19.994
14593,30NOV2008,19.863
14593,31DEC2008,18.545
14593,31JAN2009,17.508
14593,28FEB2009,16.911
14593,31MAR2009,17.107
14593,30APR2009,19.892
14593,31MAY2009,20.439
14593,30JUN2009,22.014
14593,31JUL2009,26.188
14593,31AUG2009,26.331
14593,30SEP2009,28.008
14593,31OCT2009,33.338
14593,30NOV2009,33.621
14593,31DEC2009,34
14593,31JAN2010,35.341
14593,28FEB2010,35.513
14593,31MAR2010,36.534
14593,30APR2010,43.571
14593,31MAY2010,43.639
14593,30JUN2010,45.169
14593,31JUL2010,47.675
14593,31AUG2010,48.624
14593,30SEP2010,49.199
14593,31OCT2010,53.488
14593,30NOV2010,53.788
14593,31DEC2010,54.535
14593,31JAN2011,60.182
14593,28FEB2011,60.661
14593,31MAR2011,61.34
14593,30APR2011,64.538
14593,31MAY2011,64.578
14593,30JUN2011,64.788
14593,31JUL2011,72.549
14593,31AUG2011,72.095
14593,30SEP2011,72.639
14593,31OCT2011,74.08
14593,30NOV2011,73.28
14593,31DEC2011,71.981
14593,31JAN2012,81.375
14593,29FEB2012,83.152
14593,31MAR2012,95.997
14593,30APR2012,104.1
14593,31MAY2012,104.476
14593,30JUN2012,105.817
14593,31JUL2012,104.398
14593,31AUG2012,106.143
14593,30SEP2012,112.929
14593,31OCT2012,111.15
14593,30NOV2012,109.795
14593,31DEC2012,104.926
14593,31JAN2013,88.84
14593,28FEB2013,88.711
14593,31MAR2013,86.266
14593,30APR2013,73.728
14593,31MAY2013,73.053
14593,30JUN2013,72.836
14593,31JUL2013,73.893
14593,31AUG2013,72.169
14593,30SEP2013,78.437
14593,31OCT2013,82.765
14593,30NOV2013,84.085
14593,31DEC2013,87.218
14593,31JAN2014,85.401
14593,28FEB2014,85.737
14593,31MAR2014,85.813
14593,30APR2014,90.129
14593,31MAY2014,93.939
14593,30JUN2014,95.568
14593,31JUL2014,104.199
14593,31AUG2014,104.39
14593,30SEP2014,112.111
14593,31OCT2014,116.667
14593,30NOV2014,119.951
14593,31DEC2014,125.686
14593,31MAR2015,154.25
14593,30APR2015,152.172
14593,31MAY2015,152.966
14593,30JUN2015,153.25
14593,31JUL2015,155.417
14593,31AUG2015,149.615
14593,30SEP2015,148.333
14593,31OCT2015,151.706
14593,30NOV2015,153.688
14593,31DEC2015,150.5
14593,31JAN2015,133.73
14593,28FEB2015,138.184
14593,31MAR2015,140.333
14593,30APR2015,143
14593,31MAY2015,141.538
14593,31JAN2016,131.939
14593,29FEB2016,130.943
14593,31MAR2016,129.471
14593,30APR2016,125.774
14593,31MAY2016,124.637
14593,30JUN2016,124.949
14593,31JUL2016,120.442
14593,31AUG2016,122
14593,30SEP2016,125.962
14593,31OCT2016,130.704
14593,30NOV2016,131.058
14593,31DEC2016,130.57
14593,31JAN2017,131
14593,28FEB2017,130.2
;;;;
data table2;
  infile datalines dsd truncover;
  input date_q:DATE9. id:32. value_q:F18.4;
  format date_q DATE9. value_q F18.4;
datalines4;
31MAR1998,14593,3963.0000
30JUN1998,14593,4041.0000
30SEP1998,14593,4289.0000
31DEC1998,14593,4592.0000
31MAR1999,14593,4935.0000
30JUN1999,14593,5019.0000
30SEP1999,14593,5161.0000
31DEC1999,14593,7586.0000
31MAR2000,14593,7007.0000
30JUN2000,14593,6932.0000
30SEP2000,14593,6803.0000
31DEC2000,14593,5986.0000
31MAR2001,14593,6130.0000
30JUN2001,14593,6071.0000
30SEP2001,14593,6021.0000
31DEC2001,14593,6122.0000
31MAR2002,14593,6264.0000
30JUN2002,14593,6293.0000
30SEP2002,14593,6298.0000
31DEC2002,14593,6269.0000
31MAR2003,14593,6361.0000
30JUN2003,14593,6441.0000
30SEP2003,14593,6815.0000
31DEC2003,14593,6971.0000
31MAR2004,14593,6735.0000
30JUN2004,14593,7223.0000
30SEP2004,14593,8050.0000
31DEC2004,14593,9362.0000
31MAR2005,14593,10111.0000
30JUN2005,14593,10488.0000
30SEP2005,14593,11551.0000
31DEC2005,14593,14181.0000
31MAR2006,14593,13911.0000
30JUN2006,14593,15114.0000
30SEP2006,14593,17205.0000
31DEC2006,14593,19461.0000
31MAR2007,14593,18711.0000
30JUN2007,14593,21647.0000
30SEP2007,14593,25347.0000
31DEC2007,14593,30039.0000
31MAR2008,14593,30471.0000
30JUN2008,14593,31709.0000
30SEP2008,14593,39572.0000
31DEC2008,14593,42787.0000
31MAR2009,14593,43237.0000
30JUN2009,14593,48140.0000
30SEP2009,14593,47501.0000
31DEC2009,14593,53926.0000
31MAR2010,14593,57057.0000
30JUN2010,14593,64725.0000
30SEP2010,14593,75183.0000
31DEC2010,14593,86742.0000
31MAR2011,14593,94904.0000
30JUN2011,14593,106758.0000
30SEP2011,14593,116371.0000
31DEC2011,14593,138681.0000
31MAR2012,14593,150934.0000
30JUN2012,14593,162896.0000
30SEP2012,14593,176064.0000
31DEC2012,14593,196088.0000
31MAR2013,14593,194743.0000
30JUN2013,14593,199856.0000
30SEP2013,14593,207000.0000
31DEC2013,14593,225184.0000
31MAR2014,14593,205989.0000
30JUN2014,14593,222520.0000
30SEP2014,14593,231839.0000
31DEC2014,14593,261894.0000
31MAR2015,14593,261194.0000
30JUN2015,14593,273151.0000
30SEP2015,14593,290479.0000
31DEC2015,14593,293284.0000
31MAR2016,14593,305277.0000
30JUN2016,14593,305602.0000
30SEP2016,14593,321686.0000
31DEC2016,14593,331141.0000
31MAR2017,14593,334532.0000
30JUN2017,14593,345173.0000
30SEP2017,14593,375319.0000
31DEC2017,14593,406794.0000
;;;;

Accepted Solutions
Solution
‎02-15-2018 02:07 PM
Frequent Contributor
Posts: 112

Re: Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

Posted in reply to Agent1592

This Works

 

proc sql;
create table table3 as
select t1.*, t2.value_q, t2.date_q
from table1 t1 left join table2 t2
on t1.id=t2.id
and (case when intnx('month',t1.mend,-3,'END') = intnx('qtr',intnx('month',t1.mend,-3,'END'),0,'END') then intnx('month',t1.mend,-3,'END') else intnx('qtr',intnx('month',t1.mend,-3,'END'),-1,'END') end)=t2.date_q;
quit;

Please let us know if it worked for you.

View solution in original post


All Replies
Super User
Posts: 6,935

Re: Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

Posted in reply to Agent1592

You'll need to explain a little more.

 

What does it mean for table1 to be a "leading" table?  It sounds like you want its dates to come AFTER the dates in table 2.

 

How do you match up dates that are equal?  It might help to give a small example (just 9 monthly observations) and show what the result should look like.

 

Presumably these should match on ID as well.  Yes?

 

A promising approach would be to add a variable to TABLE1, using DATE_Q to compute the proper MEND value to match.  For example:

 

mend = intnx('quarter', date_q, -1, 'end');

 

If this is the right formula, you could just sort and merge by ID MEND.  But it's really not clear whether -1 should be changed to +1, or 0, or something else.

Frequent Contributor
Posts: 76

Re: Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

[ Edited ]
Posted in reply to Astounding

Table1 is leading table meaning that I would like to keep all the rows in that table. I tried the code below but to no avail.

proc sql;
create table finalt as select a.*, b.
from table1 as a left join table2 as b on a.id=b.id and intnx('month',a.mend,-3)<=b.date_q<=intnx('month',a.mend,-5); quit;

The desired result should be something like this. I don't need the column day difference. This is just to give you an idea of the matching.

Table 1  Table 2   
idmendvalue_mdate_qidvalue_qDays difference
1459331-Mar-992.14331-Dec-9814593459290
1459330-Apr-991.78631-Dec-98145934592120
1459331-May-991.91131-Dec-98145934592151
1459330-Jun-992.04831-Mar-9914593493591
1459331-Jul-992.3931-Mar-99145934935122
1459331-Aug-992.56431-Mar-99145934935153
1459330-Sep-992.82730-Jun-9914593501992
Super User
Posts: 6,935

Re: Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

Posted in reply to Agent1592

OK, here's the formula I came up with:

 

data table1_revised;

set table1;

month_temp = intnx('month', mend, -1*mod(month(mend),3)) ;

date_q = intnx('quarter', month_temp, -1, 'end');

drop month_temp;

run;

 

That adds the matching end-of-quarter date to the TABLE1 data.  What's left is a simple sort-and-merge:

 

proc sort data=table1_revised;

by id date_q;

run;

proc sort data=table2;

by id date_q;

run;

data want;

merge table1_revised (in=keepme) table2;

by id date_q;

if keepme;

run;

Frequent Contributor
Posts: 76

Re: Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

Posted in reply to Astounding

Thank you!

Solution
‎02-15-2018 02:07 PM
Frequent Contributor
Posts: 112

Re: Merge two tables (first one on a monthly basis and second one on quarterly) proc SQL

Posted in reply to Agent1592

This Works

 

proc sql;
create table table3 as
select t1.*, t2.value_q, t2.date_q
from table1 t1 left join table2 t2
on t1.id=t2.id
and (case when intnx('month',t1.mend,-3,'END') = intnx('qtr',intnx('month',t1.mend,-3,'END'),0,'END') then intnx('month',t1.mend,-3,'END') else intnx('qtr',intnx('month',t1.mend,-3,'END'),-1,'END') end)=t2.date_q;
quit;

Please let us know if it worked for you.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 146 views
  • 2 likes
  • 3 in conversation