topic Re: Combine 2 Tables in SAS Programming

Combine 2 Tables

mrzlatan91 — Wed, 03 Oct 2018 00:17:50 GMT

Hey guys,

I have the following 2 tables old and update and want to have the output like want:

data work.old;
input (Date cusip fundid mod) ($);
datalines;
Mar2012 123  A  5$
Mar2012 124  A  5$
Apr2012 124  B  6$
;

data work.update;
input (Date cusip fundid mod) ($);
datalines;
Apr2012 124  B  7$
May2012 124  B  10$
;

data work.want;
input (Date cusip fundid mod) ($);
datalines;
Mar2012 123  A  5$
Mar2012 124  A  5$
Apr2012 124  B  7$
May2012 124  B  10$
;

That means: Table want should have all (date-cusip)-observations from work.update and

only those (date-cusip)-observations from work.old that are not in work.update.

Each date-cusip-Combination can exist more than once in both tables (with different fundids) but always have the same

value for mod.

It can be said, that work.old is a bigger table than work.update, so it would be perfect if someone could provide me a hash solution

(for better performance).

Thanks in advance 🙂

Re: Combine 2 Tables

ChrisNZ — Wed, 03 Oct 2018 01:23:56 GMT

A simple merge by should do this perfectly. Why a hash solution?

Re: Combine 2 Tables

Astounding — Wed, 03 Oct 2018 01:28:22 GMT

There's probably a hash solution for this, but here's my plain vanilla solution:

proc sort data=old;

by date cusip fundid;

run;

proc sort data=update;

by date cusip fundid;

run;

data want;

merge old update;

by date cusip fundid;

run;

It brings in all the data, but when there is a match uses the data from UPDATE to overwrite the data from OLD.

Re: Combine 2 Tables

Reeza — Wed, 03 Oct 2018 01:33:16 GMT

Would an UPDATE statement work? You need to sort the data ahead of time but it works well.

data want;
update old update;
by date cusip fundid;
run;

@mrzlatan91 wrote:

Hey guys,

I have the following 2 tables old and update and want to have the output like want:
data work.old;
input (Date cusip fundid mod) ($);
datalines;
Mar2012 123  A  5$
Mar2012 124  A  5$
Apr2012 124  B  6$
;

data work.update;
input (Date cusip fundid mod) ($);
datalines;
Apr2012 124  B  7$
May2012 124  B  10$
;

data work.want;
input (Date cusip fundid mod) ($);
datalines;
Mar2012 123  A  5$
Mar2012 124  A  5$
Apr2012 124  B  7$
May2012 124  B  10$
;
That means: Table want should have all (date-cusip)-observations from work.update and

only those (date-cusip)-observations from work.old that are not in work.update.

Each date-cusip-Combination can exist more than once in both tables (with different fundids) but always have the same

value for mod.

It can be said, that work.old is a bigger table than work.update, so it would be perfect if someone could provide me a hash solution

(for better performance).

Thanks in advance 🙂

Re: Combine 2 Tables

novinosrin — Wed, 03 Oct 2018 01:57:48 GMT

Looks a plain append/interleave to me

Re: Combine 2 Tables

s_lassen — Wed, 03 Oct 2018 07:54:34 GMT

If your OLD dataset is large, and you do not need to keep the previous version, updating in place may be the fast way to do it:

data work.old;
  modify old update;
  by date cusip;
  if _iorc_ then do;
    output;
	_error_=0;
	end;
  else replace;
run;

The _IORC_ is set when there is an observation in UPDATE that does not have a correspondent key in OLD.

If you need to improve the performance more, index your OLD table:

Proc sql;
  create Unique index idx on old(date,cusip);
quit;

Re: Combine 2 Tables

mrzlatan91 — Wed, 03 Oct 2018 12:26:38 GMT

thanks, your solution has the best performance 🙂

Re: Combine 2 Tables

mrzlatan91 — Wed, 03 Oct 2018 12:30:41 GMT

yes, this worked as well. thank you 🙂