Date:         Tue, 25 Mar 2003 18:51:16 -0800
Reply-To:     Mark Mullins <MLMullins@BBANDT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mark Mullins <MLMullins@BBANDT.COM>
Organization: http://groups.google.com/
Subject:      Re: Convert rows to columns
Content-Type: text/plain; charset=ISO-8859-1

virgile@RCN.COM (Robert Virgile) wrote in message news:<000001c2f2e2$6f1a23f0$7888accf@CPQ26937621911>...
> Mark,
>
> As you suspect, this is a relatively easy problem using arrays, the
> retain statement, and BY variables in a data step ... as long as you
> know a few things about your data:
>
> 1. Do you know the values of officer_type ahead of time?
>
> 2. Are you sure that the officer_type values are clean (no bad values,
> no mix of upper and lower case, no duplicate records for an officer_type
> for the same loan_id)?
>
> Those considerations play a huge role in determining the complexity of
> the solution.
>
> Tell us more, and I'm sure you'll get more of a response.
>
> Good luck.
>
> Bob V.
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@listserv.uga.edu] On Behalf Of
> Mark Mullins
> Sent: Monday, March 24, 2003 9:54 PM
> To: SAS-L@listserv.uga.edu
> Subject: Convert rows to columns
>
> SAS-L:
>
> I have the following dataset:
>
> Loan_Id     Title     Name     Officer_No     Officer_Type
> 23          BSA       Wilma     82             AO
> 23          BSA       Wilma     82             DO
> 23          BSO       Fred     103             CO
>
> I would like to create a single row for each loan_id so that the
> resulting columns are suffixed by the officer type, such that the new
> columns are:
>
> Loan_id
> title_ao
> name_ao
> no_ao
> type_ao
> title_do
> name_do
> no_do
> type_do
> title_co
> name_co
> no_co
> type_co
>
> I am assuming that I will need to use an array to store these values,
> since each loan_id could have anywhere from 1 - 4 records... I have
> not worked with these very much, and would like some help on how to go
> about this... Valuable help greatly appreciated!
>
> Mark Mullins
> BB&T
> mlmullins@bbandt.com

Robert,

The answer to both of your questions is yes... we do know the possible
values, that is, there are five possibilities (DE, AO, DO, CO, OO)..
not all will be used all of the time, and there are no bad values...
but if there was, could you develop an 'otherwise' suffix such as xx
that could take that situation into account?

Thanks a bunch for your help!

Mark