Transpose Data Based on value

[raja777pharma]

Hello ,

I want to transpose data based on values .

My data is as per below data set:X and would like to need data as per below data set :Y

Please help to programing this one

Data : X

SUBJID	VISIT	C_STATUS	C_DATE	C_01	C_02	C_03	D_STATUS	D_DATE	D_01	D_02	D_03
10001	FIRST	YES	11-Jan-19	X			YES	11-Jan-19		X
10002	FIRST	YES	12-Feb-19	X			YES	12-Feb-19		X
10003	FIRST	YES	09-Dec-19	X			YES	09-Jan-18		X
10004	SECOND	YES	11-Jan-20	X			YES	11-Jan-19		X
10005	SECOND	YES	12-Feb-20	X			YES	12-Feb-19		X
10006	SECOND	YES	13-Mar-20	X			YES	09-Jan-18		X

Data : Y

SUBJID	VISIT	STATUS	DATE	_01_02_03_
10001	FIRST	C_YES	11-Jan-19	C_01_X
10002	FIRST	C_YES	12-Feb-19	C_01_X
10003	FIRST	C_YES	09-Dec-19	C_01_X
10004	SECOND	C_YES	11-Jan-20	C_01_X
10005	SECOND	C_YES	12-Feb-20	C_01_X
10006	SECOND	C_YES	13-Mar-20	C_01_X
10001	FIRST	D_YES	11-Jan-19	D_02_X
10002	FIRST	D_YES	12-Feb-19	D_02_X
10003	FIRST	D_YES	09-Dec-19	D_02_X
10004	SECOND	D_YES	11-Jan-20	D_02_X
10005	SECOND	D_YES	12-Feb-20	D_02_X
10006	SECOND	D_YES	13-Mar-20	D_02_X

[PaigeMiller]

data wnat;
    set have;
    status=cats('C_',c_status);
    date=c_date;
    if c_01='X' then _01_02_03='C_01_X';
    else if c_02='X' then _01_02_03='C_02_X';
    else if c_03='X' then _01_02_03='C_03_X';
    output;
    /* then you repeat the above 6 lines for D, you do the typing, I'm lazy */
run;

You have to sort the resulting data set to get the exact output data set that you asked for.

[ErikLund_Jensen]

Hi @raja777pharma 

If more than one set of observations is contained in the same record, one would normally expect all informations of a given set to be kept together, when the set is split out in individual records.

In orher words, is your example output correct, or should the second set of observations, where STATUS is taken from D_STATUS, also take DATE from D_DATE instead of C_DATE, so your last output record would have DATE = 09-Jan-18?

[hashman · Accepted solution]

@raja777pharma:

When you need to process data lists falling in the same pattern in an identical manner, arrays become your friends. For example:

data have ;                                                                                                                                                                                                                                                     
  input SUBJID $ VISIT $ C_STATUS $ C_DATE:$9. (C_01-C_03) (:$1.)                                                                                                                                                                                               
                         D_STATUS $ D_DATE:$9. (D_01-D_03) (:$1.)                                                                                                                                                                                               
  ;                                                                                                                                                                                                                                                             
  cards ;                                                                                                                                                                                                                                                       
10001  FIRST   YES  11-Jan-19  X  .  .  YES  11-Jan-19  .  X  .                                                                                                                                                                                                 
10002  FIRST   YES  12-Feb-19  X  .  .  YES  12-Feb-19  .  X  .                                                                                                                                                                                                 
10003  FIRST   YES  09-Dec-19  X  .  .  YES  09-Jan-18  .  X  .                                                                                                                                                                                                 
10004  SECOND  YES  11-Jan-20  X  .  .  YES  11-Jan-19  .  X  .                                                                                                                                                                                                 
10005  SECOND  YES  12-Feb-20  X  .  .  YES  12-Feb-19  .  X  .                                                                                                                                                                                                 
10006  SECOND  YES  13-Mar-20  X  .  .  YES  09-Jan-18  .  X  .                                                                                                                                                                                                 
;                                                                                                                                                                                                                                                               
data w (keep = subjid visit status date _01_02_03_) / view = w ;                                                                                                                                                                                                
  set have ;                                                                                                                                                                                                                                                    
  array st (i) c_status d_status ;                                                                                                                                                                                                                              
  array dt (i) c_date   d_date ;                                                                                                                                                                                                                                
  array cd [2, 3] c_0: d_0: ;                                                                                                                                                                                                                                   
  do over st ;                                                                                                                                                                                                                                                  
    pfx = char (vname (st), 1) ;                                                                                                                                                                                                                                
    status = pfx || "_" || st ;                                                                                                                                                                                                                                 
    date   = dt ;                                                                                                                                                                                                                                               
    do j = 1 to dim (cd, 2) ;                                                                                                                                                                                                                                   
      if cmiss (cd[i,j]) then continue ;                                                                                                                                                                                                                        
      _01_02_03_ = pfx || "_" || put (j, z2.) || "_" || cd[i,j] ;                                                                                                                                                                                               
      output ;                                                                                                                                                                                                                                                  
    end ;                                                                                                                                                                                                                                                       
  end ;                                                                                                                                                                                                                                                         
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
proc sort data = w out = want ;                                                                                                                                                                                                                                 
  by status subjid ;                                                                                                                                                                                                                                            
run ;                                 

Kind regards

Paul D.