Date:         Thu, 18 Apr 2002 17:03:59 -0400
Reply-To:     Jay Weedon <jweedon@EARTHLINK.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jay Weedon <jweedon@EARTHLINK.NET>
Organization: http://extra.newsguy.com
Subject:      Re: Aggregating values across records
Content-Type: text/plain; charset=us-ascii

On 18 Apr 02 20:01:10 GMT, P13@PERSNET.NAVY.MIL (Alderton, David L.
Ph.D.) wrote:

>I would like some constructive criticism on the following code.  I have
>large transaction data files where each record has an identifier (SSN), a
>course code (CDP), and a course date (CDPDT).  What I am trying to do is to
>collect the information into a single record.  I created a small example
>below and code that works.  However, I doubt this is the best way to handle
>this.  Does anyone have any good suggestions?
>
>Thanks for the advice,
>       David.
>David L. Alderton, Ph.D.
>NPRST/PERS-1
>
>----------------------------Code----------------------------------
>data test;
>   input ssn cdp $3. cdpdt date7. ;
>   cards;
>6 adf 02JUL01
>5 adg 03AUG01
>1 abd 07JUL00
>3 adg 03AUG01
>5 adh 13AUG01
>1 abc 07JUN00
>2 abc 07JUN00
>3 ade 01JUN00
>5 adi 23AUG01
>3 adf 02JUL01
>4 abc 03AUG01
>;
>proc sort data=test;
>   by ssn cdpdt ;
>   run;
>data two;
>   set test;
>     by ssn;
>   array cdps(3) $3 cdp1-cdp3;
>   array cdpdts(3) cdpdt1-cdpdt3;
>   retain cdp1-cdp3 ' ' cdpdt1-cdpdt3 . ;
>     CDPcnt+1;
>   if first.ssn then
>     do;
>       CDPcnt=1;  /*reset CDPcnt*/
>         do j=1 to 3;  /*reinitialize cdps and cdpdts*/
>                    cdps(j)=' '; cdpdts(j)=.;
>                 end;
>          end;
>     cdps(CDPcnt)=cdp; cdpdts(CDPcnt)=cdpdt;
>   if last.ssn then output;
>   drop cdp cdpdt j ;
>  run;
>proc print data=test; format cdpdt date7. ; title "data=test"; run;
>proc print data=two;
>    var ssn CDPcnt cdp1 cdpdt1 cdp2 cdpdt2 cdp3 cdpdt3 ;
>    format cdpdt1-cdpdt3 date7. ;
>    title "data=two";
>    run;

Here's another approach:

proc transpose data=test out=scratch1 prefix=cdp;
  by ssn; var cdp;

proc transpose data=test out=scratch2 prefix=cdpdt; by ssn;
  var cdpdt;

data final; merge
  scratch1 scratch2;
  by ssn;
run;

JW