BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saipriya92
Calcite | Level 5

hi i have this dataset 

Obs PTID DRUG_DT DRUG1234567891011121314151617181920

PT07837533331DEC2014ETOPOSIDE
PT07837533301JAN2015CISPLATIN
PT07837533301JAN2015ETOPOSIDE
PT07837533302JAN2015ETOPOSIDE
PT07837533321JAN2015CISPLATIN
PT07837533321JAN2015ETOPOSIDE
PT07837533322JAN2015ETOPOSIDE
PT07837533323JAN2015ETOPOSIDE
PT07837533311FEB2015CISPLATIN
PT07837533311FEB2015ETOPOSIDE
PT07837533312FEB2015ETOPOSIDE
PT07837533313FEB2015ETOPOSIDE
PT07837533304MAR2015CISPLATIN
PT07837533305MAR2015ETOPOSIDE
PT07837533306MAR2015ETOPOSIDE
PT07883721413NOV2013CARBOPLATIN
PT07883721413NOV2013ETOPOSIDE
PT07883721411DEC2013CARBOPLATIN
PT07883721411DEC2013ETOPOSIDE
PT07883721412DEC2013ETOPOSIDE

 

 

PT07883721411DEC2013ETOPOSIDE
PT07883721412DEC2013ETOPOSIDE
PT08238804418MAR2013CARBOPLATIN
PT08238804418MAR2013ETOPOSIDE
PT08238804419MAR2013ETOPOSIDE
PT08238804420MAR2013ETOPOSIDE
PT08238804408APR2013CARBOPLATIN
PT08238804408APR2013ETOPOSIDE
PT08238804409APR2013ETOPOSIDE
PT08238804410APR2013ETOPOSIDE
PT08238804401MAY2013CARBOPLATIN
PT08238804401MAY2013ETOPOSIDE
PT08238804402MAY2013ETOPOSIDE
PT08238804403MAY2013ETOPOSIDE
PT08238804422MAY2013CARBOPLATIN
PT08238804422MAY2013ETOPOSIDE
PT08238804423MAY2013ETOPOSIDE
PT08238804424MAY2013ETOPOSIDE
PT08238804419JAN2015CARBOPLATIN
PT08238804419JAN2015ETOPOSIDE
PT08238804420JAN2015ETOPOSIDE
PT08238804421JAN2015ETOPOSIDE
PT08238804409FEB2015CARBOPLATIN
PT08238804409FEB2015ETOPOSIDE
PT08238804410FEB2015ETOPOSIDE
PT08238804411FEB2015ETOPOSIDE
PT08238804403MAR2015CARBOPLATIN
PT08238804403MAR2015ETOPOSIDE
PT08238804404MAR2015ETOPOSIDE
PT08238804405MAR2015ETOPOSIDE
PT08238804424MAR2015CARBOPLATIN
PT08238804424MAR2015ETOPOSIDE
PT08238804425MAR2015ETOPOSIDE
PT08238804426MAR2015ETOPOSIDE
PT08238804402FEB2016CARBOPLATIN
PT08238804402FEB2016ETOPOSIDE
PT08238804403FEB2016ETOPOSIDE
PT08238804404FEB2016ETOPOSIDE
PT08238804423FEB2016CARBOPLATIN
PT08238804423FEB2016ETOPOSIDE
PT08238804424FEB2016ETOPOSIDE
PT08238804425FEB2016ETOPOSIDE
PT08238804415MAR2016CARBOPLATIN
PT08238804415MAR2016ETOPOSIDE
PT08238804416MAR2016ETOPOSIDE
PT08238804417MAR2016ETOPOSIDE
PT08358537717APR2012CARBOPLATIN
PT08358537717APR2012ETOPOSIDE
PT08358537718APR2012ETOPOSIDE
PT08358537719APR2012ETOPOSIDE
PT08358537709MAY2012CARBOPLATIN
PT08358537709MAY2012ETOPOSIDE
PT08358537710MAY2012ETOPOSIDE
PT08358537711MAY2012ETOPOSIDE
PT08358537730MAY2012CARBOPLATIN
PT08358537730MAY2012ETOPOSIDE
PT08358537731MAY2012ETOPOSIDE
PT08358537701JUN2012ETOPOSIDE
PT08358537720JUN2012CARBOPLATIN
PT08358537720JUN2012ETOPOSIDE
PT08358537721JUN2012ETOPOSIDE
PT08358537722JUN2012ETOPOSIDE
PT08358537725JUL2012CARBOPLATIN
PT08358537725JUL2012ETOPOSIDE
PT08358537726JUL2012ETOPOSIDE
PT08358537727JUL2012ETOPOSIDE
PT08358537715AUG2012CARBOPLATIN
PT08358537715AUG2012ETOPOSIDE
PT08358537716AUG2012ETOPOSIDE
PT08358537717AUG2012ETOPOSIDE
PT08358537703JAN2013CARBOPLATIN
PT08358537703JAN2013ETOPOSIDE
PT08358537722JAN2013CARBOPLATIN
PT08358537722JAN2013IRINOTECAN
PT08358537718FEB2013CARBOPLATIN
PT08358537718FEB2013IRINOTECAN
PT08358537725FEB2013CARBOPLATIN
PT08358537725FEB2013IRINOTECAN
PT08358537704MAR2013CARBOPLATIN
PT08358537704MAR2013IRINOTECAN
PT08358537716APR2013CARBOPLATIN
PT08358537716APR2013IRINOTECAN
PT08358537723APR2013CARBOPLATIN
PT08358537723APR2013IRINOTECAN
PT08358537730APR2013CARBOPLATIN
PT08358537730APR2013IRINOTECAN
PT08358537731MAY2013CARBOPLATIN
PT08358537731MAY2013IRINOTECAN
PT08358537707JUN2013CARBOPLATIN
PT08358537707JUN2013IRINOTECAN
PT08358537724JUN2013TOPOTECAN
PT08358537725JUN2013TOPOTECAN
PT08358537726JUN2013TOPOTECAN
PT08358537727JUN2013TOPOTECAN
PT08358537728JUN2013TOPOTECAN
PT08358537722JUL2013CARBOPLATIN
PT08358537722JUL2013IRINOTECAN
PT08358537722JUL2013TOPOTECAN
PT08358537729JUL2013TOPOTECAN
PT08358537730JUL2013TOPOTECAN
PT08358537731JUL2013TOPOTECAN
PT08358537701AUG2013TOPOTECAN
PT08358537702AUG2013TOPOTECAN
PT08358537726AUG2013TOPOTECAN
PT08358537727AUG2013TOPOTECAN
PT08358537728AUG2013TOPOTECAN
PT08358537729AUG2013TOPOTECAN
PT08358537716SEP2013DOCETAXEL
PT08358537708OCT2013DOCETAXEL
PT08358537711NOV2013GEMCITABINE
PT08358537718NOV2013GEMCITABINE
PT08358537709DEC2013GEMCITABINE
PT08358537723DEC2013GEMCITABINE
PT08358537730DEC2013GEMCITABINE
PT08358537706JAN2014GEMCITABINE

 

