how to find closest match that is less than its date?

ZRick · Posted 01-25-2012 10:38 PM

I have:

prev_cpn_date

11/1/2011

12/15/2011

12/16/2011

1/1/2012

1/16/2012

10/20/2011

10/26/2011

11/21/2011

11/24/2011

11/25/2011

11/26/2011

11/28/2011

12/1/2011

and a quasi-lookup table

AS_OF_DATE	SERIES_VALUE
9/20/2011	0.355
9/21/2011	0.35556
9/22/2011	0.35806
9/23/2011	0.36022
9/26/2011	0.36278
9/27/2011	0.36522
9/28/2011	0.36856
9/29/2011	0.37211
9/30/2011	0.37433
10/3/2011	0.37761
10/4/2011	0.38094
10/5/2011	0.38361
10/6/2011	0.38778
10/7/2011	0.39111
10/10/2011	0.39417
10/11/2011	0.3975
10/12/2011	0.40083
10/13/2011	0.40306
10/14/2011	0.40472
10/17/2011	0.40583
10/18/2011	0.40917
10/19/2011	0.41167
10/20/2011	0.41556
10/21/2011	0.41833
10/24/2011	0.42028
10/25/2011	0.42222
10/26/2011	0.42472
10/27/2011	0.42806
10/28/2011	0.42944
10/31/2011	0.42944
11/1/2011	0.43167
11/2/2011	0.43306
11/3/2011	0.435
11/4/2011	0.4375
11/7/2011	0.44139
11/8/2011	0.44417
11/9/2011	0.44917
11/10/2011	0.45278
11/11/2011	0.45722
11/14/2011	0.46056
11/15/2011	0.46556
11/16/2011	0.47111
11/17/2011	0.47944
11/18/2011	0.48778
11/21/2011	0.495
11/22/2011	0.50028
11/23/2011	0.50611
11/24/2011	0.51167
11/25/2011	0.51806
11/28/2011	0.52306
11/29/2011	0.52694
11/30/2011	0.52889
12/1/2011	0.52722
12/2/2011	0.52833
12/5/2011	0.5339
12/6/2011	0.53775
12/7/2011	0.54
12/8/2011	0.54

I want to use pre_cpn_date to match as_of_date and return the series_value, the kicker is:

reference table doesn't have all the dates in pre_cpn_date, so in this case find the closest matched as_of_date and it must be less than pre_cpn_date

how do I do that?

Linlin · Posted 01-25-2012 11:53 PM

Hi Rick,

Here is a better solution. Good luck!

data pre;

informat prev_cpn_date mmddyy10.;

format prev_cpn_date mmddyy10.;

input prev_cpn_date;

cards;

11/1/2011

12/15/2011

12/16/2011

1/1/2012

1/16/2012

10/20/2011

10/26/2011

11/21/2011

11/24/2011

11/25/2011

11/26/2011

11/28/2011

12/1/2011

;

data have;

informat as_of_date mmddyy10.;

format as_of_date mmddyy10.;

input AS_OF_DATE : SERIES_VALUE;

cards;

9/20/2011 0.355

9/21/2011 0.35556

9/22/2011 0.35806

9/23/2011 0.36022

9/26/2011 0.36278

9/27/2011 0.36522

9/28/2011 0.36856

9/29/2011 0.37211

9/30/2011 0.37433

10/3/2011 0.37761

10/4/2011 0.38094

10/5/2011 0.38361

10/6/2011 0.38778

10/7/2011 0.39111

10/10/2011 0.39417

10/11/2011 0.3975

10/12/2011 0.40083

10/13/2011 0.40306

10/14/2011 0.40472

10/17/2011 0.40583

10/18/2011 0.40917

10/19/2011 0.41167

10/20/2011 0.41556

10/21/2011 0.41833

10/24/2011 0.42028

10/25/2011 0.42222

10/26/2011 0.42472

10/27/2011 0.42806

10/28/2011 0.42944

10/31/2011 0.42944

