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.
I think using a KEYed SET would work nicely.
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;
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;
I think using a KEYed SET would work nicely.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.