this is the dataset needed.

 

 Obs PTID DRUG STDT ENDDT12345678910111213141516

PT078375333Etoposide31DEC201406MAR2015
PT078375333Cisplatin01JAN201504MAR2015
PT078837214Carboplatin13NOV201311DEC2013
PT078837214Etoposide13NOV201312DEC2013
PT082388044Carboplatin18MAR201322MAY2013
PT082388044Etoposide18MAR201324MAY2013
PT082388044Carboplatin19JAN201524MAR2015
PT082388044Etoposide19JAN201526MAR2015
PT083585377Carboplatin17APR201215AUG2012
PT083585377Etoposide17APR201217AUG2012
PT083585377Etoposide03JAN201303JAN2013
PT083585377Carboplatin03JAN201322JUL2013
PT083585377Irinotecan22JAN201322JUL2013
PT083585377Topotecan24JUN201329AUG2013
PT083585377Docetaxel16SEP201308OCT2013
PT083585377Gemcitabine11NOV201306JAN2014

 

 

the condition is -

A period of continuous chemo administration starts on the first date of use and ends when there is a gap of greater than 60 days between 2 consecutive chemotherapy (of the same drug) administrations.

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

Hi @saipriya92 

 

I have modified the code as follows to take into account the condition of 60 days:

 

proc sort data=have;
	by PTID DRUG DRUG_DT;
run;

/* Identify gaps > 60 for unique combination of PTID-DRUG */
data have2;
	set have;
	
	by PTID DRUG;
	if first.DRUG then gap=0;

	format _lag date9.;
	_lag = lag(DRUG_DT);
	if DRUG_DT - _lag >= 60 and first.CUSTOMER_NR=0 then gap + 1;
