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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

View solution in original post

5 REPLIES 5
Haikuo
Onyx | Level 15

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

Gerd47
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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

sam369
Obsidian | Level 7

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

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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