proc transpose generate one row per ID

[CathyVI]

Hello,

I am having a problem with my transpose code. This may be very simple issue but I need help.

I have a dataset with hundreds of rows and columns but I have created only the rows and columns with an issue. When I transpose the variable ischemic stroke, I want first_Is (ischemic stroke) and recurrent_Is(ischemic) to have one row by ID but am getting two rows.  Please help. Thanks in advance.

  data have (index=(ID));
  input first_IS Recurrent_IS first_HS Recurrent_HS  first_Seizure Recurrent_Seizure  ID $  D_EVENT ICD_text $ ;
  format first_IS  Recurrent_IS first_HS Recurrent_HS  first_Seizure  Recurrent_Seizure  D_EVENT date9.;
  informat first_IS  Recurrent_IS first_HS Recurrent_HS  first_Seizure  Recurrent_Seizure   D_EVENT date9.;
  datalines;  
.             .             30NOV2009        .                         .             .             034627           30NOV2009              Hemorrhagic Stroke        
.             .             .             01DEC2009                     .             .             034627          01DEC2009              Hemorrhagic Stroke        
.             .             .             02DEC2009                     .             .             034627          02DEC2009              Hemorrhagic Stroke        
.             .             .             03DEC2009                    .             .             034627           03DEC2009              Hemorrhagic Stroke        
.             .             .             04DEC2009                    .             .             034627          04DEC2009              Hemorrhagic Stroke        
.             .             .             05DEC2009                     .             .             034627           05DEC2009              Hemorrhagic Stroke        
.             .             .             14DEC2009                    .             .             034627          14DEC2009              Hemorrhagic Stroke        
.             .             .             15DEC2009                     .             .             034627          15DEC2009              Hemorrhagic Stroke        
.             .             .             16DEC2009                  .             .             034627          16DEC2009              Hemorrhagic Stroke        
.             .             .             17DEC2009                   .             .             034627           17DEC2009              Hemorrhagic Stroke        
.             .             .             21DEC2009                    .             .             034627           21DEC2009              Hemorrhagic Stroke        
.             .             .             23DEC2009                   .             .             034627          23DEC2009              Hemorrhagic Stroke        
.             .             .             04JAN2010                     .             .             034627           04JAN2010             Hemorrhagic Stroke        
.             .             .             05JAN2010                     .             .             034627           05JAN2010             Hemorrhagic Stroke        
05JAN2010         .             .             .                        .             .             034627          05JAN2010             Ischemic Stroke   
.             .             .             13JUL2010                   .             .             034627          13JUL2010            Hemorrhagic Stroke        
.             .             .             19JUL2010                      .             .             034627           19JUL2010            Hemorrhagic Stroke        
.             .             .             03AUG2010                   .             .             034627          03AUG2010              Hemorrhagic Stroke        
.             .             .             19OCT2012                  .             .             034627           19OCT2012              Hemorrhagic Stroke        
.             .             .             26NOV2012                   .             .             034627           26NOV2012              Hemorrhagic Stroke        
.             .             .             21FEB2013                    .             .             034627          21FEB2013             Hemorrhagic Stroke        
.             .             .             .                         28JUL2014          .             034627           28JUL2014            Seizure 
.             .             .             .                        .             21AUG2014        034627         21AUG2014              Seizure 
.             08JAN2010         .             .                        .             .             034627           08JAN2010             Ischemic Stroke   
.             13JAN2010         .             .                          .             .             034627           13JAN2010             Ischemic Stroke   
.             20JAN2010         .             .                        .             .             034627          20JAN2010             Ischemic Stroke   
.             01FEB2010         .             .                          .             .             034627          01FEB2010             Ischemic Stroke   
 
;

run;
 
* Ischemic stroke;
proc sort data = Have force; by ID Recurrent_IS; run;
data numberedSTROKE;
    set have;
    RETAIN R_STROKE_N;
    by ID Recurrent_IS;
    if first.ID then R_STROKE_N = 0;
    else R_STROKE_N + 1;
run;

proc sort data = numberedSTROKE; by ID R_STROKE_N; run;
proc transpose data = numberedSTROKE out = want_STROKE(DROP=_NAME_) PREFIX=R_STROKE_;
  ID R_STROKE_N;
  COPY first_IS;
  VAR Recurrent_IS;
  BY ID;
run;

[PaigeMiller]

There are two values of FIRST_IS: a missing value, and the date 05JAN2010. So you get one record for each value of FIRST_IS. You seem to want one record, but its not clear how you would get one record from this input. What would this one record contain?

I hate to see someone struggle very hard to obtain a data set that is poorly structured and hard to work with. Long data sets are preferable to wide data sets because almost every SAS PROC is designed to work on long data sets, not wide data sets. Could you explain what you would do with the data after you (successfully) transpose it? What would the next steps of the analysis or processing be?

[CathyVI]

@PaigeMiller  I want to get one record for the ID so I can calculate "pre" and "post" event- this is the next analysis step. I want the record to contain all non-missing columns like my example below.  I have multiple calculations to do for each ID. I think transpose is suppose to make it into one row? Also, getting one record per ID will help me to identify those ID that did not meet my initial criteria.

034627

05JAN2010

08JAN2010

13JAN2010

20JAN2010

01FEB2010

[PaigeMiller]

Pre and post event analysis can be done without transposing the data.

 Can you be more specific about exactly what pre and post event analyses you will be doing?