Date:         Mon, 12 Jun 2006 02:43:40 -0400
Reply-To:     Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Subject:      Re: can this transpose be done in one proc
Comments: To: gordononline@OPTUSNET.COM.AU

Hi Gordon,

Download MR2RM.zip with MR2RM.sas inside from
http://home.hccnet.nl/jim.groeneveld/software/SASmacro and also follow
instructions in MR2RM.txt

%MR2RM (Data=Bens, Out=All, OverWrit=Y, ByList=ID, VarList=Ben Amt,
   Index=Period, CopyList=Sex);

PROC TRANSPOSE rotates a data matrix, macro MR2RM flattens a data matrix.

Regards - Jim.
--
Jim Groeneveld, Netherlands
Statistician, SAS consultant
home.hccnet.nl/jim.groeneveld

My computer predicts: David C. will say: you don't want to do this.

On Sun, 11 Jun 2006 08:33:30 +1000, gordon
<gordononline@OPTUSNET.COM.AU> wrote:

>Hi,
>
>I am looking at using proc transpose (SA913 and EG3), and I would like to
>move data from a thin form to a fattter form (ie one record for each id).
>While there may be some good reasons not to do this transpose, this type of
>data must be in the fatter format to be able to be appended to an existing
>data structure.
>
>This is test data, and I would like to be able to tranpose both the 'ben'
>and 'amt' fields in the same proc to achieve the fatter form of
>
>id ben1 amt1 ben2 amt2 ben3 amt3...      ben10 amt10 sex
>
>where the suffix of amt and ben is the relative period.  Is this possible
>using proc transpose.  I can probably do this with a do while or a first.
>last. routine but as I am currently learning tranpose it would be handy to
>use that as a solution.
>
>thanks
>
>data bens;
>    input id $2. period ben $3. amt sex $1.;
>    datalines;
>1a 1 nsa 230 M
>1a 2 nsa 230 M
>1a 3 nsa 230 M
>1a 4 nsa 230 M
>1a 5 nsa 230 M
>1a 6 pps 242 M
>1a 7 pps 242 M
>1a 8 nsa 230 M
>1a 9 pps 242 M
>1a 10 nsa 230 M
>2a 1 age 275 F
>2a 2 age 275 F
>2a 3 age 279 F
>2a 4 age 275 F
>2a 5 age 275 F
>2a 6 age 275 F
>2a 7 age 270 F
>2a 8 age 275 F
>2a 9 age 290 F
>2a 10 age 275 F
>3a 1 dsp 253 M
>3a 2 dsp 253 M
>3a 4 dsp 253 M
>3a 5 dsp 253 M
>3a 6 dsp 253 M
>3a 7 dsp 253 M
>3a 9 dsp 253 M
>3a 10 dsp 253 M
>;
>proc sort;
> by id ben;
>run;
>proc transpose data=bens out=amts let prefix=amt;
> by id sex;
>  id period ;
>  var amt;
>run;
>proc sort data=close;
> by id period1-period10;
>run;
> proc transpose data=bens out=bens let prefix=ben;
>  by id ;
>   id period;
>   var ben;
>  run;
>data all (drop=_name_);
> merge bens amts;
>  by id;
>run;
>
>proc print label;
>   title 'Benfits by periods';
>run;
>*****************************************************