topic Re: Proc transpose Problem in SAS Procedures

Proc transpose Problem

lovedieer — Thu, 12 Feb 2015 14:54:44 GMT

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

Re: Proc transpose Problem

Bari_sas — Thu, 12 Feb 2015 15:08:09 GMT

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.

Re: Proc transpose Problem

lovedieer — Thu, 12 Feb 2015 16:39:58 GMT

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.

Re: Proc transpose Problem

lovedieer — Thu, 12 Feb 2015 17:21:27 GMT

I fixed it. Thanks.

Re: Proc transpose Problem

Ksharp — Fri, 13 Feb 2015 10:06:11 GMT

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