Issue with merging data

[ealderm2]

I'm trying to merge 2 data sets. One has many rows with a date and the other has 3 rows and a data range with the aplicable value that i need to be picked up.

I need to show the correct value for each row in a by picking up the correct value within the date range specified.

I have tried the following -

proc sql;

create table work.EDW_ALL as
select *
from EDW_PLUS_CPC a
left join DCA_PERCENT b
ON a.DEALER_CODE=b.dealer_code
and A.CPC=B.CPC
AND  A.DLR_IP=B.DLR_IP
and PURCHASE_DATE >= CPA_START_DT
AND PURCHASE_DATE <= CPA_EXP_DT
;
QUIT;

This gives me all the 225 rows I need but only gives me the merged value when the date falls into the first range on the b dataset,

and

data work.EDW_ALL;

merge      EDW_PLUS_CPC (in=a)

  DCA_PERCENT (in=b);

by DEALER_CODE CPC DLR_IP;

if (PURCHASE_DATE >= CPA_START_DT

AND PURCHASE_DATE <= CPA_EXP_DT);

if a then

  output work.EDW_ALL;

run

This gives me just 10 rows where the date falls into the first range on the b dataset.

It would appear that the first row is being picked up rather than picking up the correct value for each date/row.

Any thoughts would be gratefully appreciated.

Thanks

[Steelers_In_DC]

Do you get the desired results using a where clause in the second example?  Depending on where the data comes from you can do this:

dca_percent(in=b where=(PURCHASE_DATE >= CPA_START_DT AND PURCHASE_DATE <= CPA_EXP_DT));

[JayCorbett]

Could you post some sample data? Is the "Applicable Value" variable the same or different in the two data sets?