topic Creating new smaller dataset from a larger one in SAS Data Management

Creating new smaller dataset from a larger one

Moein — Tue, 08 Nov 2011 00:41:17 GMT

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

aPrice1 bPrice1

aPrice2 bPrice2

aPrice3 bPrice3

aPrice4 bPrice4

aPrice5 bPrice5

Could someone please help me?

Moe

Creating new smaller dataset from a larger one

ieva — Tue, 08 Nov 2011 09:18:37 GMT

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;

Re: Creating new smaller dataset from a larger one

LinusH — Tue, 08 Nov 2011 11:56:51 GMT

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;

Creating new smaller dataset from a larger one

Moein — Tue, 08 Nov 2011 16:11:20 GMT

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.

Creating new smaller dataset from a larger one

Moein — Tue, 08 Nov 2011 16:12:38 GMT

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

letters to show the manipulation i needed.

Creating new smaller dataset from a larger one

Linlin — Tue, 08 Nov 2011 21:18:12 GMT

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;

Creating new smaller dataset from a larger one

LinusH — Tue, 08 Nov 2011 21:35:54 GMT

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

/Linus

Re: Creating new smaller dataset from a larger one

Tom — Tue, 08 Nov 2011 23:16:57 GMT

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;

Creating new smaller dataset from a larger one

Moein — Wed, 09 Nov 2011 03:17:01 GMT

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