Re-organizing data: concatenating CPT codes onto one line per ID per service date

HyunJee — Tue, 06 Dec 2011 22:35:24 GMT

I have a visit file of all hospital visits for a year time period.

There are some patients who were seen more than once in this year time period.

There are repeated rows of data for each ID visit date and diagnosis codes when a different CPT code is recorded.

For example:

ID Visit Date DX1 DX2 CPT

123 10/10/10 abc cde 45876

123 10/10/10 abc cde 68594

123 10/10/10 abc cde 36543

123 11/12/10 ghj jki 89652

123 11/12/10 ghj jki 45623

123 11/12/10 ghj jki 75623

I would like it to look as follows:

ID visit date DX1 DX2 CPT1 CPT2 CPT3

123 10/10/10 abc cde 45876 68594 36543

123 11/12/10 ghj jki 89652 45623 75623

Below is the code I have been using so far:

data ou_er3;

set ou_er2;

by ID;

if first.ID then id_count = 1;

output;

id_count+1;

value = 1;run;

proc sort data=ou_er3;by id visit_date cpt id_count;run;

proc transpose data=ou_er3 out =ou_er4;

by id service_date;

id id_count;

var cpt;run;

the above code does give me results that are similar to what I am wanting. But below is what I end up with:

ID visit_date dx dx2 CPT1 CPT2 CPT3 CPT4 CPT5 CPT6

123 10/10/10 abc cde 45876 68594 36543

123 11/12/10 ghj jki 89652 45623 75623

Thank you for any help you can provide

Re-organizing data: concatenating CPT codes onto one line per ID per service date

art297 — Tue, 06 Dec 2011 22:54:38 GMT

data have;

informat visit_date mmddyy8.;

format visit_date mmddyy8.;

input ID visit_date (DX1 DX2) ($) CPT;

cards;

123 10/10/10 abc cde 45876

123 10/10/10 abc cde 68594

123 10/10/10 abc cde 36543

123 11/12/10 ghj jki 89652

123 11/12/10 ghj jki 45623

123 11/12/10 ghj jki 75623

;

proc transpose data=have

out=want (drop=_:)

prefix=CPT;

var cpt;

by id visit_date dx1 dx2;

run;

Re-organizing data: concatenating CPT codes onto one line per ID per service date

HyunJee — Wed, 07 Dec 2011 13:44:18 GMT

this worked perfectly. thank you for your help

topic Re-organizing data: concatenating CPT codes onto one line per ID per service date in SAS Procedures

Re-organizing data: concatenating CPT codes onto one line per ID per service date

Re-organizing data: concatenating CPT codes onto one line per ID per service date

Re-organizing data: concatenating CPT codes onto one line per ID per service date