Help using Base SAS procedures

Proc transpose Problem

Reply
Occasional Contributor
Posts: 16

Proc transpose Problem

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.

PersonIDRelationshipContactNameContactphoneContactAddress
1001MotherLInda22233344412345 ABC Str
1001FatherSam11122233312345 ABC Str
1001UncleTom44455566622566 XYZ Rd
1002Sister125785775456 DEF Ln
1002SisterSandy552222 AA Ct
1002BrotherSpencer888888222234 refcvere RD
1002Dave1237587571237 ferwere Dr.
1002SonWalker257275858
1003FriendDenise25785788145666 HJUK St
1003Brother-in-lawJohn112222777678093842 NNNN St
SAS Employee
Posts: 5

Re: Proc transpose Problem

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.

Occasional Contributor
Posts: 16

Re: Proc transpose Problem

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.

Occasional Contributor
Posts: 16

Re: Proc transpose Problem

I fixed it. Thanks.

Super User
Posts: 9,662

Re: Proc transpose Problem

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

Ask a Question
Discussion stats
  • 4 replies
  • 345 views
  • 1 like
  • 3 in conversation