topic Re: Match and merge dates within the range in SAS Programming

Match and merge dates within the range

Ruslan — Sun, 09 Aug 2015 12:32:05 GMT

Dear All,

I have the following data (two datasets):

Company Date Company Date Value

1 199601 1 199606 10

1 199602 1 199706 11

1 199603 1 199806 12

1 199604

1 199605

1 199606

1 199607

1 199608

1 199609

1 199610

1 199611

1 199612

1 199701

1 199702

1 199703

1 199704

1 199705

1 199706

1 199707

1 199708

1 199709

1 199710

1 199711

1 199712

1 199801

1 199802

1 199803

1 199804

1 199805

1 199806

1 199807

1 199808

1 199809

1 199810

1 199811

1 199812

I need to obtain the following dataset:

Company Date Value

1 199601 10

1 199602 10

1 199603 10

1 199604 10

1 199605 10

1 199606 11

1 199607 11

1 199608 11

1 199609 11

1 199610 11

1 199611 11

1 199612 11

1 199701 11

1 199702 11

1 199703 11

1 199704 11

1 199705 11

1 199706 12

1 199707 12

1 199708 12

1 199709 12

1 199710 12

1 199711 12

1 199712 12

1 199801 12

1 199802 12

1 199803 12

1 199804 12

1 199805 12

1 199806 .

1 199807 .

1 199808 .

1 199809 .

1 199810 .

1 199811 .

1 199812 .

So, for each company I need to add "value" column from the second to the first dataset. The criteria how to add is the following. Add 10 if first date in the second dataset (199606) is greater (but not equal!!) than the dates in the first dataset. Then, add 11 if the second date in the second dataset (199706) is greater than the dates in the first dataset, but less or equal than 199606. Finally, add 12 if the third date in the second dataset (199806) is greater than the dates in the first dataset, but less or equal than 199706. Please note that I should have "value" column missing if date is greater or equal than 199806.

I look forward to getting your reply.

Kind regards,

Ruslan

Re: Match and merge dates within the range

Ksharp — Sun, 09 Aug 2015 12:59:37 GMT

Code: Program

data a;
input Company   Date  ;
cards;   
1 199601
1 199602
1 199603
1 199604
1 199605
1 199606
1 199607
1 199608
1 199609
1 199610
1 199611
1 199612
1 199701
1 199702
1 199703
1 199704
1 199705
1 199706
1 199707
1 199708
1 199709
1 199710
1 199711
1 199712
1 199801
1 199802
1 199803
1 199804
1 199805
1 199806
1 199807
1 199808
1 199809
1 199810
1 199811
1 199812
;
run;
data b;
input Company   Date   Value;
cards;
  1 199606 10
  1 199706 11
  1 199806 12
;
run;
data temp;
 set  b(in=inb) a;
 by Company   Date;
 in=inb;
run;
data want;
 do until(last.Company or not missing(Value));
  set temp;
  by Company;
 end;
 v=Value;
 do until(last.Company or not missing(Value));
  set temp;
  by Company;
  if not in then output;
 end;
drop Value in;
run;

Re: Match and merge dates within the range

Jagadishkatam — Sun, 09 Aug 2015 13:54:26 GMT

Please try the proc sql approach

proc sql;

create table test as select a.* , b.value from a left join b on

a.company=b.company and a.date<b.date order by a.date,b.value;

quit;

data test2;

set test;

by company date value;

if first.date;

run;

Thanks,

Jag

Re: Match and merge dates within the range

Ruslan — Mon, 10 Aug 2015 04:00:16 GMT

Thank you very much for your help. Your code works perfectly.

However, I also need to get table like this with minor correction. Could you please let me know what I need to change in your code? I am a bit struggled.

I need to get value 10 for 199601, value 11 for 199701 and so on. I will hugely appreciate your help.

Company Date Value

1 199601 10

1 199602 10

1 199603 10

1 199604 10

1 199605 10

1 199606 10

1 199607 11

1 199608 11

1 199609 11

1 199610 11

1 199611 11

1 199612 11

1 199701 11

1 199702 11

1 199703 11

1 199704 11

1 199705 11

1 199706 11

1 199707 12

1 199708 12

1 199709 12

1 199710 12

1 199711 12

1 199712 12

1 199801 12

1 199802 12

1 199803 12

1 199804 12

1 199805 12

1 199806 12

1 199807 .

1 199808 .

1 199809 .

1 199810 .

1 199811 .

1 199812 .

Kind regards,

Ruslan

Re: Match and merge dates within the range

Jagadishkatam — Mon, 10 Aug 2015 05:43:47 GMT

Hi Ruslan, Just curios to know if the code i provided was helpful. Please let me know if it need ant changes.

Re: Match and merge dates within the range

Ruslan — Mon, 10 Aug 2015 05:55:54 GMT

Hi Jagadishkatam,

I have tried your code, but it did not work since it gave me for each "value" from dataset 2 the same values of date from dataset 1. Could you please change Xia Keshan code to account for minor corrections mentioned above?

Thanks.

Kind regards,

Ruslan

Re: Match and merge dates within the range

Jagadishkatam — Mon, 10 Aug 2015 08:25:19 GMT

Yes the code is creating a Cartesian product and we need to consider the first record for which i wrote the below code. Could you please try.

data test2;

set test;

by company date value;

if first.date;

run;

i did not try Xia Keshan code. i will check this code as well and try to help you

Re: Match and merge dates within the range

Ksharp — Mon, 10 Aug 2015 12:25:03 GMT

Change the position of two table A and B in the first data step.

Code: Program

data a;
input Company   Date  ;
cards;   
1 199601
1 199602
1 199603
1 199604
1 199605
1 199606
1 199607
1 199608
1 199609
1 199610
1 199611
1 199612
1 199701
1 199702
1 199703
1 199704
1 199705
1 199706
1 199707
1 199708
1 199709
1 199710
1 199711
1 199712
1 199801
1 199802
1 199803
1 199804
1 199805
1 199806
1 199807
1 199808
1 199809
1 199810
1 199811
1 199812
;
run;
data b;
input Company   Date   Value;
cards;
  1 199606 10
  1 199706 11
  1 199806 12
;
run;
data temp;
 set a b(in=inb);
 by Company   Date;
 in=inb;
run;
data want;
 do until(last.Company or not missing(Value));
  set temp;
  by Company;
 end;
 v=Value;
 do until(last.Company or not missing(Value));
  set temp;
  by Company;
  if not in then output;
 end;
drop Value in;
run;

Re: Match and merge dates within the range

Ruslan — Mon, 10 Aug 2015 17:11:23 GMT

Perfect!

Thanks a lot, Xia!