Fred,

Here is one way to do this:

data test;

input product $ year order sale;

datalines;

abc 1999 200 100

xyz 1999 100 50

cdf 2000 30 20

abc 2000 300 180

;

run;

proc transpose data=test out=tranord(drop=_name_) prefix=ord_;

by year;

var order;

id product;

run;

proc transpose data=test out=transale(drop=_name_) prefix=sal_;

by year;

var sale;

id product;

run;

* sort datasets by year if not already sorted ;

data fred_out;

merge tranord transale;

by year;

run;

Retaining the sale variables in the above result dataset should be easy. Option (keep= sal:) in proc sql would retain all variables starting with sal.

proc sql;

create table sales(keep=year sal:) as

select * from result;

run;

HTH, Amit

-----Original Message----- From: Fred [mailto:ieaggie2002@GMAIL.COM] Sent: Tuesday, November 30, 2004 9:57 AM To: SAS-L@LISTSERV.UGA.EDU Subject: [SAS-L] How to perform this special TRANSPOSE and SQL proc?

Hi, all

I have the following data set D1: Product Year Order Sale abc 1999 200 100 xyz 1999 100 50 cdf 2000 30 20 abc 2000 300 180 ...

1) I want to transpose the above table such that the new VARIABLES in D2 are: Year abc_Order abc_Sale xyz_Order xyz_Sale cdf_Order cdf_Sale .... 1999 200 100 100 50 . . 2000 300 180 . . 30 20 ...

In PROC TRANSPOSE, how to control the options to produce this table?

2). I also need to segment the transposed new table by selecting the columns whose name include "Sale"? Should I use macro to achieve this SQL selection proc?

Thanks for your help.

Fred