topic Re: Problems with Merging in SAS Programming

Problems with Merging

mrzlatan91 — Sat, 13 Jan 2018 02:13:10 GMT

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

Re: Problems with Merging

Reeza — Sat, 13 Jan 2018 03:51:44 GMT

Have you looked into either UPDATE or MODIFY Statements?

Re: Problems with Merging

novinosrin — Sat, 13 Jan 2018 04:54:09 GMT

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?

Re: Problems with Merging

novinosrin — Sat, 13 Jan 2018 06:34:52 GMT

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;

Re: Problems with Merging

mrzlatan91 — Sat, 13 Jan 2018 09:39:25 GMT

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.

Re: Problems with Merging

Ksharp — Sat, 13 Jan 2018 10:19:37 GMT

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;

Re: Problems with Merging

novinosrin — Sat, 13 Jan 2018 17:15:57 GMT

@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!

Re: Problems with Merging

mrzlatan91 — Sat, 13 Jan 2018 20:39:15 GMT

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