Merge two tables and create new columns for values that match certain criteria

[Linh1]

Hi, I have 2 tables, the first one contains week, brand (4 choices) and avgPrice, the second one is the transaction panel data.

I want to display price of all brands in that week in the transaction data but do not know how.

my table is similar to the following:

table 1:

Week     Brand    avgPrice

1             1                10

2             1                 11

1             2                 5

2             2                 6

1             3                 15

2             3                10

......

table2:

householdid      week         brand         dollars      

1                         1                  2              32

3                         2                  1              25

...............

The table I want to create is like:

householdid      week         brand         dollars      Pricebrand1    Pricebrand2    Pricebrand3          Pricebrand4

12345                  1                  2              32                 10                    5                  15

43534                  2                  1              25                 11                    6                   10

...............

[novinosrin · Accepted solution]

data table1;
 input Week     Brand    avgPrice ;
cards;
1             1                10
2             1                 11
1             2                 5
2             2                 6
1             3                 15
2             3                10
;

data table2;
input householdid      week         brand         dollars ;     
cards;
1                         1                  2              32
3                         2                  1              25
;
proc sort data=table1 out=temp(keep=week avgprice);
by week;
run;

proc transpose data=temp out=_temp(drop=_name_) prefix=Pricebrand;
by week;
var avgprice ;
run;
data want;
merge table2 _temp;
by week;
run;

[Linh1]

Thank you so much!

[Tom]

Close, but if any brands are missing for any week you will have an alignment problem.

Better to use ID statement in PROC TRANPOSE to determine the target variable name based on the brand id number.