topic Re: Help Transposing a Column with Duplicate Values in SAS Programming

Help Transposing a Column with Duplicate Values

mreynaud — Wed, 24 Nov 2021 21:26:32 GMT

Hello,

The table below is what I currently have. Lets call it "Test" for this example

What I want is to transpose the phone_type column and have its values contain the phone numbers from the phone_number column.

Here is the ideal table I want, we'll call it "Final"

I've been trying the following code:

proc transpose data=final out=test;
id phone_type;
var phone_number;
run;

And get this error:

ERROR: The ID value "Home" occurs twice in the input data set.

Any help I can get would be wonderful!

Difficulty Transposing Columns with Duplicate values

mreynaud — Wed, 24 Nov 2021 21:10:50 GMT

Hello,

I am trying to create a table called "Final" that will look like this (for this example I am using made up data):

Client_ID	Client_Name	Cell	Home	Text Message Opt-in
1	Kate	(111)111-1111	(101)010-1010	No
2	Jack		(202) 020-2020
3	Shannon	(333)333-3333	(303)030-3030	Yes
4	John	(444)444-4444	(404) 040-4040	No
5	Juliet	(555)555-5555		Yes
6	Sayid	(666)666-6666		No
7	Claire		(707) 070-7070
8	Alex	(888)888-8888	(808) 080-8080	Yes
9	Charlie	(999)999-9999		No

The table I am working from is called "Test". I've been trying to use Proc Transpose for the columns Phone_Type to create two new columns. The two new columns would be Home and Cell and its values would contain the numbers from the Phone_Number column.

Client_ID	Client_Name	Phone_Type	Phone_Number	Text Message Opt-in
1	Kate	Cell	(111)111-1111	No
1	Kate	Home	(101)010-1010
2	Jack	Home	(202) 020-2020
3	Shannon	Cell	(333)333-3333	Yes
3	Shannon	Home	(303)030-3030
4	John	Cell	(444)444-4444	No
4	John	Home	(404) 040-4040
5	Juliet	Cell	(555)555-5555	Yes
6	Sayid	Cell	(666)666-6666	No
7	Claire	Home	(707) 070-7070
8	Alex	Cell	(888)888-8888	Yes
8	Alex	Home	(808) 080-8080
9	Charlie	Cell	(999)999-9999	No

Here is the code I've been attempting to use

proc transpose data=final out=test;
id phone_type;
var phone_number;
run;

I keep on getting an error that says the ID value "Home" occurs twice in the input data set.

Any help I can get would be wonderful!

Re: Help Transposing a Column with Duplicate Values

Kurt_Bremser — Wed, 24 Nov 2021 21:39:32 GMT

Add

by client_id client_name;

to the TRANSPOSE procedure.

Re: Help Transposing a Column with Duplicate Values

PaigeMiller — Wed, 24 Nov 2021 21:40:33 GMT

UNTESTED CODE

proc transpose data=test out=final;
    by client_id client_name;
    var phone_number;
    id phone_type;
run;

Since we can't program from screen captures, we request that in the future you provide data as SAS data steps (instructions) and then we can give you tested code. So, @mreynaud , please, no more data as screen captures (and also do not provide data as file attachments, as many people will not download them), data only as SAS data steps.

Re: Help Transposing a Column with Duplicate Values

MayurChaudhari — Fri, 17 Jan 2025 14:15:15 GMT

Re: Help Transposing a Column with Duplicate Values

MayurChaudhari — Fri, 17 Jan 2025 14:26:13 GMT

Please refer the solution below .

Proc transpose data=final out=a;
by phone_type;
var phone number;
run;

Proc transpose data=a out=b (drop-_name_) ;
var coll col2 col3 col4>>add all colums created;
id phone type;
run;

If any doubts plz reach out me on LinkedIn http://linkedin.com/in/mayur-chaudhari-77010821b

Re: Help Transposing a Column with Duplicate Values

mkeintz — Sat, 18 Jan 2025 19:45:46 GMT

I don't see the need for proc transpose here, since a self_merge of the dataset (merging cell phone records against corresponding home phone records) should do well:

data want;
  merge have (where=(phone_type='Cell') rename=(phone_number=cell))
        have (where=(phone_type='Home') rename=(phone_number=home) drop=text_msg_opt);
  by clientid client_name;
  drop phone_type;
run;

This program is untested in the absence of sample data in the form of a working DATA step.

But it will correctly produce the "test_message_opt_in" column, unlike the PROC TRANSPOSE solutions.