topic Re: Merge data vertically in SAS Procedures

Merge data vertically

tampham92 — Tue, 08 May 2018 19:43:34 GMT

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
Account	Mth_Key	Price	Account	Mth_Key	Price
A1	201601	20	A1	201602	88
A2	201601	23	A2	201602	75
B1	201601	33	B1	201602	45
B2	201601	41	B2	201602	33
B3	201601	15	B3	201602	11
C1	201601	75	B4	201602	53
C2	201601	39	C1	201602	22
			C2	201602	10
			D1	201602	77

Data 3
Account	Mth_Key	Price
A1	201601	20
A2	201601	23
B1	201601	33
B2	201601	41
B3	201601	15
C1	201601	75
C2	201601	39
A1	201602	88
A2	201602	75
B1	201602	45
B2	201602	33
B3	201602	11
C1	201602	22
C2	201602	10

Re: Merge data vertically

Tom — Tue, 08 May 2018 20:44:57 GMT

What do you want that is different than?

data data3;
  set data1 data2;
run;

Re: Merge data vertically

PeterClemmensen — Tue, 08 May 2018 21:00:31 GMT

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;

Re: Merge data vertically

novinosrin — Tue, 08 May 2018 22:12:10 GMT

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;

Re: Merge data vertically

mkeintz — Wed, 09 May 2018 17:25:47 GMT

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;

Re: Merge data vertically

PeterClemmensen — Wed, 09 May 2018 20:34:49 GMT

Cool way to use the hash object @mkeintz