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!
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!
Add
by client_id client_name;
to the TRANSPOSE procedure.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.