modifying a dataset to calculate the gap between two phases

[michtka]

Hi guys,

i got the next records:

subjid phase  fdosedt         ldosedt         

x-1      ol     28sep03       14dec03           

x-2      db    28sep03        14dec03          

x-2      ol     26sep06        12dec06          

I would like to get something like that:

subjid    fdosedt_db ldosedt_db fdosedt_ol     ldosedt_ol     

x-1                                          28sep03       14dec03             

x-2        28sep03   14dec03      26sep06        12dec06

any help?

Thanks in advance.

J.

[Ksharp · Accepted solution]

How about

data have;
input subjid $ phase  $ fdosedt  :date9.       ldosedt   :date9.;  
format fdosedt   ldosedt date9.;
cards;
x-1      ol     28sep03       14dec03           
x-2      db    28sep03        14dec03          
x-2      ol     26sep06        12dec06      
;
run;
proc sql noprint;
 select distinct catt('have(where=(phase="',phase,'") rename=(fdosedt=fdosedt_',phase,' ldosedt=ldosedt_',phase,'))')  
  into : list separated by ' '
   from have;
quit;
data want(drop=phase);
 merge &list ;
 by subjid;
run;

Ksharp

[michtka]

It works.

Thanks Ksharp.

Could you write down the code not using the  sql procedure too?

Thanks in advance.

[Ksharp]

OK. no problem.

data have;
input subjid $ phase  $ fdosedt  :date9.       ldosedt   :date9.;  
format fdosedt   ldosedt date9.;
cards;
x-1      ol     28sep03       14dec03           
x-2      db    28sep03        14dec03          
x-2      ol     26sep06        12dec06      
;
run;
proc sort data=have(keep=phase) out=temp nodupkey;by phase;run;
data _null_;
 set temp end=last;
 if _n_ eq 1 then call execute('data want(drop=phase);merge ');
  call execute(catt('have(where=(phase="',phase,'") rename=(fdosedt=fdosedt_',phase,' ldosedt=ldosedt_',phase,'))'));
 if last then call execute(';by subjid;run;');
run;

Ksharp

[Learner_SAS]

It can be done using proc transpose too.

proc transpose data=have out=dummy;
by subjid phase ;
var ldosedt fdosedt ;
run;

data dummy;
set dummy;
VAR=catx('_',_name_,phase);
run;

proc transpose data=DUMMY out=WANT (drop=_NAME_);
by subjid;
id VAR;
run;