run;

/* Report */
proc sql;
	select PTID,
	min(DRUG_DT) as STDT format=date9.,
	max(DRUG_DT) as ENDDT format=date9.,
	DRUG
	from have2 group by PTID, DRUG, gap;
quit;

View solution in original post

14 REPLIES 14
Shmuel
Garnet | Level 18

Try next code:

proc sort data=have; by ptid drug drug_dt;

data want;
  set have;
       by ptid drug;
           retain stdt;
            if first.drug then stdt = drug_dt;
            if last.drug then do;
               enddt = drug_dt;
              output;
           end;
     keep ptid stdt enddt;
run;
ed_sas_member
Meteorite | Level 14

Hi @saipriya92 

 

You can try this:

proc sql;
	select PTID,
	min(DRUG_DT) as STDT format=date9.,
	max(DRUG_DT) as ENDDT format=date9.,
	DRUG
	from have group by PTID, DRUG;
quit;

Output:

 

Capture d’écran 2019-11-27 à 21.28.36.png

 

saipriya92
Calcite | Level 5

HI this works thanks! 

 

But also there is one more condition and that is-

.  A period of continuous chemo administration starts on the first date of use and ends when there is a gap of greater than 60 days between 2 consecutive chemotherapy (of the same drug) administrations.

 

How do i code for this within the proc sql step ?

unison
Lapis Lazuli | Level 10

Hi @saipriya92,

 

Please either adjust your initial post if this is what you want or create a new post with this. Your initial post hints at what you want but the table you show as 'want' does not include the gap logic requirement.

 

This is not to be inflexible, but rather to match a solution to the actual ask. We want to be sure that if someone finds this post in the future, the solution will align with the initial request.

 

Thanks,

 

-unison

-unison
saipriya92
Calcite | Level 5

hi unison 

 

Thanks for the suggestion. Please see the edited post

unison
Lapis Lazuli | Level 10

Thank you,

 

See my edited post below.

 

-unison

-unison
ed_sas_member
Meteorite | Level 14

Hi @saipriya92 

 

I have modified the code as follows to take into account the condition of 60 days:

 

proc sort data=have;
	by PTID DRUG DRUG_DT;
run;

/* Identify gaps > 60 for unique combination of PTID-DRUG */
data have2;
	set have;
	
	by PTID DRUG;
	if first.DRUG then gap=0;

	format _lag date9.;
	_lag = lag(DRUG_DT);
	if DRUG_DT - _lag >= 60 and first.CUSTOMER_NR=0 then gap + 1;
run;

/* Report */
proc sql;
	select PTID,
	min(DRUG_DT) as STDT format=date9.,
	max(DRUG_DT) as ENDDT format=date9.,
	DRUG
	from have2 group by PTID, DRUG, gap;
quit;
saipriya92
Calcite | Level 5

Also what is the meaning of first.customer_nr? I have no such variable in my dataset and neither did we create any 

unison
Lapis Lazuli | Level 10

proc means would be the way to go if you're just interested in min/max dates.

 

