SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating new smaller dataset from a larger one

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Creating new smaller dataset from a larger one

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


Accepted Solutions
Solution
‎11-08-2011 06:16 PM
Super User
Super User
Posts: 7,076

Re: Creating new smaller dataset from a larger one

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 82

Creating new smaller dataset from a larger one

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;

Occasional Contributor
Posts: 7

Creating new smaller dataset from a larger one

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.

Super User
Posts: 5,437

Re: Creating new smaller dataset from a larger one

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;

Data never sleeps
Occasional Contributor
Posts: 7

Creating new smaller dataset from a larger one

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

letters to show the manipulation i needed.

Super Contributor
Posts: 1,636

Creating new smaller dataset from a larger one

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;

Super User
Posts: 5,437

Creating new smaller dataset from a larger one

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

/Linus

Data never sleeps
Solution
‎11-08-2011 06:16 PM
Super User
Super User
Posts: 7,076

Re: Creating new smaller dataset from a larger one

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;

Occasional Contributor
Posts: 7

Creating new smaller dataset from a larger one

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 352 views
  • 3 likes
  • 5 in conversation