Issue with merging data

ealderm2 — Tue, 31 Mar 2015 14:47:37 GMT

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

Re: Issue with merging data

Steelers_In_DC — Tue, 31 Mar 2015 16:51:16 GMT

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));

Re: Issue with merging data

JayCorbett — Tue, 31 Mar 2015 17:03:14 GMT

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

topic Re: Issue with merging data in SAS Enterprise Guide

Issue with merging data

Re: Issue with merging data

Re: Issue with merging data