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

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!

CodePudding user response：

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