transpose

Dhana18 — Fri, 28 Jun 2019 02:10:53 GMT

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

Re: transpose

Reeza — Fri, 28 Jun 2019 02:56:30 GMT

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

topic transpose in SAS Programming

transpose

Re: transpose