11/1/2011 0.43167

11/2/2011 0.43306

11/3/2011 0.435

11/4/2011 0.4375

11/7/2011 0.44139

11/8/2011 0.44417

11/9/2011 0.44917

11/10/2011 0.45278

11/11/2011 0.45722

11/14/2011 0.46056

11/15/2011 0.46556

11/16/2011 0.47111

11/17/2011 0.47944

11/18/2011 0.48778

11/21/2011 0.495

11/22/2011 0.50028

11/23/2011 0.50611

11/24/2011 0.51167

11/25/2011 0.51806

11/28/2011 0.52306

11/29/2011 0.52694

11/30/2011 0.52889

12/1/2011 0.52722

12/2/2011 0.52833

12/5/2011 0.5339

12/6/2011 0.53775

12/7/2011 0.54

12/8/2011 0.54

;

proc sql;

create table temp as

select prev_cpn_date ,as_of_date,SERIES_VALUE,(prev_cpn_date-as_of_date) as diff

from pre,have

where prev_cpn_date GE as_of_date

order by prev_cpn_date, diff;

quit;

data want(drop=diff);

set temp;

by prev_cpn_date;

if first.prev_cpn_date;

run;

proc print;run;

prev_cpn_ SERIES_

Obs date as_of_date VALUE

1 10/20/2011 10/20/2011 0.41556

2 10/26/2011 10/26/2011 0.42472

3 11/01/2011 11/01/2011 0.43167

4 11/21/2011 11/21/2011 0.49500

5 11/24/2011 11/24/2011 0.51167

6 11/25/2011 11/25/2011 0.51806

7 11/26/2011 11/25/2011 0.51806

8 11/28/2011 11/28/2011 0.52306

9 12/01/2011 12/01/2011 0.52722

10 12/15/2011 12/08/2011 0.54000

11 12/16/2011 12/08/2011 0.54000

12 01/01/2012 12/08/2011 0.54000

13 01/16/2012 12/08/2011 0.54000

ZRick · Posted 01-26-2012 12:32 AM

Hello Linlin,

Thank you so much for the quick response, I do have couple of questions,

1.can you tell me the logic for your data want, I know you are using array, but how it gets realized?

2. in the pre_cpn_date dataset, I actually have 337 obs, how do I alter your code to adjust it?

really appreciate your help.

Peter_C · Posted 01-26-2012 07:46 AM

zRick

here is an approach that depends on ordered data

data want ;

set have (in= have rename= prev_cpn_date=date )

quasi(in= lu rename= as_of_date=date ) ;

by date ;

if lu then do ; * to keep the prevailing values ;

latest_rate = SERIES_VALUE ;

lu_date = date ;

end ;

if have ; * just cpn_date data rows are output ;

retain lu_date latest_rate ;

format lu_date date11. latest_rate percent10.4 ;

drop SERIES_VALUE ;

run ;

and here is a display of WORK.WANT

+FSVIEW: WORK.WANT (B)-------------------------------+

| Obs date latest_rate lu_date |

| |

| 1 20-OCT-2011 41.1670% 19-OCT-2011 |

| 2 26-OCT-2011 42.2220% 25-OCT-2011 |

| 3 01-NOV-2011 42.9440% 31-OCT-2011 |

| 4 21-NOV-2011 48.7780% 18-NOV-2011 |

| 5 24-NOV-2011 50.6110% 23-NOV-2011 |

| 6 25-NOV-2011 51.1670% 24-NOV-2011 |

| 7 26-NOV-2011 51.8060% 25-NOV-2011 |

| 8 28-NOV-2011 51.8060% 25-NOV-2011 |

| 9 01-DEC-2011 52.8890% 30-NOV-2011 |

| 10 15-DEC-2011 54.0000% 08-DEC-2011 |

| 11 16-DEC-2011 54.0000% 08-DEC-2011 |

| 12 01-JAN-2012 54.0000% 08-DEC-2011 |

| 13 16-JAN-2012 54.0000% 08-DEC-2011 |

