topic modifying a dataset to calculate the gap between two phases in SAS Procedures

modifying a dataset to calculate the gap between two phases

michtka — Fri, 02 Mar 2012 18:06:50 GMT

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.

modifying a dataset to calculate the gap between two phases

Ksharp — Mon, 05 Mar 2012 07:10:46 GMT

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

modifying a dataset to calculate the gap between two phases

michtka — Mon, 05 Mar 2012 12:23:53 GMT

It works.

Thanks Ksharp.

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

Thanks in advance.

modifying a dataset to calculate the gap between two phases

Ksharp — Tue, 06 Mar 2012 04:45:45 GMT

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

modifying a dataset to calculate the gap between two phases

Learner_SAS — Tue, 06 Mar 2012 12:16:08 GMT

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;