Help using Base SAS procedures

how to handle end date missing

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

how to handle end date missing

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


Accepted Solutions
Solution
‎04-01-2012 11:48 PM
Respected Advisor
Posts: 3,156

Re: how to handle end date missing

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=_Smiley Happy;

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

View solution in original post


All Replies
Solution
‎04-01-2012 11:48 PM
Respected Advisor
Posts: 3,156

Re: how to handle end date missing

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=_Smiley Happy;

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

Occasional Contributor
Posts: 10

how to handle end date missing

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

Respected Advisor
Posts: 3,156

how to handle end date missing

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

Regular Contributor
Posts: 168

how to handle end date missing

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

Respected Advisor
Posts: 3,156

how to handle end date missing

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 326 views
  • 3 likes
  • 3 in conversation