BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Moein
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
ieva
Pyrite | Level 9

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
Calcite | Level 5

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
Tourmaline | Level 20

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
Moein
Calcite | Level 5

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

letters to show the manipulation i needed.

Linlin
Lapis Lazuli | Level 10

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
Tourmaline | Level 20

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
Tom
Super User Tom
Super User

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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