data have;
length ptid $13. drug $11.;
input ptid $ drug_dt :date9. drug $;
format drug_dt date9.;
datalines;
PT078375333 31DEC2014 ETOPOSIDE
PT078375333 01JAN2015 CISPLATIN
PT078375333 01JAN2015 ETOPOSIDE
PT078375333 02JAN2015 ETOPOSIDE
PT078375333 21JAN2015 CISPLATIN
PT078375333 21JAN2015 ETOPOSIDE
PT078375333 22JAN2015 ETOPOSIDE
PT078375333 23JAN2015 ETOPOSIDE
PT078375333 11FEB2015 CISPLATIN
PT078375333 11FEB2015 ETOPOSIDE
PT078375333 12FEB2015 ETOPOSIDE
PT078375333 13FEB2015 ETOPOSIDE
PT078375333 04MAR2015 CISPLATIN
PT078375333 05MAR2015 ETOPOSIDE
PT078375333 06MAR2015 ETOPOSIDE
PT078837214 13NOV2013 CARBOPLATIN
PT078837214 13NOV2013 ETOPOSIDE
PT078837214 11DEC2013 CARBOPLATIN
PT078837214 11DEC2013 ETOPOSIDE
PT078837214 12DEC2013 ETOPOSIDE
PT082388044 18MAR2013 CARBOPLATIN
PT082388044 18MAR2013 ETOPOSIDE
PT082388044 19MAR2013 ETOPOSIDE
PT082388044 20MAR2013 ETOPOSIDE
PT082388044 08APR2013 CARBOPLATIN
PT082388044 08APR2013 ETOPOSIDE
PT082388044 09APR2013 ETOPOSIDE
PT082388044 10APR2013 ETOPOSIDE
PT082388044 01MAY2013 CARBOPLATIN
PT082388044 01MAY2013 ETOPOSIDE
PT082388044 02MAY2013 ETOPOSIDE
PT082388044 03MAY2013 ETOPOSIDE
PT082388044 22MAY2013 CARBOPLATIN
PT082388044 22MAY2013 ETOPOSIDE
PT082388044 23MAY2013 ETOPOSIDE
PT082388044 24MAY2013 ETOPOSIDE
PT082388044 19JAN2015 CARBOPLATIN
PT082388044 19JAN2015 ETOPOSIDE
PT082388044 20JAN2015 ETOPOSIDE
PT082388044 21JAN2015 ETOPOSIDE
PT082388044 09FEB2015 CARBOPLATIN
PT082388044 09FEB2015 ETOPOSIDE
PT082388044 10FEB2015 ETOPOSIDE
PT082388044 11FEB2015 ETOPOSIDE
PT082388044 03MAR2015 CARBOPLATIN
PT082388044 03MAR2015 ETOPOSIDE
PT082388044 04MAR2015 ETOPOSIDE
PT082388044 05MAR2015 ETOPOSIDE
PT082388044 24MAR2015 CARBOPLATIN
PT082388044 24MAR2015 ETOPOSIDE
PT082388044 25MAR2015 ETOPOSIDE
PT082388044 26MAR2015 ETOPOSIDE
PT082388044 02FEB2016 CARBOPLATIN
PT082388044 02FEB2016 ETOPOSIDE
PT082388044 03FEB2016 ETOPOSIDE
PT082388044 04FEB2016 ETOPOSIDE
PT082388044 23FEB2016 CARBOPLATIN
PT082388044 23FEB2016 ETOPOSIDE
PT082388044 24FEB2016 ETOPOSIDE
PT082388044 25FEB2016 ETOPOSIDE
PT082388044 15MAR2016 CARBOPLATIN
PT082388044 15MAR2016 ETOPOSIDE
PT082388044 16MAR2016 ETOPOSIDE
PT082388044 17MAR2016 ETOPOSIDE
PT083585377 17APR2012 CARBOPLATIN
PT083585377 17APR2012 ETOPOSIDE
PT083585377 18APR2012 ETOPOSIDE
PT083585377 19APR2012 ETOPOSIDE
PT083585377 09MAY2012 CARBOPLATIN
PT083585377 09MAY2012 ETOPOSIDE
PT083585377 10MAY2012 ETOPOSIDE
PT083585377 11MAY2012 ETOPOSIDE
PT083585377 30MAY2012 CARBOPLATIN
PT083585377 30MAY2012 ETOPOSIDE
PT083585377 31MAY2012 ETOPOSIDE
PT083585377 01JUN2012 ETOPOSIDE
PT083585377 20JUN2012 CARBOPLATIN
PT083585377 20JUN2012 ETOPOSIDE
PT083585377 21JUN2012 ETOPOSIDE
PT083585377 22JUN2012 ETOPOSIDE
PT083585377 25JUL2012 CARBOPLATIN
PT083585377 25JUL2012 ETOPOSIDE
PT083585377 26JUL2012 ETOPOSIDE
PT083585377 27JUL2012 ETOPOSIDE
PT083585377 15AUG2012 CARBOPLATIN
PT083585377 15AUG2012 ETOPOSIDE
PT083585377 16AUG2012 ETOPOSIDE
PT083585377 17AUG2012 ETOPOSIDE
PT083585377 03JAN2013 CARBOPLATIN
PT083585377 03JAN2013 ETOPOSIDE
PT083585377 22JAN2013 CARBOPLATIN
PT083585377 22JAN2013 IRINOTECAN
PT083585377 18FEB2013 CARBOPLATIN
PT083585377 18FEB2013 IRINOTECAN
PT083585377 25FEB2013 CARBOPLATIN
PT083585377 25FEB2013 IRINOTECAN
PT083585377 04MAR2013 CARBOPLATIN
PT083585377 04MAR2013 IRINOTECAN
PT083585377 16APR2013 CARBOPLATIN
PT083585377 16APR2013 IRINOTECAN
PT083585377 23APR2013 CARBOPLATIN
PT083585377 23APR2013 IRINOTECAN
PT083585377 30APR2013 CARBOPLATIN
PT083585377 30APR2013 IRINOTECAN
PT083585377 31MAY2013 CARBOPLATIN
PT083585377 31MAY2013 IRINOTECAN
PT083585377 07JUN2013 CARBOPLATIN
PT083585377 07JUN2013 IRINOTECAN
PT083585377 24JUN2013 TOPOTECAN
PT083585377 25JUN2013 TOPOTECAN
PT083585377 26JUN2013 TOPOTECAN
PT083585377 27JUN2013 TOPOTECAN
PT083585377 28JUN2013 TOPOTECAN
PT083585377 22JUL2013 CARBOPLATIN
PT083585377 22JUL2013 IRINOTECAN
PT083585377 22JUL2013 TOPOTECAN
PT083585377 29JUL2013 TOPOTECAN
PT083585377 30JUL2013 TOPOTECAN
PT083585377 31JUL2013 TOPOTECAN
PT083585377 01AUG2013 TOPOTECAN
PT083585377 02AUG2013 TOPOTECAN
PT083585377 26AUG2013 TOPOTECAN
PT083585377 27AUG2013 TOPOTECAN
PT083585377 28AUG2013 TOPOTECAN
PT083585377 29AUG2013 TOPOTECAN
PT083585377 16SEP2013 DOCETAXEL
PT083585377 08OCT2013 DOCETAXEL
PT083585377 11NOV2013 GEMCITABINE
PT083585377 18NOV2013 GEMCITABINE
PT083585377 09DEC2013 GEMCITABINE
PT083585377 23DEC2013 GEMCITABINE
PT083585377 30DEC2013 GEMCITABINE
PT083585377 06JAN2014 GEMCITABINE
;
run;

