Date: Wed, 26 Mar 2003 11:41:56 +0100
Reply-To: Peter Crawford <peter.crawford@DB.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Peter Crawford <peter.crawford@DB.COM>
Subject: Re: Convert rows to columns
Content-type: text/plain; charset=iso-8859-1
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.
| 
