Date: Thu, 27 Mar 2003 17:14:47 -0500
Reply-To: "Mullins, Mark" <MLMullins@BBANDT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Mullins, Mark" <MLMullins@BBANDT.COM>
Subject: Re: Convert rows to columns
Content-Type: text/plain; charset="iso-8859-1"
Peter,
Thanks as always for your valuable input... as well to Ian Whitlock and Arun
Kumar for their suggestions... in my production environment I went with
Peter's solution: it looks like this:
%macro suf( base, suf_list )/des='apply suffixes in list to base';
%local word i;
%let i=1;
%let word = %scan( &suf_list, &i );
%do %while( &word ne ) ;
&base&word
%let i = %eval( 1+ &i );
%let word = %scan( &suf_list, &i );
%end;
%mend;
* combine datasets....;
proc sql;
create table frommem as
select a.loan_id, a.s560_officer_name, a.s560_officer_no,
a.s560_officer_title,
a.s560_officer_type,
b.s530_comment, b.s530_author, b.s530_desc_1,
c.s610_decision_code, c.s610_recommend_ind
from seg_560 as a left join seg_530 as b on
a.loan_id = b.loan_id and
a.s560_counter = b.s530_counter
left join seg_610 as c on
a.loan_id = c.loan_id and
a.s560_counter = c.s610_counter
where a.work_per_bus ne 'P'
;
quit;
* now, get var lengths from input data set;
proc sql noprint;
create table tab_meta as select * from dictionary.columns
where libname="WORK" and memname="FROMMEM"
;
* now get some attrs... var lengths;
select length format=dollar4. into :t_len separated by ' '
from tab_meta where lowcase(name)='s560_officer_title';
select length format=dollar4. into :n_len separated by ' '
from tab_meta where lowcase(name)='s560_officer_name';
select length into :o_len separated by ' '
from tab_meta where lowcase(name)='s560_officer_no';
select length format=dollar4. into :d_len separated by ' '
from tab_meta where lowcase(name)='s610_decision_code';
select length format=dollar4. into :r_len separated by ' '
from tab_meta where lowcase(name)='s610_recommend_ind';
select length format=dollar4. into :c_len separated by ' '
from tab_meta where lowcase(name)='s530_comment';
quit;
* now the array handling ........;
data decisions;
array Atitle(0:6) &t_len %suf( title_,xx de ao at do co oo) ;
array Aname(0:6) &n_len %suf( name_,xx de ao at do co oo) ;
array Aoffic(0:6) &o_len %suf( offno_,xx de ao at do co oo) ;
array Adeccd(0:6) &d_len %suf( decis_,xx de ao at do co oo) ;
array Arecin(0:6) &r_len %suf( recind_,xx de ao at do co oo) ;
array Acomme(0:6) &c_len %suf(comment_,xx de ao at do co oo) ;
do until( last.loan_id ); *<-------<<< classic DOW loop !;
set frommem ;
by loan_id;
index = indexw( ' DE AO AT DO CO OO', trim(s560_officer_type)) /3;
* result is index=0 for types not in the given list
and that 0 will index the 0-th array element
which has suffix xx ;
atitle( index ) = s560_officer_title ;
aname( index ) = s560_officer_name ;
aoffic( index ) = s560_officer_no ;
adeccd( index ) = s610_decision_code ;
arecin( index ) = s610_recommend_ind ;
acomme( index ) = s530_comment ;
end;
keep loan_id title_: name_: offno_: decis_: recind_: comment_:;
run;
I learn so much from SAS-L, but arrays, I believe are what keep me from
being a full-time programmer. Thanks again!
Mark L. Mullins
BB&T - Small Business Banking
Portfolio Administration Manager
* 336.733.2667
* mailto:MLMullins@BBandT.com
________________________________________________________________________
> Privileged/confidential information may be contained in this message. If
> you are not the addressee indicated in this message (or responsible for
> delivery of the message to such person), you may not copy or deliver this
> message to anyone. In such case, you should destroy the message and
> kindly notify the sender by reply e-mail.
>
>
> -----Original Message-----
> From: Peter Crawford [SMTP:peter.crawford@db.com]
> Sent: Wednesday, March 26, 2003 5:42 AM
> To: Mullins, Mark
> Cc: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: Convert rows to columns
>
>
> with Mark (mlmullins@bbandt.com) latest definitions
> perhaps an array based approach looks simpler than
> proc transpose !
>
> First,
> *test data...;
> libname fromlib '!TEMP';
>
> data fromlib.frommem;
> length Loan_Id $6 Title $9 Name $15 Officer_No 6 Officer_Type $3 ;
> * these lengths and types are just my guesswork........;
> infile cards dsd truncover ;
> input loan_id -- officer_type ;
> * Loan_Id Title Name Officer_No Officer_Type;
> cards;
> 23 ,BSA ,Wilma , 82 , AO
> 23 ,BSA ,Wilma , 82 , DO
> 23 ,BSO ,Fred ,103 , CO
> 321 , Major , Issue, 4567891293, 12 , /*invalid type */
> 321 , Captain , trialist, 99567891293, DO ,
> 321 , Commodore , sailor,199567891293, DE , /* large number*/
> ; *** large value in officer_no to test adequacy of length;
>
>
> * now, get var lengths from input data set;
> proc sql noprint;
> create table tab_meta as select * from dictionary.columns
> where libname="FROMLIB" and memname="FROMMEM"
> ;
> * now get some attrs... var lengths;
> select length format=dollar4. into :t_len separated by ' '
> from tab_meta where lowcase(name)='title';
> select length format=dollar4. into :n_len separated by ' '
> from tab_meta where lowcase(name)='name';
> select length into :o_len separated by ' '
> from tab_meta where lowcase(name)='officer_no';
> quit;
>
> * now the array handling ........;
> data final_solution;
> array Atitle(0:5) &n_len %suf(title_,xx de ao do co oo) ;
> array Aname(0:5) &n_len %suf( name_,xx de ao do co oo) ;
> array Aoffic(0:5) &o_len %suf( oo_,xx de ao do co oo) ;
>
> do until( last.loan_id ); *<-------<<< classic DOW loop !;
> set fromlib.frommem ;
> by loan_id;
> index = indexw( ' DE AO DO CO OO', trim(officer_type)) /3;
> * result is index=0 for types not in the given list
> and that 0 will index the 0-th array element
> which has suffix xx ;
> atitle( index ) = title ;
> aname( index ) = name ;
> aoffic( index ) = officer_no ;
> end;
> keep loan_id title_: name_: oo_: ;
> run;
>
> That obviously wants a copy of my suffixing macro for naming
> the array elements....
> %macro suf( base, suf_list )/des='apply suffixes in list to base';
>
> %local word i;
> %let i=1;
> %let word = %scan( &suf_list, &i );
> %do %while( &word ne ) ;
> &base&word
> %let i = %eval( 1+ &i );
> %let word = %scan( &suf_list, &i );
> %end;
> %mend;
> * demonstrating my style concept for coding sas macros
> that generate text without going through a sas
> statement boundary............... :
> macro code should start with a large indent
> and
> generated text should left align
> to
> separate clearly macro logic from generated text
> and
> minimise leading/embedded blanks in generated text
> ;;;;;;;;
>
> Hope it is useful
>
> Regards
> Peter Crawford
>
>
>
>
>
>
>
> Datum: 26/03/2003 02:56
> An: SAS-L@LISTSERV.UGA.EDU
>
>
>
>
> Antwort an: Mark Mullins <MLMullins@BBANDT.COM>
>
> Betreff: Re: Convert rows to columns
> Nachrichtentext:
>
> 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
>
>
>
>
>
> --
>
> Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte
> Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail
> irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und
> vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte
> Weitergabe dieser Mail ist nicht gestattet.
>
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received this e-mail in error)
> please notify the sender immediately and destroy this e-mail. Any
> unauthorized copying, disclosure or distribution of the material in this
> e-mail is strictly forbidden.
>
| 
