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
@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!
Have you looked into either UPDATE or MODIFY Statements?
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?
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;
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.
@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!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.