Wide to Long Format For Data

Regular Contributor
Posts: 152

Wide to Long Format For Data


I am using SAS 9.1.  I have a hospital data set where one abstract is one row of data. 

I was able to figure out how to extract the multi-occurring fields like diagnosis or procedure using the following:

DATA have;

set mylib.data_2011;

keep chartno acctno sepdate diagcde1-diagcde25;


proc sort data=have out=have_sort;

by acctno chartno;


proc transpose data=have_sort out=need(rename=col1=dxcode);

   by acctno chartno sepdate;

   var diagcde1-diagcde25;


data Dx_List;

      set need;

      format sepdate ddmmyy10.

      where dxcode ne "";


With hospital there are 25 possible diagnoses that could be coded but not every patient will have 25 diagnoses but in the transpose step it assigns diagcde1-diagcde25 for all cases.  My last step for Dx_List is to remove the blanks and everything is working well.

But how do I introduce other variables?  For instance, I want to also include diagtyp, diagpre, and diagclust which all also have 25 possible entries. 

Also, for the code above, it creates a column called "Name of Former Variable" which lists diagcde1-diagcde25 - how do I change that field?  For instance, I would like it to show as occurrence number instead of this text field.  In other words take out the diagcde portion of the field to leave the #.

Any and all assistance greatly appreciated - thanks.

Super Contributor
Posts: 578

Wide to Long Format For Data

I may not be understanding your desired output and I certainly don't understand your data, but if you'd like to have one row per dx, you might try something like this:

data Dx_List (keep=chartno acctno sepdate diagtyp diagpre diagclust diagcde);

set mylib.data_2011;

array dtyp(25) diagtyp1 - diagtyp25;

array dpre(25) diagpre1 - diagpre25;

array dclust(25) diagclust1 - diagclust25;

array dcde(25) diagcde1 - diagcde25;

do j = 1 to 25;

     if dcde(j) >'' then do;


          diagpre = dpre(j);


          diagcde = dcde(j);





Post a Question
Discussion Stats
  • 1 reply
  • 1 like
  • 2 in conversation