mardi 12 juin 2018

SAS merge on a lot of by variables

I am working in SAS and I'm trying to append some new interest rates onto an observation based on the following criteria:

Segment ID, Fixed or Floating, Performing Status, Type , Country, Currency

The problem is, I always have a mismatch in the merge and it selects the variable below : for example

data wantmerge;
input Segment_ID, Fixed_or_floating, Performing_status $, Type $, Country $, Currency $ impliedRate;
datalines;

1 FIX P "New Issuance" GB GBP 0.1
1 FIX NP "New Issuance" GB GBP 0.2
1 FLT P "Existing" GB GBP 0.3

data have;
input Segment_ID, Fixed_or_floating, Performing_status $, Type $, Country $, Currency $;
datalines;

1 FIX P "New Issuance" GB GBP
1 FIX P "New Issuance" GB GBP
1 FIX P "New Issuance" GB GBP
;
run;

the code is use is

   proc sort data = have;
    by segment_ID fixed_or_floating performing_status type country currency;
    run;

    proc sort data = wantmerge;
    by segment_ID fixed_or_floating performing_status type country currency;
    run;

    data want;
    merge have (in = a)
            wantmerge (in = b);
    by segment_id fixed_or_floating performing_status type country currency;
    if a;
    run;

for an individual line this works well but for big datasets it will keep merging the impliedRate from the below entry e.g. data have will take the impliedEIR from wantmerge row 2.

Is there a way around this?

Aucun commentaire:

Enregistrer un commentaire