Date:         Tue, 30 Nov 2004 10:10:27 -0600
Reply-To:     Pudding Man <pudding.man@gmail.com>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Pudding Man <pudding.man@GMAIL.COM>
Subject:      Re: How to perform this special TRANSPOSE and SQL proc?
Comments: To: Fred <ieaggie2002@gmail.com>
In-Reply-To:  <13a7bb3204113006577f63a74f@mail.gmail.com>
Content-Type: text/plain; charset=US-ASCII

1.) A single TRANSPOSE should be suffucient:

data d1(keep = year var_name var_value);
  input Product $ Year    Order      Sale;
    var_name= trim(product) || '_Order'; var_value = order; output;
    var_name= trim(product) || '_Sale' ; var_value = sale ; output;
cards;
abc              1999      200        100
xyz              1999      100         50
cdf               2000      30           20
abc              2000      300        180
; run;

proc transpose out = d2(drop = _name_);
  var var_value;
  id var_name;
  by year;
  run;

proc print; run;


2.) Consider reversing the order of the two-level var
    names:

data d1(keep = year var_name var_value);
  input Product $ Year    Order      Sale;
    var_name= 'Order_' || product; var_value = order; output;
    var_name= 'sale_' || product; var_value = sale; output;
cards;
abc              1999      200        100
xyz              1999      100         50
cdf               2000      30           20
abc              2000      300        180
; run;

proc transpose out = d2(drop = _name_);
  var var_value;
  id var_name;
  by year;
  run;

proc print data = d2(keep = year sale:); run;

Hope it hep's ...

  Skoal,
  Puddin'

******************************************************
*** Puddin' Man      PuddingDotMan at GmailDotCom  ***
******************************************************;

"... they gotta handful of 'gimme'
 and a mouthful of 'much obliged'!"
      - Sleepy John Estes

On Tue, 30 Nov 2004 09:57:28 -0500, Fred <ieaggie2002@gmail.com> wrote:
> 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
>