## Combining two data sets in a specific way

Solved
Frequent Contributor
Posts: 100

# Combining two data sets in a specific way

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

Input #1:

POLICY_NRINVOICE_NRAMOUNT_TYPESTATUS
852112345ECOO
852112345FDMO
85214567ECOC
85214567FDMC

Input #2:

POLICY_NRAMOUNT_TYPEAMOUNT
8521D_COST100

Desired output:

POLICY_NRINVOICE_NRAMOUNT_TYPEAMOUNTSTATUS
852112345ECOO
852112345FDMO
852112345D_COST100O
85214567ECOC
85214567FDMC
85214567D_COST100C

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.

Accepted Solutions
Solution
‎02-19-2015 08:54 AM
Posts: 3,852

## Re: Combining two data sets in a specific way

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;

All Replies
Super Contributor
Posts: 319

## Re: Combining two data sets in a specific way

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;

Super User
Posts: 9,599

## Re: Combining two data sets in a specific way

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;

Solution
‎02-19-2015 08:54 AM
Posts: 3,852

## Re: Combining two data sets in a specific way

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;

🔒 This topic is solved and locked.