topic Combining two data sets in a specific way in SAS Procedures

Combining two data sets in a specific way

EinarRoed — Thu, 19 Feb 2015 12:25:13 GMT

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.

Re: Combining two data sets in a specific way

Loko — Thu, 19 Feb 2015 12:51:03 GMT

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;

Re: Combining two data sets in a specific way

RW9 — Thu, 19 Feb 2015 13:15:37 GMT

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;

Re: Combining two data sets in a specific way

data_null__ — Thu, 19 Feb 2015 13:54:48 GMT

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;