DATA Step, Macro, Functions and more

Transpose data to make new columns

Reply
Contributor zz
Contributor
Posts: 28

Transpose data to make new columns

I need to make field values as new column headings and would greatly appreciate help on the below:

 

Program to go through below dataset:

 

SUB CEDECODN              DATE          FATEST                                                       FASTRESC
1      INITIAL DIAGNOSIS 10/10/2015 Ann Arbor Classification                               Stage IV
1      INITIAL DIAGNOSIS 10/11/2015 B-Symptoms                                                 PRESENT
1      INITIAL DIAGNOSIS 10/12/2015 Type of Non-Hodgkin Lymphoma                INDOLENT
1      INITIAL DIAGNOSIS 10/13/2015 Histology of lymphoma                                Follicular lymphoma
1      INITIAL DIAGNOSIS 10/14/2015 Follicular lymphoma grade                          Grade 3a

 

And Format the data as follows:

 

SUBJECT INITIAL DIAGNOSIS Ann Arbor Classification B-Symptoms Type of Non-Hodgkin Lymphoma Histology of lymphoma Follicular lymphoma grade
1               10/14/2015               Stage IV                          PRESENT     INDOLENT                                   Follicular lymphoma

Grade 3a

 

Thank you very much in advance.

 

ZZ

Trusted Advisor
Posts: 1,394

Re: Transpose data to make new columns

In order to save only the last DATE I prefer use a data step instead of the TRANSPOSE procedure.

 

%let max oc = 10;  /* change to max of distinct FATEST values */

data want;

  set have;

    by SUB CEDECODN  DATE ;

        length  fatest1-fatest&oc $30 fastresc1-fastrec&oc $20;   /* addapt length to maximum as need */

        retain fatest1-fatest&oc  fastresc1-fastresc&oc  i;

        array  tstx {&oc}  fatest1-fatest&oc;

        array  rscx {&oc} fastresc1-fastresc&oc ;

 

        if first.CEDECODN   then do;

          do i=1 to &oc; tstx(i)=' '; rscx(i)=' '; end;   /* initiate by space */

          i = 1;

        end;

        

        if i le &oc then do;

             tstx(i) = FATEST  ;

             rscx(i) = FASTRESC ;

       end;

       else put 'WARNING: There are more then ' &oc ' distinct values.';

 

       if last.CEDECODN   then output;

      DROP i FATEST  FASTRESC;

run;

        

Trusted Advisor
Posts: 1,394

Re: Transpose data to make new columns

Need add a line:
if i le &oc then do;
tstx(i) = FATEST ;
rscx(i) = FASTRESC ;
i+1; /* <<<<<<< */
end;
Ask a Question
Discussion stats
  • 2 replies
  • 206 views
  • 0 likes
  • 2 in conversation