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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.