How to multiply and merge two tables in SAS using a bridge table?

Question

I am trying to merge two SAS tables based on a third “bridge table” and perform some calculations during the process. The code should be like “For each good lookup the price and calculate the annual revenue.”

My raw data: one table with annual quantity of goods, one table with prices and one bridge table with the information which price is used for which good.

data work.goods;
  input date date. GoodA GoodB GoodC;
  format date year. ;
  datalines;
    01Jan20 10 12 2
    01Jan21 12 11 5
run;`

data work.price;
  input date date. PriceA PriceB;
  format date year.;
  datalines;
    01Jan20 220 110
    01Jan21 250 120
run;

data work.bridgetable;
  input goods $5. price $7.;
  datalines;
    GoodA PriceA
    GoodB PriceB
    GoodC PriceB
run;

So far, I used a proc sql statement without the information in the bridge table.

proc sql;
  create table work.result as
  select  goods.date,
          goods.GoodA * price.PriceA as RevenueA,
          goods.GoodB * price.PriceB as RevenueB,
          goods.GoodC * price.PriceB as RevenueC
  from work.goods as goods, work.price as price
  where goods.date = price.date;
quit;

Now, I would like to use the information from the bridge table, so that I can change the assignment of a price to a good (e.g. instead of PriceB PriceA is used for GoodC). In addition, I’d like to have the code more dynamic without the hardcoding so that I can add new goods and prices in my tables without re-coding the ‘select’ part of my sql statement.

How do I implement the bridge table in proc sql?

Thanks a lot for your help!

Just transpose your first two tables so the good name and price name are stored in variables instead of in metadata (name of the variable). Then you can just join the three tables. — Tom, Feb 03 '22 at 13:49

score 4 · Accepted Answer · answered Feb 03 '22 at 14:02

Your first two tables need to be vertical and not horizontal. Then the structure will not change when new goods or new price categories are added.

You can use PROC TRANSPOSE to convert your current tables.

data goods;
  input year GoodA GoodB GoodC;
datalines;
2020 10 12 2
2021 12 11 5
;`

data price;
  input year PriceA PriceB;
datalines;
2020 220 110
2021 250 120
;

data bridgetable;
  input goods $5. price $7.;
datalines;
GoodA PriceA
GoodB PriceB
GoodC PriceB
;

proc transpose data=goods 
  name=goods
  out=goods_tall(rename=(col1=amount))
;
  by year;
  var good: ;
run;

proc transpose data=price 
  name=price
  out=price_tall(rename=(col1=unit_price))
;
  by year;
  var price: ;
run;

Now the tables are easy to join.

proc sql ;
create table want as 
  select *,unit_price*amount as revenue
  from goods_tall
  natural join price_tall
  natural join bridgetable
;
quit;

Results

                                            unit_
Obs    goods    price     year    amount    price    revenue

 1     GoodA    PriceA    2020      10       220       2200
 2     GoodB    PriceB    2020      12       110       1320
 3     GoodC    PriceB    2020       2       110        220
 4     GoodA    PriceA    2021      12       250       3000
 5     GoodB    PriceB    2021      11       120       1320
 6     GoodC    PriceB    2021       5       120        600

Thanks a lot. I had to change "by year" in "by date" in "proc transpose" to make it work and now it runs. Perfect! — chris, Feb 09 '22 at 08:20
Yes. I simplified the example to just use YEAR as you appeared to only need the year and not a full date. Note that if your variable is an actual date value with YEAR format attached the BY statement will treat different dates in the same year as different by groups since it uses the actual value not the text displayed by the format to do the grouping. — Tom, Feb 09 '22 at 15:09

How to multiply and merge two tables in SAS using a bridge table?

1 Answers1