DATA Step, Macro, Functions and more

Appending and Merging in the same Data Step

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Appending and Merging in the same Data Step

I have three data sets and I want to append of two them on each other then join the appended datasets to a third data set. See code below for sample data and existing process. I would like to get the same result as the Cust_ID_Check dataset in a single data step using the individual, company, and exist_check data sets.

 

data individual;

input Record_ID Cust_ID $5.;

datalines;

 

35 AD123

74 NEW

24 GH456

;

run;

 

data company;

input Record_ID Cust_ID $5.;

datalines;

 

21 YE789

62 AG&7

93 JI245

;

run;

 

data exist_check;

input Customer $4. CN_ID $5.;

datalines;

 

Sam AD123

Mia JI245

Jon YE789

Ben GH456

;

run;

 

Data TEST;

set individual (keep=Record_ID Cust_ID) company(keep=Record_ID Cust_ID);

run;

 

proc sql;

create table Cust_ID_Check as

select A.*, B.CN_ID

from TEST as A

left join exist_check as B

on (A.Cust_ID=B.CN_ID);

quit;

 


Accepted Solutions
Solution
‎03-29-2018 02:54 PM
PROC Star
Posts: 8,114

Re: Appending and Merging in the same Data Step

Yes, you can do the same thing with a data step merge, but the three files would first have to be sorted. i.e.,

proc sort data=individual;
  by Cust_ID;
run;

proc sort data=company;
  by Cust_ID;
run;

proc sort data=exist_check;
  by CN_ID;
run;

data want;
  merge individual
        company 
        exist_check (drop=Customer in=inC rename=(CN_ID=Cust_ID));
  by Cust_ID;
  if inC then CN_ID=Cust_ID;
run;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎03-29-2018 02:54 PM
PROC Star
Posts: 8,114

Re: Appending and Merging in the same Data Step

Yes, you can do the same thing with a data step merge, but the three files would first have to be sorted. i.e.,

proc sort data=individual;
  by Cust_ID;
run;

proc sort data=company;
  by Cust_ID;
run;

proc sort data=exist_check;
  by CN_ID;
run;

data want;
  merge individual
        company 
        exist_check (drop=Customer in=inC rename=(CN_ID=Cust_ID));
  by Cust_ID;
  if inC then CN_ID=Cust_ID;
run;

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 1,328

Re: Appending and Merging in the same Data Step

Hashes can make you avoid sort and accomplish in one datastep as you wanted:

 

data individual;

input Record_ID Cust_ID $5.;

datalines;
35 AD123
74 NEW
24 GH456
;

run;

 

data company;

input Record_ID Cust_ID $5.;

datalines;
21 YE789
62 AG&7
93 JI245
;

run;

 

data exist_check;

input Customer $4. CN_ID $5.;

datalines;
Sam AD123
Mia JI245
Jon YE789
Ben GH456
;

run;

data want;
if _n_=1 then do;
if 0 then do;set individual; set exist_check;end;
   dcl hash H (dataset:'exist_check') ;
   h.definekey  ("CN_ID") ;
   h.definedata ("CN_ID") ;
   h.definedone () ;
   end;
set individual company;
if h.find(key:Cust_ID) ne 0 then call missing(CN_ID);
drop customer;
run;

 

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 106 views
  • 2 likes
  • 3 in conversation