02-12-2015 09:54 AM
I have a question about transpose, I have a table named CONTACT which have the personID, relationship, contactName, ContactPhone, ContactAddress , each personID may associated with up to 5 contacts. and some information may missing. I want to transpose the data to a structure each person ID only have one row of record, all the different contact will show as Relationship1, contactname1, contactphone1, contactaddress1, realtionship2, contactname2, contactphone2, contactaddress2, relationship3....Etc. Thank you very much for your help.
|1001||Mother||LInda||222333444||12345 ABC Str|
|1001||Father||Sam||111222333||12345 ABC Str|
|1001||Uncle||Tom||444555666||22566 XYZ Rd|
|1002||Sister||125785775||456 DEF Ln|
|1002||Sister||Sandy||552222 AA Ct|
|1002||Brother||Spencer||888888222||234 refcvere RD|
|1002||Dave||123758757||1237 ferwere Dr.|
|1003||Friend||Denise||257857881||45666 HJUK St|
|1003||Brother-in-law||John||112222777||678093842 NNNN St|
02-12-2015 10:08 AM
I would suggest creating a unique identifier like this example shows, using _n_ :
With your data, it may be necessary to keep the _name_ variable from the first Transpose step, then create (with the DATA step) another variable in the data set FIRST for use on the ID statement in the second Transpose step. This new variable would have the values (from _name_): RelationshipN, ContactnameN.
02-12-2015 11:39 AM
Thanks Bari. That's very helpful, finally I have one record per personID. But I still have the problems with the column names, it will create a series of column name such as col1, col2, col3 , col4 for first contact 's relationship, name, phone, address, and col5 col6,col7, col8 for second contact's information, and so on.
How do I add a meaningful column name such as relationship1, name1, phone1, address1, relationship2, name2, phone2, address2......
Thank you very much.
02-13-2015 05:06 AM
The simplest way is double proc transpose, if they are all character type variable.
data test; input (id v1-v3) ($); cards; 11 1 0 1 11 1 0 1 11 1 0 1 21 0 1 1 31 1 1 0 41 1 1 0 41 1 1 0 ; run; data test; set test; by id; if first.id then n=0; n+1; run; proc transpose data=test out=temp; by id n; var v1-v3; run; proc transpose data=temp out=want(drop=_NAME_); by id ; id _NAME_ n; var col1; run;