Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

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

    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 Smiley Happy


Accepted Solutions
Solution
‎12-06-2011 05:54 PM
PROC Star
Posts: 7,363

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

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=_Smiley Happy

               prefix=CPT;

  var cpt;

  by id visit_date dx1 dx2;

run;

View solution in original post


All Replies
Solution
‎12-06-2011 05:54 PM
PROC Star
Posts: 7,363

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

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=_Smiley Happy

               prefix=CPT;

  var cpt;

  by id visit_date dx1 dx2;

run;

Contributor
Posts: 71

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

this worked perfectly. thank you for your help Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 128 views
  • 0 likes
  • 2 in conversation