Hi,
I have a large panel data set of weekly product prices for a number of products.
Now I need to take the large column of
prices and puts the prices of each product in one new column in a
new data set.
I want to go from:
Product A aPrice1
Product A aPrice2
Product A aPrice3
Product A aPrice4
Product A aPrice5
Product B bPrice1
Product B bPrice2
Product B bPrice3
Product B bPrice4
Product B bPrice5
To
aPrice1 bPrice1
aPrice2 bPrice2
aPrice3 bPrice3
aPrice4 bPrice4
aPrice5 bPrice5
Could someone please help me?
Moe
Why did you pair aPrice1 with bPrice1 instead of some other value of the price for product B?
Are there not ID variables in the data that you could use to merge?
If there are no ID variables then you might get what you want using a merge WITHOUT a by statement.
data want;
merge have(where=(var1='Product A') keep=var1 var2 rename=(var2=Aprice))
have(where=(var1='Product B') keep=var1 var2 rename=(var2=Bprice))
;
* NO by statement ;
drop var1 ;
run;
Hi,
Sounds like you need to use PROC TRANSPOSE. In your data, do you have a variable indicating week number?
data test;
input product $ 1-9 price $ week $;
datalines;
Product A aPrice1 201101
Product A aPrice2 201102
Product A aPrice3 201103
Product A aPrice4 201104
Product A aPrice5 201105
Product B bPrice1 201101
Product B bPrice2 201102
Product B bPrice3 201103
Product B bPrice4 201104
Product B bPrice5 201105
;
run;
proc sort data=test;
by week;
run;
proc transpose data=test out=test_transposed (drop=_name_);
by week;
id product;
var price;
run;
Thanks for this neat reply. This would work if my weeks were unique for each product.
But since it's a panel data, the same weeks recur for different stores. I'll try to see how
I can use the proc transpose.
Thanks again.
Is your price column containing a price id, or is the price itself?
If it is an id, and it is built using a naming convention as in your example, you could accomplish this by joining the table with itself.
proc sql;
select test_a.price as price_a,
test_b.price as price_b
from test as test_a,
test as test_b
where substr(test_a.price,2) = substr(test_b.price,2) and
substr(test_a,1,1) = 'a' and
substr(test_b,1,1) = 'b'
;
quit;
Thanks for this answer. My data has numbers and I was using
letters to show the manipulation i needed.
data have;
input product $ 1-9 price $ ;
num=substr(price,length(price),1);
datalines;
Product A aPrice1
Product A aPrice2
Product A aPrice3
Product A aPrice4
Product A aPrice5
Product B bPrice1
Product B bPrice2
Product B bPrice3
Product B bPrice4
Product B bPrice5
;
run;
proc sort;
by num;
proc transpose data=have out=want (drop=_name_ num);
by num;
id product;
var price;
run;
If you could give us some more near real world sample data we might give you some better suggestions...
/Linus
Why did you pair aPrice1 with bPrice1 instead of some other value of the price for product B?
Are there not ID variables in the data that you could use to merge?
If there are no ID variables then you might get what you want using a merge WITHOUT a by statement.
data want;
merge have(where=(var1='Product A') keep=var1 var2 rename=(var2=Aprice))
have(where=(var1='Product B') keep=var1 var2 rename=(var2=Bprice))
;
* NO by statement ;
drop var1 ;
run;
As you correctly thought, there are no unique ID variables. Your answer neatly does the trick. Thank you.
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 16. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.