+-----------------------------------------------------+

not only is SERIES_VALUE present (named latest_rate and formatted as a percent), but the date of that series value from the quasi lookup is present too as lu_date.

The datastep depends on having both tables in date order. BY group processing interleaves rows from each table on the SET statement

Placing QUASI second on the SET statement ensures that when date is the same on both files, the rate chosen is for the previous day (see 24 and 25th November)

Linlin · Posted 01-26-2012 08:18 AM

Hi Rick,

I have updated my code. Please ignore my previous post.

Rick, when there are match dates, do you want the same date or previous date? I selected the same date and Peter selected the previous date.

Thanks,

Linlin

ZRick · Posted 01-26-2012 04:18 PM

I use previous available date.

Linlin · Posted 01-26-2012 04:27 PM

The code below will get the same dates as Peter's code:

proc sql;

create table temp as

select prev_cpn_date ,as_of_date,SERIES_VALUE,(prev_cpn_date-as_of_date) as diff

from pre,have

where prev_cpn_date GT as_of_date

order by prev_cpn_date, diff;

quit;

data want(drop=diff);

set temp;

by prev_cpn_date;

if first.prev_cpn_date;

run;

proc print;run;

Linlin

Haikuo · Posted 01-26-2012 10:46 AM

I still think SQL have the native edge to single-handed deal with this kind of problem, it may not as efficient, but it sure does its job. In the following code, I have borrowed tables setup by Linlin.

proc sql;

create table want as

select * from pre a

left join have b

on 1

group by a.prev_cpn_date

having abs(a.prev_cpn_date-b.AS_OF_DATE)<= min(abs(a.prev_cpn_date-b.AS_OF_DATE) )

;

quit;

the abs() and minus action could be replaced by datdif().

Kindly Regards,

Haikuo

ZRick · Posted 01-26-2012 06:25 PM

I copied the Peter's Linlin's and HaiKuo's codes and tested it.

Peter's comes back with exactly the same 339 entries, while Linlins return way more records, and haikuo's returned a cartesian warning.

%let analysis_date = "19dec2011"d;

