Selecting columns line by line, when joining tables

[jimmychoi]

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

[PeterClemmensen · Accepted solution]

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;

[jimmychoi]

Hi Draycut, your code works just perfect. and for the transposing part, can I do similar thing using SQL?

[PeterClemmensen]

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;

[FreelanceReinh]

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.

[novinosrin]

Hi @jimmychoi   Your table B names with _ prefix gives me the impression, that's actually  result of a transposed dataset?