Help using Base SAS procedures

Merge data vertically

Reply
Occasional Contributor
Posts: 17

Merge data vertically

[ Edited ]

I have data1 and 2. I want to merge them vertically to create data3 but I only want to keep observations as in data1 only. I tried to store account column value into a macro variable but the length exceeded SAS default so it did not work. 

Data 1   Data 2  
AccountMth_KeyPrice AccountMth_KeyPrice
A120160120 A120160288
A220160123 A220160275
B120160133 B120160245
B220160141 B220160233
B320160115 B320160211
C120160175 B420160253
C220160139 C120160222
    C220160210
    D120160277
       
Data 3      
AccountMth_KeyPrice    
A120160120    
A220160123    
B120160133    
B220160141    
B320160115    
C120160175    
C220160139    
A120160288    
A220160275    
B120160245    
B220160233    
B320160211    
C120160222    
C220160210    
Super User
Super User
Posts: 8,115

Re: Merge data vertically

Posted in reply to tampham92

What do you want that is different than?

data data3;
  set data1 data2;
run;
Trusted Advisor
Posts: 1,345

Re: Merge data vertically

The OP apparently wants to drop the D1 record found in data2 but not in data1:

 

data data1; 
input Account $ Mth_Key $ Price;
datalines;
A1 201601 20
A2 201601 23
B1 201601 33
B2 201601 41
B3 201601 15
C1 201601 75
C2 201601 39
;

data data2;
input Account $ Mth_Key $ Price;
datalines;
A1 201602 88
A2 201602 75
B1 201602 45
B2 201602 33
B3 201602 11
B4 201602 53
C1 201602 22
C2 201602 10
D1 201602 77
;

data want;
  set data1 data2;
  if _n_=1 then do;
    declare hash h (dataset:'data1 (keep=account)');
      h.definekey('account');
      h.definedone();
  end;
  if h.find()=0;
run;

 

The above will have all the data1 records first, then all the data2 records.

 

More efficient, but will result in data sets sorted by ACCOUNT.   It assume both datasets are sorted by ACCOUNT.

 

data want (drop=_:);
  set data1 (in=in1) data2;
  by account;
  retain _keep;
  if first.account then do;
    _keep=in1;
  end;
  if _keep;
run;
PROC Star
Posts: 1,283

Re: Merge data vertically

Cool way to use the hash object @mkeintz

PROC Star
Posts: 1,283

Re: Merge data vertically

Posted in reply to tampham92

You can do it like this

 

data data1; 
input Account $ Mth_Key $ Price;
datalines;
A1 201601 20
A2 201601 23
B1 201601 33
B2 201601 41
B3 201601 15
C1 201601 75
C2 201601 39
;

data data2;
input Account $ Mth_Key $ Price;
datalines;
A1 201602 88
A2 201602 75
B1 201602 45
B2 201602 33
B3 201602 11
B4 201602 53
C1 201602 22
C2 201602 10
D1 201602 77
;

proc sql noprint;
   select distinct upcase(strip(Account)) into :acc separated by " " from data1 ;
quit;

%put &acc.;

data data3;
   set data1 data2(where=(indexw("&acc.", Account)>0));
run;
PROC Star
Posts: 1,817

Re: Merge data vertically

Posted in reply to tampham92
data data1; 
input Account $ Mth_Key $ Price;
datalines;
A1 201601 20
A2 201601 23
B1 201601 33
B2 201601 41
B3 201601 15
C1 201601 75
C2 201601 39
;

data data2;
input Account $ Mth_Key $ Price;
datalines;
A1 201602 88
A2 201602 75
B1 201602 45
B2 201602 33
B3 201602 11
B4 201602 53
C1 201602 22
C2 201602 10
D1 201602 77
;
proc sql;
create table want as
  select * from data1
   union all
   select * from data2
   where Account in (select Account from data1);
   quit;
Ask a Question
Discussion stats
  • 5 replies
  • 170 views
  • 1 like
  • 5 in conversation