Creating new smaller dataset from a larger one

[Moein]

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

[ieva]

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;

[Moein]

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.

[LinusH]

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;

[Moein]

Thanks for this answer. My data has numbers and I was using

letters to show the manipulation i needed.

[Linlin]

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;

[LinusH]

If you could give us some more near real world sample data we might give you some better suggestions...

/Linus

[Tom · Accepted solution]

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;

[Moein]

As you correctly thought, there are no unique ID variables. Your answer neatly does the trick. Thank you.