data FLoat_Leg_fr_GDR;
input prev_cpn_date:mmddyy10. PRI_ID $;
cards;
11/1/2011 0WR158494
12/15/2011 0WR161175
12/16/2011 0WR161290
1/1/2012 0WR036278
1/16/2012 0WR162975
10/20/2011 0WR163296
10/26/2011 0WR163874
11/21/2011 0WR165713
11/24/2011 0WR165853
11/25/2011 0WR166133
11/26/2011 0WR166315
11/28/2011 0WR166513
12/1/2011 0WR166737
12/8/2011 0WR167396
12/25/2011 0WR036013
11/16/2011 0WR172578
11/19/2011 0WR173071
11/25/2011 0WR174350
11/26/2011 0WR174475
12/1/2011 0WR174996
12/11/2011 0WR151812
12/14/2011 0WR151838
12/15/2011 0WR038993
12/15/2011 0WR171950
12/15/2011 0WR172172
12/28/2011 0WR177379
12/28/2011 0WR177213
12/28/2011 0WR177478
12/28/2011 0WR177239
1/1/2012 0WR177973
1/4/2012 0WR178351
1/5/2012 0WR178559
1/19/2012 0WR179797
11/16/2011 0WR182536
11/17/2011 0WR182718
11/22/2011 0WR183492
11/22/2011 0WR183450
12/13/2011 0WR185315
12/16/2011 0WR156753
12/17/2011 0WR156878
12/21/2011 0WR156894
12/1/2011 0WR035551
12/14/2011 0WR194879
12/18/2011 0WR161530
1/16/2012 0WR163031
1/19/2012 0WR163056
12/14/2011 0WR168014
11/15/2011 0WR036351
12/2/2011 0WR219114
1/5/2012 0WR224452
1/1/2012 0WR178013
1/5/2012 0WR178617
1/18/2012 0WR222373
12/7/2011 0WR184896
12/16/2011 0WR063090
1/6/2012 0WP227895
1/9/2012 0WP228059
11/3/2011 0WP212517
12/4/2011 0WR139031
12/5/2011 0WR139254
12/5/2011 0WR139239
12/6/2011 0WR139452
12/9/2011 0WR139536
12/10/2011 0WR139593
12/11/2011 0WR139734
12/12/2011 0WR139817
12/13/2011 0WR140039
12/15/2011 0WR064197
12/16/2011 0WR140195
12/17/2011 0WR140419
12/18/2011 0WR140591
12/19/2011 0WR140831
12/30/2011 0WR141334
1/16/2012 0WR142456
11/28/2011 0WR144890
11/28/2011 0WR144916
11/29/2011 0WR145038
11/29/2011 0WR145095
11/28/2011 0WR157116
12/2/2011 0WR145194
12/3/2011 0WR145459
12/4/2011 0WR145590
12/5/2011 0WR145855
12/5/2011 0WR145814
12/9/2011 0WR146176
12/11/2011 0WR146374
12/12/2011 0WR146473
12/24/2011 0WR146994
12/26/2011 0WR147034
1/2/2012 0WR147356
10/27/2011 0WR148412
11/12/2011 0WR149550
11/19/2011 0WR173097
11/19/2011 0WR173196
11/19/2011 0WR149956
11/20/2011 0WR150095
11/20/2011 0WR173394
11/25/2011 0WR150277
11/26/2011 0WR150616
11/26/2011 0WR150590
11/27/2011 0WR150657
11/28/2011 0WR150772
12/1/2011 0WR150996
12/3/2011 0WR151317
12/3/2011 0WR151374
12/6/2011 0WR219551
12/10/2011 0WR151739
12/15/2011 0WR049073
12/16/2011 0WR151978
12/17/2011 0WR152075
12/17/2011 0WR152091
12/18/2011 0WR152174
12/18/2011 0WR152299
12/21/2011 0WR152372
12/22/2011 0WR152455
12/24/2011 0WR152851
12/25/2011 0WR152976
12/28/2011 0WR153230
1/7/2012 0WR153453
1/13/2012 0WR153511
10/22/2011 0WR153834
11/9/2011 0WR154691
11/19/2011 0WR155318
11/20/2011 0WR155417
11/23/2011 0WR155599
11/24/2011 0WR155672
11/27/2011 0WR155854
12/9/2011 0WR156431
12/10/2011 0WR156530
12/14/2011 0WR156639
12/15/2011 0WR156712
1/19/2012 0WR157694
10/28/2011 0WR158338
11/5/2011 0WR159393
11/18/2011 0WR160235
11/28/2011 0WR176314
12/5/2011 0WR160755
12/8/2011 0WR160870
12/10/2011 0WR160995
12/10/2011 0WR160953
12/11/2011 0WR161019
12/11/2011 0WR161035
12/12/2011 0WR161092
12/15/2011 0WR161191
12/16/2011 0WR161431
12/18/2011 0WR161555
12/18/2011 0WR161597
12/22/2011 0WR161993
12/23/2011 0WR162074
12/26/2011 0WR162314
1/9/2012 0WR162652
1/14/2012 0WR162892
1/15/2012 0WR162959
1/16/2012 0WR162991
1/19/2012 0WR163072
1/19/2012 0WR163197
10/20/2011 0WR163312
10/20/2011 0WR163254
10/22/2011 0WR163510
10/22/2011 0WR163551
10/23/2011 0WR163650
10/23/2011 0WR163759
10/26/2011 0WR163890
10/26/2011 0WR163916
11/17/2011 0WR165333
11/19/2011 0WR165614
11/21/2011 0WR165739
11/24/2011 0WR165879
11/25/2011 0WR166158
11/26/2011 0WR166331
11/26/2011 0WR166299
11/28/2011 0WR166539
12/1/2011 0WR166752
12/8/2011 0WR167412
12/21/2011 0WR168998
12/21/2011 0WR169038
1/15/2012 0WR142514
10/30/2011 0WR171455
10/30/2011 0WR171539
11/5/2011 0WR171919
11/12/2011 0WR172271
11/13/2011 0WR172339
11/26/2011 0WR174491
12/1/2011 0WR201393
12/15/2011 0WR035452
12/15/2011 0WR171976
12/15/2011 0WR172156
12/15/2011 0WR176496
12/17/2011 0WR176611
12/22/2011 0WR176892
12/28/2011 0WR177270
12/28/2011 0WR177437
12/28/2011 0WR177494
12/28/2011 0WR177254
12/28/2011 0WR177395
12/29/2011 0WR177593
1/4/2012 0WR178377
1/6/2012 0WR178799
1/7/2012 0WR178831
1/14/2012 0WR179318
1/19/2012 0WR179813
10/21/2011 0WR180118
11/1/2011 0WR180936
11/3/2011 0WR181397
11/10/2011 0WR040239
12/7/2011 0WR184912
12/9/2011 0WR185117
. 0WR227935
1/15/2012 0WR062555
10/25/2011 0WR192519
11/8/2011 0WR159492
11/25/2011 0WR193111
12/1/2011 0WR050451
12/23/2011 0WR196452
12/14/2011 0WR207051
12/15/2011 0WR053315
12/20/2011 0WR207838
12/21/2011 0WR208216
12/24/2011 0WR208836
1/3/2012 0WR063355
12/1/2011 0WR065913
1/1/2012 0WR063454
12/15/2011 0WR176454
12/16/2011 0WR176553
12/12/2011 0WR139858
12/13/2011 0WR140054
12/16/2011 0WR140211
12/17/2011 0WR140435
12/18/2011 0WR128133
12/18/2011 0WR140617
12/19/2011 0WR140856
11/28/2011 0WR144932
11/29/2011 0WR145053
11/29/2011 0WR145079
12/2/2011 0WR145210
12/3/2011 0WR145475
12/4/2011 0WR145616
12/5/2011 0WR145830
12/11/2011 0WR146390
12/24/2011 0WR147018
12/26/2011 0WR147059
1/2/2012 0WR147372
10/27/2011 0WR148438
11/12/2011 0WR149535
11/19/2011 0WR149972
11/26/2011 0WR150632
11/27/2011 0WR150673
11/28/2011 0WR150798
12/3/2011 0WR151333
12/17/2011 0WR152133
12/18/2011 0WR152190
12/22/2011 0WR152471
12/24/2011 0WR152877
12/28/2011 0WR153198
1/16/2012 0WR153578
10/26/2011 0WR154030
10/30/2011 0WR154238
11/12/2011 0WR154857
11/16/2011 0WR154915
11/18/2011 0WR155219
11/18/2011 0WR155276
11/20/2011 0WR155433
12/14/2011 0WR156654
11/1/2011 0WR158510
1/16/2012 0WR163015
1/19/2012 0WR163171
10/22/2011 0WR163577
10/23/2011 0WR163775
11/24/2011 0WR165895
11/25/2011 0WR166174
11/28/2011 0WR166497
11/28/2011 0WR166554
12/21/2011 0WR169012
1/16/2012 0WR170234
1/16/2012 0WR170259
10/30/2011 0WR171554
10/30/2011 0WR171471
11/5/2011 0WR171935
11/5/2011 0WR171893
11/11/2011 0WR172255
11/13/2011 0WR172313
11/16/2011 0WR172693
11/19/2011 0WR173055
11/19/2011 0WR173139
11/25/2011 0WR174392
11/26/2011 0WR174517
12/9/2011 0WR175837
12/9/2011 0WR175795
12/10/2011 0WR175977
12/15/2011 0WR176470
12/15/2011 0WR176512
12/16/2011 0WR176538
12/17/2011 0WR176637
12/27/2011 0WR177130
12/28/2011 0WR177353
12/28/2011 0WR177296
12/28/2011 0WR177452
12/28/2011 0WR177411
12/28/2011 0WR177536
1/4/2012 0WR178336
1/4/2012 0WR178393
1/5/2012 0WR178591
1/6/2012 0WR178815
1/15/2012 0WR179599
10/29/2011 0WR180878
11/10/2011 0WR182114
11/17/2011 0WR182759
12/2/2011 0WR184177
12/10/2011 0WR185257
12/10/2011 0WR185190
12/13/2011 0WR185414
12/17/2011 0WR185950
12/20/2011 0WR186131
12/23/2011 0WR186313
1/6/2012 0WR186974
10/20/2011 0WR189077
12/4/2011 0WR194416
10/20/2011 0WR200213
11/5/2011 0WR201971
11/11/2011 0WR202334
12/20/2011 0WR207911
12/1/2011 0WR210030
12/28/2011 0WR213851
1/6/2012 0WR227919
1/9/2012 0WR228016
1/17/2012 0WR228339
1/17/2012 0WR228438
1/17/2012 0WP228356
1/17/2012 0WR228313
. 0WP227952
12/30/2011 0WP223431
11/15/2011 0WR217456
11/19/2011 0WR218074
12/20/2011 0WR220237
12/21/2011 0WR203316
11/9/2011 0WR224817
11/22/2011 0WR225392
1/9/2012 0WP228034
1/17/2012 0WP228414

