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

[HyunJee]

    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

[art297 · Accepted solution]

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;

[HyunJee]

this worked perfectly. thank you for your help