Combining two data sets in a specific way

[EinarRoed]

I need to combine two data sets in a specific way, and could use some advice.

Input #1:

POLICY_NR	INVOICE_NR	AMOUNT_TYPE	STATUS
8521	12345	ECO	O
8521	12345	FDM	O
8521	4567	ECO	C
8521	4567	FDM	C

Input #2:

POLICY_NR	AMOUNT_TYPE	AMOUNT
8521	D_COST	100

Desired output:

POLICY_NR	INVOICE_NR	AMOUNT_TYPE	AMOUNT	STATUS
8521	12345	ECO		O
8521	12345	FDM		O
8521	12345	D_COST	100	O
8521	4567	ECO		C
8521	4567	FDM		C
8521	4567	D_COST	100	C

Everything has the same POLICY_NR. Input #2 needs to be appended into Input #1 two times: once for each INVOICE_NR. When it's appended, the respective INVOICE_NR and STATUS need to be filled out for each of the two rows with AMOUNT_TYPE=D_COST.

Thanks for any suggestions you might give me in regards to this.

[Loko]

Hello,

One solution:

data master;
input POLICY_NR INVOICE_NR AMOUNT_TYPE $ STATUS $;
datalines;
8521 12345 ECO O
8521 12345 FDM O
8521 4567 ECO C
8521 4567 FDM C
8522 4567 FDM C
8523 4567 FDM C
;

data updatee;
input POLICY_NR AMOUNT_TYPE $ AMOUNT ;
datalines;
8521 D_COST 100
8522 D_COST 100
;

proc sort data=master;
by POLICY_NR INVOICE_NR;
run;

data want;
set master;
length amount 8;

if _n_=1 then do;
  call missing(amount);
  declare hash u(dataset:"updatee");
  u.definekey('POLICY_NR');
  u.definedata('AMOUNT_TYPE','AMOUNT');
  u.definedone();
  end;

by POLICY_NR INVOICE_NR;

if last.INVOICE_NR then do;
output;
if u.find() eq 0 then output;
end;
else output;
run;

[RW9]

As an alternative, use conditional output statements on a merged dataset (using Loko's test data):

data want;

  merge master updatee (rename=(amount_type=amountt amount=amt));

  by policy_nr;

run;

proc sort data=want;

  by policy_nr invoice_nr;

run;

data want2 (drop=amountt amt);

  set want;

  by policy_nr invoice_nr;

  length amount $10;

  if last.invoice_nr then do;

    output;

    amount_type=amountt;

    amount=amt;

    output;

  end;

  else output;

run;

[data_null__ · Accepted solution]

I think using a KEYed SET would work nicely.

data one;
   input (POLICY_NR INVOICE_NR AMOUNT_TYPE STATUS)($);
   cards;
8521  12345 ECO   O
8521  12345 FDM   O
8521  4567  ECO   C
8521  4567  FDM   C
8531  22345 ECO   O
8531  22345 FDM   O
8531  3567  ECO   C
8531  3567  FDM   C
9531  3567  ECO   C
9531  3567  FDM   C
;;;;
   run;
data two(index=(policy_nr));
   input (POLICY_NR   AMOUNT_TYPE AMOUNT)($);
   cards;
8521  D_COST   100
8531  D_COST   200
;;;;
   run;
data three;
   set one;
   by policy_nr invoice_nr;
   output;
   if last.invoice_nr then do;
      set two key=policy_nr/unique;
      if _iorc_ eq 0 then output;
      else _error_=0;
      end;
   call missing(amount);
   run;
proc print;
   by policy_nr;
   id policy_nr;
   run;