transpose

[Dhana18]

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

[Reeza]

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

---