proc sort data=have; by ptid drug drug_dt; run;

data flag_data;
set have;
by ptid drug;
retain flag 0;
if first.drug or dif(drug_dt)>60 then flag=1;
else flag+1;
run;

data flag_data2;
set flag_data;
by ptid drug;
if first.drug or last.drug;
drop flag;
run;

proc means nway missing noprint data=flag_data2;
class ptid drug;
output out=want(drop=_type_ _freq_) 
	min(drug_dt)=start_dt max(drug_dt)=end_dt;
run;

-unison

-unison
novinosrin
Tourmaline | Level 20

Hi @unison   Very neat!

saipriya92
Calcite | Level 5

Hi unison, 

 

Can I change this code that you priovided here :

 

proc means nway missing noprint data=flag_data2;
class ptid drug;
output out=want(drop=_type_ _freq_) 
	min(drug_dt)=start_dt max(drug_dt)=end_dt;
run;

To using proc sql ? 

 

 

proc sql;
	create table step1_final as 
	select ptid, drug, min(drug_dt) as stdt format date9., max(drug_dt) as enddt format date9. 
	from step1_1 
	group by ptid, drug ;
quit ;

If no, what is the difference between these two ?

 

Thanks in advance

unison
Lapis Lazuli | Level 10

Hi @saipriya92,

 

Looks like they are identical!

 

proc means nway missing noprint data=flag_data2;
class ptid drug;
output out=opt1(drop=_type_ _freq_) 
	min(drug_dt)=stdt max(drug_dt)=enddt;
run;

proc sql;
	create table opt2 as 
	select ptid, drug, min(drug_dt) as stdt format date9., max(drug_dt) as enddt format date9. 
	from flag_data2 
	group by ptid, drug ;
quit ;

proc compare base=opt1 compare=opt2; run;

-unison

-unison
PGStats
Opal | Level 21

I don't think SQL is well suited for this task. My take:

 

proc sort data=have; by ptid drug drug_dt; run;

data want;
do until(last.drug);
	set have; by ptid drug;
	if missing(startDt) then startDt = drug_dt;
	else if intck("day", endDt, drug_Dt) > 60 then do;
		output;
		call missing(startDt);
		end;
	endDt = drug_Dt;
	end;
output;
keep ptid drug startDt endDt;
run;
PG
mkeintz
PROC Star

