Issues With Proc Transpose

shellp55 — Sun, 30 Jun 2013 00:31:19 GMT

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;

Re: Issues With Proc Transpose

PGStats — Sun, 30 Jun 2013 01:53:24 GMT

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.

Re: Issues With Proc Transpose

shellp55 — Sun, 30 Jun 2013 02:32:40 GMT

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

topic Re: Issues With Proc Transpose in SAS Programming

Issues With Proc Transpose

Re: Issues With Proc Transpose

Re: Issues With Proc Transpose