topic Re: Merge two tables and create new columns for values that match certain criteria in SAS Programming

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

Linh1 — Sun, 28 Apr 2019 16:00:28 GMT

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

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

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

novinosrin — Sun, 28 Apr 2019 16:20:45 GMT

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;

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

Linh1 — Sun, 28 Apr 2019 19:26:28 GMT

Thank you so much!

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

Tom — Sun, 28 Apr 2019 19:37:55 GMT

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.