Date:         Tue, 3 Apr 2007 12:05:37 -0400
Reply-To:     Jake Bee <johbee@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jake Bee <johbee@GMAIL.COM>
Subject:      Re: follow-up question in PROC Transpose
Comments: To: "Terjeson, Mark" <Mterjeson@russell.com>
In-Reply-To:  <16FD64291482A34F995D2AF14A5C932C015A71C2@MAIL002.prod.ds.russell.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

I guess this will get you there, will have to test on larger dataset.

Sorry just saw the same idea posted.

data have;
input pid $3. date /* anydtdte. */ id $2.;
*format date date9.;
datalines;
DAR  010107 H8
DAR  020107 H8
KJM  020107 H8
KJM  020107 V2
;
run;

proc sort data=have;
 by id date pid;
run;

proc print data=have;
run;

data have2(drop=pid);
 set have;
  by id date pid;
   length pid_string $500;
     retain pid_string;
     if first.date and first.pid then pid_string=pid;

     if ^first.date then
     do;
           pid_string=strip(pid_string)||','||pid;
     end;

     if last.date then output;
run;

proc print data=have2;
run;

proc sort data=have2;
 by id;
run;

proc transpose data=have2 out=t_have2(drop=_name_);
 by id;
  id date;
   var pid_string;
run;

proc print data=t_have2;
run;





On 4/3/07, Terjeson, Mark <Mterjeson@russell.com> wrote:
>
> Hi Douglas,
>
> Here is one of several approaches:
>
>
> data sample;
>    input Obs $ Date $ ID $;
> cards;
> DAR 010107 H8
> DAR 020107 H8
> KJM 020107 V2
> KJM 020107 H8
> ;
> run;
>
>    * prep ordering for first. last. ;
> proc sort data=sample out=temp1;
>    by ID Date;
> run;
>
>    * consolidate multiple Obs into one ;
> data temp2(drop=oldObs);
>    set temp1(rename=(Obs=oldObs));
>    by ID Date; * set first. last. flags ;
>
>        * insure enough length for gathering ;
>    length Obs $50; * or whatever size you need ;
>
>        * collect IDs across rows ;
>    retain Obs '';
>    if first.Date then Obs = '';
>    if Obs eq '' then Obs = oldObs;
>    else Obs = trim(Obs)||', '||oldObs;
>    if last.Date then output;
> run;
>
> proc transpose data=temp2 out=result(drop=_name_);
>    by ID;
>    id Date;
>    var Obs;
> run;
>
>
>
>
> Hope this is helpful.
>
>
> Mark Terjeson
> Senior Programmer Analyst, IM&R
> Russell Investment Group
>
>
> Russell
> Global Leaders in Multi-Manager Investing
>
>
>
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Douglas A. Robinson, Jr.
> Sent: Tuesday, April 03, 2007 7:55 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: follow-up question in PROC Transpose
>
> Firstly, thank you to all that responded to my question last week
> regarding a transpose question in SAS.  I figured out another issue in
> the dataset, and that is the inclusion of multiple entries for a single
> column.  Here are the data:
>
> Obs     Date    ID
> DAR     010107  H8
> DAR     020107  H8
> KJM     020107  V2
> KJM     020107  H8
>
> And I would like this as an output:
>        010107  020107
> H8      DAR             DAR, KJM
> V2                      KJM
>
> What I don't want to happen is this:
>        010107  020107  020107
> H8      DAR             DAR             KJM
> V2                      DAR
>
> What I'm trying to avoid is multiple columns being created for the same
> ID and different dates when more than one observer ('Obs') is listed for
> an ID.
> Once again, I would greatly appreciate any help writing this program.
>
> Cheers,
> Douglas A. Robinson, Jr.
> Department of Biological Sciences
> Binghamton University
> Binghamton, NY 13902-6000
>