HI,
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.
PersonID | Relationship | ContactName | Contactphone | ContactAddress |
---|---|---|---|---|
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. | |
1002 | Son | Walker | 257275858 | |
1003 | Friend | Denise | 257857881 | 45666 HJUK St |
1003 | Brother-in-law | John | 112222777 | 678093842 NNNN St |
I would suggest creating a unique identifier like this example shows, using _n_ :
22932 - How to use PROC TRANSPOSE to get one record per BY group
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.
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.
I fixed it. Thanks.
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;Xia Keshan
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: