LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous (more recent) messageNext (less recent) messagePrevious (more recent) in topicNext (less recent) in topicPrevious (more recent) by same authorNext (less recent) by same authorPrevious page (March 2003, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: Peter Crawford <peter.crawford@db.com>
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. >


Back to: Top of message | Previous page | Main SAS-L page