You need to do a double transpose.
First transpose it to a long format and then you can go to the extra wide format.
I illustrate the concept here:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd
If you need further assistance, please post your data as a data step. Instructions on how to do so are here
I'm sure you're aware that variables can't have the same name in a SAS data set.
@Dhana18 wrote:
Yes, I want unique patient visits. May be transpose is what I need. I need to merge this data set with another data set.
The data set i am working has 25317 observations, these are tests done over 28 months. I think there are about 12681 unique patients visits.
specimen id is unique, but patient id and test date are repeated. I have created another unique id by concatenating DOB and visit date.
proc sort data=have;
by unique_id spec_id;
run;
proc transpose data=have out=transposed;
by unique_id spec_id;
id test;
var result;
run;
| UNIQUE_ID |
SPEC_COLLECT_DATE |
SPEC_ID |
LAB_PATIENTID |
SPEC_SOURCE |
TESTTYPE1 |
RESULT1 |
| 3513742828 |
4/3/2018 |
P6030025 |
21501 |
UR |
2 |
0 |
| 3513742828 |
4/3/2018 |
P6030026 |
21501 |
U |
1 |
1 |
| 1973542828 |
4/23/2017 |
P6030043 |
13561 |
UR |
2 |
1 |
| 1973542828 |
4/23/2017 |
P6030044 |
13561 |
P |
2 |
0 |
want
| UNIQUE_ID |
SPEC_COLLECT_DATE |
SURRG_SPEC_ID |
SURRG_SPEC_ID |
LAB_PATIENTID |
SPEC_SOURCE |
SPEC_SOURCE |
TESTTYPE1 |
TESTTYPE2 |
RESULT1 |
RESULT2 |
| 3513742828 |
4/3/2018 |
P6030025 |
P6030026 |
261501 |
UR |
U |
2 |
1 |
0 |
1 |
| 1973542828 |
4/23/2017 |
P6030043 |
P6030044 |
123561 |
UR |
P |
2 |
1 |
1 |
0 |