As @PGStats said, this is not a problem well-suited to an SQL solution.  I would suggest code similar to @PGStats,s except this code uses a "look-ahead" technique to see if the upcoming gap is >60.  It is what I would term a "SET/BY + MERGE/firstobs" solution:

 

data have;
length ptid $13. drug $11.;
input ptid $ drug_dt :date9. drug $;
format drug_dt date9.;
datalines;
PT078375333 31DEC2014 ETOPOSIDE
PT078375333 01JAN2015 CISPLATIN
PT078375333 01JAN2015 ETOPOSIDE
PT078375333 02JAN2015 ETOPOSIDE
PT078375333 21JAN2015 CISPLATIN
PT078375333 21JAN2015 ETOPOSIDE
PT078375333 22JAN2015 ETOPOSIDE
PT078375333 23JAN2015 ETOPOSIDE
PT078375333 11FEB2015 CISPLATIN
PT078375333 11FEB2015 ETOPOSIDE
PT078375333 12FEB2015 ETOPOSIDE
PT078375333 13FEB2015 ETOPOSIDE
PT078375333 04MAR2015 CISPLATIN
PT078375333 05MAR2015 ETOPOSIDE
PT078375333 06MAR2015 ETOPOSIDE
PT078837214 13NOV2013 CARBOPLATIN
PT078837214 13NOV2013 ETOPOSIDE
PT078837214 11DEC2013 CARBOPLATIN
PT078837214 11DEC2013 ETOPOSIDE
PT078837214 12DEC2013 ETOPOSIDE
PT082388044 18MAR2013 CARBOPLATIN
PT082388044 18MAR2013 ETOPOSIDE
PT082388044 19MAR2013 ETOPOSIDE
PT082388044 20MAR2013 ETOPOSIDE
PT082388044 08APR2013 CARBOPLATIN
PT082388044 08APR2013 ETOPOSIDE
PT082388044 09APR2013 ETOPOSIDE
PT082388044 10APR2013 ETOPOSIDE
PT082388044 01MAY2013 CARBOPLATIN
PT082388044 01MAY2013 ETOPOSIDE
PT082388044 02MAY2013 ETOPOSIDE
PT082388044 03MAY2013 ETOPOSIDE
PT082388044 22MAY2013 CARBOPLATIN
PT082388044 22MAY2013 ETOPOSIDE
PT082388044 23MAY2013 ETOPOSIDE
PT082388044 24MAY2013 ETOPOSIDE
PT082388044 19JAN2015 CARBOPLATIN
PT082388044 19JAN2015 ETOPOSIDE
PT082388044 20JAN2015 ETOPOSIDE
PT082388044 21JAN2015 ETOPOSIDE
PT082388044 09FEB2015 CARBOPLATIN
PT082388044 09FEB2015 ETOPOSIDE
PT082388044 10FEB2015 ETOPOSIDE
PT082388044 11FEB2015 ETOPOSIDE
PT082388044 03MAR2015 CARBOPLATIN
PT082388044 03MAR2015 ETOPOSIDE
PT082388044 04MAR2015 ETOPOSIDE
PT082388044 05MAR2015 ETOPOSIDE
PT082388044 24MAR2015 CARBOPLATIN
PT082388044 24MAR2015 ETOPOSIDE
PT082388044 25MAR2015 ETOPOSIDE
PT082388044 26MAR2015 ETOPOSIDE
PT082388044 02FEB2016 CARBOPLATIN
PT082388044 02FEB2016 ETOPOSIDE
PT082388044 03FEB2016 ETOPOSIDE
PT082388044 04FEB2016 ETOPOSIDE
PT082388044 23FEB2016 CARBOPLATIN
PT082388044 23FEB2016 ETOPOSIDE
PT082388044 24FEB2016 ETOPOSIDE
PT082388044 25FEB2016 ETOPOSIDE
PT082388044 15MAR2016 CARBOPLATIN
PT082388044 15MAR2016 ETOPOSIDE
PT082388044 16MAR2016 ETOPOSIDE
PT082388044 17MAR2016 ETOPOSIDE
PT083585377 17APR2012 CARBOPLATIN
PT083585377 17APR2012 ETOPOSIDE
PT083585377 18APR2012 ETOPOSIDE
PT083585377 19APR2012 ETOPOSIDE
PT083585377 09MAY2012 CARBOPLATIN
PT083585377 09MAY2012 ETOPOSIDE
PT083585377 10MAY2012 ETOPOSIDE
PT083585377 11MAY2012 ETOPOSIDE
PT083585377 30MAY2012 CARBOPLATIN
PT083585377 30MAY2012 ETOPOSIDE
PT083585377 31MAY2012 ETOPOSIDE
PT083585377 01JUN2012 ETOPOSIDE
PT083585377 20JUN2012 CARBOPLATIN
PT083585377 20JUN2012 ETOPOSIDE
PT083585377 21JUN2012 ETOPOSIDE
PT083585377 22JUN2012 ETOPOSIDE
PT083585377 25JUL2012 CARBOPLATIN
PT083585377 25JUL2012 ETOPOSIDE
PT083585377 26JUL2012 ETOPOSIDE
PT083585377 27JUL2012 ETOPOSIDE
PT083585377 15AUG2012 CARBOPLATIN
PT083585377 15AUG2012 ETOPOSIDE
PT083585377 16AUG2012 ETOPOSIDE
PT083585377 17AUG2012 ETOPOSIDE
PT083585377 03JAN2013 CARBOPLATIN
PT083585377 03JAN2013 ETOPOSIDE
PT083585377 22JAN2013 CARBOPLATIN
PT083585377 22JAN2013 IRINOTECAN
PT083585377 18FEB2013 CARBOPLATIN
PT083585377 18FEB2013 IRINOTECAN
PT083585377 25FEB2013 CARBOPLATIN
PT083585377 25FEB2013 IRINOTECAN
PT083585377 04MAR2013 CARBOPLATIN
PT083585377 04MAR2013 IRINOTECAN
PT083585377 16APR2013 CARBOPLATIN
PT083585377 16APR2013 IRINOTECAN
PT083585377 23APR2013 CARBOPLATIN
PT083585377 23APR2013 IRINOTECAN
PT083585377 30APR2013 CARBOPLATIN
PT083585377 30APR2013 IRINOTECAN
PT083585377 31MAY2013 CARBOPLATIN
PT083585377 31MAY2013 IRINOTECAN
PT083585377 07JUN2013 CARBOPLATIN
PT083585377 07JUN2013 IRINOTECAN
PT083585377 24JUN2013 TOPOTECAN
PT083585377 25JUN2013 TOPOTECAN
PT083585377 26JUN2013 TOPOTECAN
PT083585377 27JUN2013 TOPOTECAN
PT083585377 28JUN2013 TOPOTECAN
PT083585377 22JUL2013 CARBOPLATIN
PT083585377 22JUL2013 IRINOTECAN
PT083585377 22JUL2013 TOPOTECAN
PT083585377 29JUL2013 TOPOTECAN
PT083585377 30JUL2013 TOPOTECAN
PT083585377 31JUL2013 TOPOTECAN
PT083585377 01AUG2013 TOPOTECAN
PT083585377 02AUG2013 TOPOTECAN
PT083585377 26AUG2013 TOPOTECAN
PT083585377 27AUG2013 TOPOTECAN
PT083585377 28AUG2013 TOPOTECAN
PT083585377 29AUG2013 TOPOTECAN
PT083585377 16SEP2013 DOCETAXEL
PT083585377 08OCT2013 DOCETAXEL
PT083585377 11NOV2013 GEMCITABINE
PT083585377 18NOV2013 GEMCITABINE
PT083585377 09DEC2013 GEMCITABINE
PT083585377 23DEC2013 GEMCITABINE
PT083585377 30DEC2013 GEMCITABINE
PT083585377 06JAN2014 GEMCITABINE
;
run;

proc sort data=have; by ptid drug drug_dt; run;


data want (drop=nxt_: drug_dt);
  do until (last.drug or nxt_drug_dt>drug_dt+60);
    set have (keep=ptid drug);
    by ptid drug ;
    merge have 
          have (firstobs=2 keep=drug_dt rename=(drug_dt=nxt_drug_dt));
    if startdt=. then startdt=drug_dt;
  end;
  enddt=drug_dt;
  format startdt enddt date9. ;
run;

 

  1. The set and by statements are there merely to provide a test for the beginning or end of a ptid/drug group.
  2. But there is another criterion for outputting an observation, namely if the current drug_dt is more the 60 days prior to the upcoming drug_dt  (renamed to nxt_drug_dt above).  I can get that variable by using a MERGE statement in which one of the merged input has "firstobs=2" and the rename drug_dt variable).

 

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 1796 views
  • 2 likes
  • 7 in conversation