;

proc sort data = FLoat_Leg_fr_GDR out=flt_srt;
by prev_cpn_date;
run;

data lib3rate;
input AS_OF_DATE: mmddyy10. SERIES_VALUE;
cards;
9/20/2011 0.355
9/21/2011 0.35556
9/22/2011 0.35806
9/23/2011 0.36022
9/26/2011 0.36278
9/27/2011 0.36522
9/28/2011 0.36856
9/29/2011 0.37211
9/30/2011 0.37433
10/3/2011 0.37761
10/4/2011 0.38094
10/5/2011 0.38361
10/6/2011 0.38778
10/7/2011 0.39111
10/10/2011 0.39417
10/11/2011 0.3975
10/12/2011 0.40083
10/13/2011 0.40306
10/14/2011 0.40472
10/17/2011 0.40583
10/18/2011 0.40917
10/19/2011 0.41167
10/20/2011 0.41556
10/21/2011 0.41833
10/24/2011 0.42028
10/25/2011 0.42222
10/26/2011 0.42472
10/27/2011 0.42806
10/28/2011 0.42944
10/31/2011 0.42944
11/1/2011 0.43167
11/2/2011 0.43306
11/3/2011 0.435
11/4/2011 0.4375
11/7/2011 0.44139
11/8/2011 0.44417
11/9/2011 0.44917
11/10/2011 0.45278
11/11/2011 0.45722
11/14/2011 0.46056
11/15/2011 0.46556
11/16/2011 0.47111
11/17/2011 0.47944
11/18/2011 0.48778
11/21/2011 0.495
11/22/2011 0.50028
11/23/2011 0.50611
11/24/2011 0.51167
11/25/2011 0.51806
11/28/2011 0.52306
11/29/2011 0.52694
11/30/2011 0.52889
12/1/2011 0.52722
12/2/2011 0.52833
12/5/2011 0.5339
12/6/2011 0.53775
12/7/2011 0.54
12/8/2011 0.54
12/9/2011 0.54175
12/12/2011 0.5435
12/13/2011 0.54625
12/14/2011 0.55505
12/15/2011 0.55915
12/16/2011 0.56315
12/19/2011 0.56695
12/20/2011 0.56975
12/21/2011 0.57125
12/22/2011 0.57375
12/23/2011 0.57575
12/28/2011 0.57925
12/29/2011 0.581
12/30/2011 0.581
1/3/2012 0.5825
1/4/2012 0.5825
1/5/2012 0.5825
1/6/2012 0.5815
1/9/2012 0.5805
1/10/2012 0.5795
1/11/2012 0.5765
1/12/2012 0.5715
1/13/2012 0.567
1/16/2012 0.5649
1/17/2012 0.5623
1/18/2012 0.5612
1/19/2012 0.5612
1/20/2012 0.5611
;

