LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (November 2004, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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 >


Back to: Top of message | Previous page | Main SAS-L page