DATA Step, Macro, Functions and more

Problems with Merging

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Problems with Merging

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 Smiley Happy 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 Smiley Happy

Thanks in advance and sorry for my bad English.

 

Kind regards

Zlatan


Accepted Solutions
Solution
‎01-13-2018 03:33 PM
PROC Star
Posts: 1,294

Re: Problems with Merging

Posted in reply to mrzlatan91

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

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

View solution in original post


All Replies
Super User
Posts: 22,820

Re: Problems with Merging

Posted in reply to mrzlatan91

Have you looked into either UPDATE or MODIFY Statements?

PROC Star
Posts: 1,294

Re: Problems with Merging

Posted in reply to mrzlatan91

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?

PROC Star
Posts: 1,294

Re: Problems with Merging

Posted in reply to mrzlatan91
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;
Occasional Contributor
Posts: 16

Re: Problems with Merging

Posted in reply to novinosrin

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.

Solution
‎01-13-2018 03:33 PM
PROC Star
Posts: 1,294

Re: Problems with Merging

Posted in reply to mrzlatan91

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

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

Super User
Posts: 10,610

Re: Problems with Merging

Posted in reply to mrzlatan91
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;
Highlighted
Occasional Contributor
Posts: 16

Re: Problems with Merging

Posted in reply to 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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 300 views
  • 2 likes
  • 4 in conversation