Problems with Merging

[mrzlatan91]

Hey guys,

Im a student from Germany and having some problems with merging two tables at the moment. I hope someone of you can help me with that 🙂 The problem is:

I have two tables, each of those 2 tables has the following structure:

(Let´s say, this is Table 1)

Date / Share-ID, Fund, Value

Mar2012/ 123 / A / 5$

Apr2012 / 124 / B / 6$

For example, the first row can be understood as: In March 2012, Fund A valued the Share-ID 123 with 5$.

So, let´s say Table 2 looks like this:

Date / Share-ID, Fund, Value

Mar2012/ 123 / C / 6$

Aug2012 / 124 / B / 6$

So, what I want now is this table:

Date / Share-ID, Fund, Value

Mar2012/ 123 / A / 5$

Apr2012 / 124 / B / 6$

Aug2012 / 124 / B / 6$

That means, I want to have all different DATE-SHAREID Combinations of both tables, but always take the one from Table 1 if it´s not missing. Or in other words, I only want to add a row from Table 2 to Table 1, if the Date-ShareID combination is not in Table 1.

I already could solve this problem by using a Full Join, with the following code:

proc sql;
create table dest as select
coalesce(a.shareid, b.shareid) as shareid,
coalesce(a.date, b.date) as date,
a.value as value,
b.value as newValue,
coalesce(a.fundid, b.fundid) as fundid
from work.table1 as a full join
work.table2 as b
on (a.shareID = b.shareID and a.date = b.date);
quit;

data work.dest;
set work.dest;
if missing(value) and not missing(newValue) then do;
value = newValue;
end;
drop newValue;
run;
%end;

Unfortunately, the performance of this program is so bad, that I cant run it on my PC (It´s a Macro, so the program runs at least 1000 times).

I´d really appreciate if someone could provide a smarter solution of this problem 🙂

Thanks in advance and sorry for my bad English.

Kind regards

Zlatan

[Reeza]

Have you looked into either UPDATE or MODIFY Statements?

[novinosrin]

Hi @mrzlatan91, You wrote - "Unfortunately, the performance of this program is so bad, that I cant run it on my PC (It´s a Macro, so the program runs at least 1000 times)."

1. Where is the macro and why macro?

2. Should i give you a hash solution, are you ok with it?

[novinosrin]

data table1;
infile datalines dlm='/';
input (Date  Share_ID Fund Value) ($);
datalines;
Mar2012/ 123 / A / 5$
Apr2012 / 124 / B / 6$
;

data table2;
infile datalines dlm='/';
input (Date  Share_ID Fund Value) ($);
datalines;
Mar2012/ 123 / C / 6$
Aug2012 / 124 / B / 6$
;

data want;
   if _N_ = 1 then do;
   if 0 then set table2;
      declare hash h(dataset:'table2');
      h.defineKey('share_id','date');
      h.defineData(all:'yes');
      h.defineDone();
	  declare hiter iter('h');
      end;
	  set table1 end=last;
	  if h.check()= 0 then h.remove();
	  output;
	  if last then   rc = iter.first();
   do while (rc = 0);
      output;
      rc = iter.next();
   end;
   drop rc;
run;

[mrzlatan91]

Dear navinosrin,

Thank you so much, this is exactly what I wanted to have.

Im using a Macro, because I need to do SQL-Operations and Data-Steps as long 

as a specific condition is valid. 

If you ever come to Germany, you´ll get as much beer as you can drink 😉

Have a nice weekend.

[novinosrin · Accepted solution]

@mrzlatan91 I would like that offer. My favorite is indeed dunkel  dark German lager 🙂 

Ok, if you are satisfied, kindly mark the question as answered and close the thread.  You have a good weekend too. Take care!

[Ksharp]

data table1;
infile datalines dlm='/';
input (Date  Share_ID Fund Value) ($);
datalines;
Mar2012/ 123 / A / 5$
Apr2012 / 124 / B / 6$
;

data table2;
infile datalines dlm='/';
input (Date  Share_ID Fund Value) ($);
datalines;
Mar2012/ 123 / C / 6$
Aug2012 / 124 / B / 6$
;

proc sql;
create table want as
 select *
  from table1
union all
 select * 
  from table2
   where catx(' ',date,share_id) not in 
   (select catx(' ',date,share_id) from table1);
quit;

[mrzlatan91]

Good choice dude. You can also try Munich „Helles“ from Hacker Pschorr (he invented this sort of beer). Yeah I marked it as „solved“. Thanks again
@Ksharp: thanks for your additional solution