topic how to handle end date missing in SAS Procedures

how to handle end date missing

sam369 — Mon, 02 Apr 2012 03:08:44 GMT

Hi,

I have a couple of questions!! i have following data

subjid visit stdt endt dose dia

100 week1 10jan2011 13jan2011 10 4.5

100 week2 18jan2011 24jan2011 20 1.5

100 week3 1feb2011 6

100 week4 17feb2011 21feb2011 15 3

100 week5 12mar2011 14mar2011 10

101 week1 19jan2011 21jan2011 10 8

101 week2 25jan2011 28jan2011 4

101 week3 07feb2011 10feb2011 12 1.3

101 week4 18feb2011 14 3.1

101 week5 01mar2011 03mar2011 15

------

-----

1)i hva this kind of data some of end dates are missing. If end date is missing we need to replace it with next visit start date-1. i.e, for subject 100 at visit week3, end date is missing.so i need to replace week4 start date-1 i.e., 16feb2011

2)for dose varaible i need to replace missing doses with previous visit dose. i.e., for subject 100 at visit week3 dose is missing. week2 dose i.e., 20 is replaced in missing dose

3)for dia variable missing be replace by highest dia value for the subject. i.e., for subject 100 week5 visiit dia is missing so i need to replace 6(maximum dia) in that week5 dia,

Thanks

Sam

Re: how to handle end date missing

Haikuo — Mon, 02 Apr 2012 03:48:58 GMT

It is late in the night, my brain is not functioning well, so I don't have enough confidence on the code below, which involves 2XDOW and a little look ahead technique.

data have;

infile cards truncover;

input subjid visit :$ stdt :date9. endt :date9. dose dia;

cards;

100 week1 10jan2011 13jan2011 10 4.5

100 week2 18jan2011 24jan2011 20 1.5

100 week3 1feb2011 . . 6

100 week4 17feb2011 21feb2011 15 3

100 week5 12mar2011 14mar2011 10 .

101 week1 19jan2011 21jan2011 10 8

101 week2 25jan2011 28jan2011 . 4

101 week3 07feb2011 10feb2011 12 1.3

101 week4 18feb2011 . 14 3.1

101 week5 01mar2011 03mar2011 15 .

;

data want (drop=_:);

format stdt endt date9.;

do until (last.subjid);

set have;

by subjid notsorted;

_dia=max(_dia,dia);

end;

do until (last.subjid);

set have;

by subjid notsorted;

set have (firstobs=2 keep=stdt rename=stdt=_stdt)

have (obs=1 drop=_all_) ;

_stdt=ifn(last.subjid,.,_stdt);

endt=coalesce(endt,_stdt-1);

_dose=ifn(dose=.,_dose,dose);

dose=coalesce(dose,_dose);

dia=coalesce(dia,_dia);

output;

end;

run;

proc print;run;

Good night and Good luck!

Haikuo

how to handle end date missing

Gerd47 — Mon, 02 Apr 2012 12:47:04 GMT

Maybe you need to add a further observation to a second HAVE dataset because multiple SET-Statements stop at the EOF of the shortest dataset:

data have1;

set have end=eof;

output;

if eof then do;

stdt=.; endt=.;

output;

end;

run;

data want (drop=stdt1 _dia _dose);

format stdt endt stdt1 date9.;

do until (last.subjid);

set have;

by subjid;

_dia=max(_dia,dia);

end;

do until (last.subjid);

set have;

by subjid;

set have1 (firstobs=2 keep=stdt endt rename=(stdt=stdt1))

have (obs=1 drop=_all_) ;

if endt=. then endt=stdt1-1;

if dia=. then dia=_dia;

_dose=ifn(dose=.,_dose,dose);

if dose=. then dose=_dose;

output;

end;

run;

Best regards,

Gerd

how to handle end date missing

Haikuo — Mon, 02 Apr 2012 14:20:29 GMT

Gerd,

if you look at the following statements in my code, I have already factor the 'lost' obs in. So I will have n counts.

set have (firstobs=2 keep=stdt endt rename=(stdt=stdt1))

have (obs=1 drop=_all_) ;

While in your construct, you will end up with n+1 counts, while it may not affect results in this case, yours is a bit unnecessary.

Regards,

Haikuo

how to handle end date missing

sam369 — Mon, 02 Apr 2012 20:21:44 GMT

Hi Haikuo,

Thank you so much!!! That is the reason why i like this community!!!!!!!! being new in sas, it is very kind of opportunity to learn!!!! to learn more new things and way of think of other programmers. Thanks for letting me know do until(last.,first.). I havent understand a bit when you using by, in that you are using by varname "notsorted" what does it do?

Thanks in advance!!!!!!!

Regards

Surya

how to handle end date missing

Haikuo — Tue, 03 Apr 2012 00:05:30 GMT

Hi Surya,

First, DOW was one of the great contributions by Ian Whitlock and Paul Dorfman. Please refer to this paper for details:

http://support.sas.com/resources/papers/proceedings09/038-2009.pdf

Second, you can find look ahead details here:

http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

Third, the minor one, 'notsorted' option I used was just trying to use whatever the order in your data as is without sorting it again myself. in other words, I was a little lazy. Please search it in SAS online doc for details.

Good Luck,

Haikuo