Desktop productivity for business analysts and programmers

Issue with merging data

Reply
N/A
Posts: 1

Issue with merging data

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


Valued Guide
Posts: 854

Re: Issue with merging data

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

Occasional Contributor
Posts: 6

Re: Issue with merging data

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

Ask a Question
Discussion stats
  • 2 replies
  • 247 views
  • 0 likes
  • 3 in conversation