BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrzlatan91
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

View solution in original post

7 REPLIES 7
Reeza
Super User

Have you looked into either UPDATE or MODIFY Statements?

novinosrin
Tourmaline | Level 20

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
Tourmaline | Level 20
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
Obsidian | Level 7

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
Tourmaline | Level 20

@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
Super User
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
Obsidian | Level 7
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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