Help using Base SAS procedures

Combining two data sets in a specific way

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

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
Respected Advisor
Posts: 3,799

Re: Combining two data sets in a specific way

Posted in reply to EinarRoed

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;


2-19-2015 7-53-36 AM.png

View solution in original post


All Replies
Super Contributor
Posts: 308

Re: Combining two data sets in a specific way

Posted in reply to EinarRoed

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
Super User
Posts: 7,942

Re: Combining two data sets in a specific way

Posted in reply to EinarRoed

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
Respected Advisor
Posts: 3,799

Re: Combining two data sets in a specific way

Posted in reply to EinarRoed

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;


2-19-2015 7-53-36 AM.png
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 222 views
  • 7 likes
  • 4 in conversation