/*************from peter*************/
data final ;
   set flt_srt   (in= have rename= prev_cpn_date=date )
     lib3rate (in= lu   rename=as_of_date=date ) ;
   by date ;
   if lu then do ; * to keep the prevailing values ;
      latest_rate = SERIES_VALUE ;
      lu_date     = date ;
   end ;
   if have ;        * just cpn_date data rows are output ;
   retain lu_date         latest_rate ;
   format lu_date date9.;
   drop   SERIES_VALUE ;
run ;

/***********************from Lin****************/
proc sql;
create table temp_lin as
   select prev_cpn_date ,as_of_date,SERIES_VALUE,(prev_cpn_date-as_of_date) as diff
   from FLoat_Leg_fr_GDR,lib3rate
     where prev_cpn_date GT as_of_date
        order by prev_cpn_date, diff;
quit;

data final_lin(drop=diff);
   set temp_lin;
   by prev_cpn_date;
   if first.prev_cpn_date;
run;

/***************from Hai.Kuo***************/

proc sql;
create table final3 as
select * from FLoat_Leg_fr_GDR a
left join lib3rate b
on 1
group by a.prev_cpn_date
having abs(a.prev_cpn_date-b.AS_OF_DATE)<= min(abs(a.prev_cpn_date-b.AS_OF_DATE) )
;
quit;

