## modifying a dataset to calculate the gap between two phases

Solved
Super Contributor
Posts: 301

# modifying a dataset to calculate the gap between two phases

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?

J.

Accepted Solutions
Solution
‎03-05-2012 02:10 AM
Super User
Posts: 10,761

## modifying a dataset to calculate the gap between two phases

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

All Replies
Solution
‎03-05-2012 02:10 AM
Super User
Posts: 10,761

## modifying a dataset to calculate the gap between two phases

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

Super Contributor
Posts: 301

## modifying a dataset to calculate the gap between two phases

It works.

Thanks Ksharp.

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

Super User
Posts: 10,761

## modifying a dataset to calculate the gap between two phases

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

New Contributor
Posts: 2

## modifying a dataset to calculate the gap between two phases

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;

🔒 This topic is solved and locked.