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
...............
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.