Hi friends, it's difficult to elaborate.
Let's say for Table A, I have column 'firmname' and 'dealyear' representing when a M&A deal was taken place.
and for Table B I have column 'firmname', '2015', '2016', '2017', '2018', and '2019', representing annual average stock value of the company.
Now I wanna add column called 'stockprice' in table A, thus, for the respective deal info in table A there would be a stock value of the company, of the year.
Can anyone let me know how to achieve this?
example of table A:
Company_Name | dealyear | stockprice ( to be added with the value from Table B) |
Abcam plc | 2000 | |
Abeona Therapeutics Inc | 1999 | |
Abiomed Inc | 2001 | |
Acadia Healthcare | 2002 |
example of table B:
Company_Name | _1999 | _2000 | _2001 | _2002 |
Abcam plc | 6.648748 | 7.526305 | 14.21621 | 17.03757 |
Abeona Therapeutics Inc | 602.3358 | 1363.622 | 933.9541 | 667.004 |
Abiomed Inc | 8.457992 | 24.72086 | 20.06118 | 7.379444 |
Acadia Healthcare | 4.092705 | 4.483808 | 1.457923 | 2.454762 |
Transpose your B table from wide to long and look up from there. Makes the whole problem much simpler 🙂
data a;
input Company_Name:$50. dealyear;
infile datalines dlm=',';
datalines;
Abcam plc,2000
Abeona Therapeutics Inc,1999
Abiomed Inc,2001
Acadia Healthcare,2002
;
data b;
input Company_Name:$50. _1999 _2000 _2001 _2002;
infile datalines dlm=',';
datalines;
Abcam plc,6.648748,7.526305,14.21621,17.03757
Abeona Therapeutics Inc,602.3358,1363.622,933.9541,667.004
Abiomed Inc,8.457992,24.72086,20.06118,7.379444
Acadia Healthcare,4.092705,4.483808,1.457923,2.454762
;
/* transpose b from wide to long */
data b_long(keep=Company_Name dealyear stockprice);
set b;
array y{*} _1999-_2002;
do i=1 to dim(y);
dealyear=input(compress(vname(y[i]), '_'), best.);
stockprice=y[i];
output;
end;
run;
proc sql;
create table want as
select a.*,
b_long.stockprice
from a, b_long
where a.Company_Name=b_long.Company_Name
and a.dealyear=b_long.dealyear;
quit;
Transpose your B table from wide to long and look up from there. Makes the whole problem much simpler 🙂
data a;
input Company_Name:$50. dealyear;
infile datalines dlm=',';
datalines;
Abcam plc,2000
Abeona Therapeutics Inc,1999
Abiomed Inc,2001
Acadia Healthcare,2002
;
data b;
input Company_Name:$50. _1999 _2000 _2001 _2002;
infile datalines dlm=',';
datalines;
Abcam plc,6.648748,7.526305,14.21621,17.03757
Abeona Therapeutics Inc,602.3358,1363.622,933.9541,667.004
Abiomed Inc,8.457992,24.72086,20.06118,7.379444
Acadia Healthcare,4.092705,4.483808,1.457923,2.454762
;
/* transpose b from wide to long */
data b_long(keep=Company_Name dealyear stockprice);
set b;
array y{*} _1999-_2002;
do i=1 to dim(y);
dealyear=input(compress(vname(y[i]), '_'), best.);
stockprice=y[i];
output;
end;
run;
proc sql;
create table want as
select a.*,
b_long.stockprice
from a, b_long
where a.Company_Name=b_long.Company_Name
and a.dealyear=b_long.dealyear;
quit;
Also, if you prefer a data step lookup approach, replace the PROC SQL step with
data want;
if 0 then set b_long;
if _N_ = 1 then do;
declare hash h(dataset:'b_long');
h.defineKey('Company_Name', 'dealyear');
h.defineData('stockprice');
h.defineDone();
end;
set a;
rc=h.find();
run;
Or simply merge by Company_Name:
data have_a;
input Company_Name $25. dealyear;
cards;
Abcam plc 2000
Abeona Therapeutics Inc 1999
Abiomed Inc 2001
Acadia Healthcare 2002
;
data have_b;
input Company_Name $25. _1999-_2002;
cards;
Abcam plc 6.648748 7.526305 14.21621 17.03757
Abeona Therapeutics Inc 602.3358 1363.622 933.9541 667.004
Abiomed Inc 8.457992 24.72086 20.06118 7.379444
Acadia Healthcare 4.092705 4.483808 1.457923 2.454762
;
data want(drop=_:);
merge have_a(in=a) have_b;
by Company_Name;
if a;
array _[1999:2002] _:;
stockprice=_[dealyear];
run;
Alternatively, you can omit the ARRAY statement and define stockprice by means of the VVALUEX function:
stockprice=input(vvaluex(cats('_',dealyear)),12.);
Of course, merging by Company_Name requires that there are no spelling differences (upper/lower case, puctuation, etc.) between the two datasets and that they are sorted by this variable.
Hi @jimmychoi Your table B names with _ prefix gives me the impression, that's actually result of a transposed dataset?
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.