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
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
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
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
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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.