BookmarkSubscribeRSS Feed
shellp55
Quartz | Level 8

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;

2 REPLIES 2
PGStats
Opal | Level 21

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
shellp55
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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