art297 · Posted 01-26-2012 06:30 PM

Which one, if any, returned the result you wanted? And, if the answer is none of the above, what do you want your resulting file to look like?

ZRick · Posted 01-26-2012 09:27 PM

Peter's is the right one.

Linlin · Posted 01-26-2012 08:01 PM

Hi Rick,

The number of observations generated by my code are 79. this is caused by the duplicate and missing value of variable "prev_cpn_date" in your dataset.

Thare are no duplicate and missing prev_cpn_date in the sample data you provided.

Linlin

Linlin · Posted 01-26-2012 09:41 PM

The code below generates 337 observations. the two observations with missing prev_cpn_date were exluded.

proc sql;

create table temp_lin as

select prev_cpn_date format=mmddyy10.,pri_id,as_of_date format=mmddyy10.,SERIES_VALUE,(prev_cpn_date-as_of_date) as diff

from FLoat_Leg_fr_GDR,lib3rate

where prev_cpn_date GT as_of_date

order by prev_cpn_date, diff,pri_id ;

create table look as select prev_cpn_date as dd format=mmddyy10.,min(diff) as

mdiff from temp_lin

group by prev_cpn_date;

create table final_lin (drop=diff dd mdiff) as

select * from temp_lin as a,look

where a.prev_cpn_date=look.dd

and a.diff=look.mdiff;

quit;

proc print;run;

Haikuo · Posted 01-27-2012 07:21 AM

My previous code did not count for missing values, it produced table with 513 obs, however, under my settings (SAS 9.2, winxp sp3), it did not alarm any cartisian warnings. So are you using a learning version of SAS?

After a minor tweak to acount for the missing values, it outcomes a table with 341 obs.

proc sql;

create table final3 as

select distinct * from FLoat_Leg_fr_GDR (where=(not missing (prev_cpn_date))) a

left join lib3rate b

on 1

group by a.prev_cpn_date

having abs(a.prev_cpn_date-b.AS_OF_DATE)<= min(abs(a.prev_cpn_date-b.AS_OF_DATE) )

;

quit;

The reason it has 341 obs instead of 337 is that there are some records have the same date difference between pre and post obs, so they BOTH get to be kept. I wonder if SQL can actuall chose pre or post obs when there is a tie. The benefit of SQL is that data does not need to be pre-sorted, and SQL in this case, will deliver most accurate results just in term of minimum differences.

Regards,

Haikuo