DATA Step, Macro, Functions and more

Issues With Proc Transpose

Reply
Regular Contributor
Posts: 164

Issues With Proc Transpose

Hello

I am using BASE SAS 9.3.

Hospitals all submit their data to a central location so there are strict file specs.  One row of data is one hospital visit so the diagnosis information is in wide format.   I created a program to extract data from this submission text file and then from that created a program to transpose the diagnosis information in long format.

For one hospital, it worked out great and I was able to transpose the different diagnosis data elements and merge to create a table.  Everything worked great.  Then I tried with a second hospital and the main table (the one with all data in wide format with one row per patient record) was successfully produced exactly the same way.  However, when I used the same transpose code, I got a COL2 added to each transpose step.  Can someone explain what causes that?

Below is an excerpt of code and dataset dx1 has COL2 added to it.  Some of the entries in COL2 mirror the column now called Diagnosis but not in all circumstances.  I realize I can just remove it from each data set as a drop = so that all fields merge correctly but I want to find out what this is happening.  All assistance greatly appreciated.

/*sort file */

proc sort data=diagnosesA out=diagnosesB;

by inst acctno chartno disdate;

run;

/*transpose the dx code */

proc transpose data=diagnosesB out=dx2(rename=col1=Diagnosis);

   by inst acctno chartno disdate;

   var diagcde1-diagcde25;

   run;

Respected Advisor
Posts: 4,920

Re: Issues With Proc Transpose

The appearance of COL2 in your transposed table means that there are two records for at least one combination of  (inst acctno chartno disdate) in diagnosesB. The 2 records x 25 variables block of data is transposed into a 25x2 block. The problem likely originates before the proc sort.

PG

PG
Regular Contributor
Posts: 164

Re: Issues With Proc Transpose

Thank you so much, PG.  I'll investigate as per your suggestion.

Ask a Question
Discussion stats
  • 2 replies
  • 172 views
  • 0 likes
  • 2 in conversation