BookmarkSubscribeRSS Feed
sam369
Obsidian | Level 7

Hi All,

i Have a dataset with some dates and events occurred and sample collected. But few have missing. If any record is missing sample date or event date available i need to consider that records

data have;

pid          reason                     eventdt            samdt

100         xxxy                        23aug2011      14agu2011

100          xxxy                       19dec2011      .

110           yyyy                       31jul2010       .

120            zzzzz                     14jun2010     

  120         zzzzz                      27oct2010      28oct2010

120           zzzzz                       02jan2011     17feb2011

data want;

pid          reason                     eventdt            samdt               flag

100         xxxy                        23aug2011      14agu2011         1

100          xxxy                       19dec2011      14ug2011           1

110           yyyy                       31jul2010       .                          0

120            zzzzz                     14jun2010      28oct2010           1

   120         zzzzz                      27oct2010      28oct2010          1

120           zzzzz                       02jan2011      17feb2011           1

I need to create a flag variable and sampdt is carry forward from previous available samdt

Thanks

Sam

6 REPLIES 6
sas_Forum
Calcite | Level 5

data want;

set  have;

if samdt=. then flag=0

else flag=1;

run;

Jagadishkatam
Amethyst | Level 16

Hi Sam,

Please try the below code

data have;

    infile datalines missover;

    input pid  reason $ eventdt : date9. samdt : date9.;

    format eventdt samdt date9.;

datalines;

100  xxxy    23aug2011  14aug2011

100  xxxy    19dec2011

110  yyyy    31jul2010

120  zzzzz   14jun2010

120  zzzzz   27oct2010  28oct2010

120  zzzzz   02jan2011  17feb2011

;

run;

proc sort data=have;

    by pid;

run;

data want(keep=pid reason samdt flag);

    set have(where=(samdt ne .));

    retain new;

    by pid reason;

    flag=1;

    if first.reason then output want;

run;

data want_;  

    merge have(in=a) want(in=b rename=(samdt=new_date));

    by pid reason;

    if a;

    if flag=. then flag=0;

run;

                          Obspidreason  eventdt             samdt         new_date   flag

                           1 100xxxy  23AUG201114AUG201114AUG2011  1
                           2 100xxxy  19DEC2011        .14AUG2011  1
                           3 110yyyy  31JUL2010        .        0
                           4 120zzzzz 14JUN2010        .28OCT2010  1
                           5 120zzzzz 27OCT201028OCT201028OCT2010  1
                           6 120zzzzz 02JAN201117FEB201128OCT2010  1

Thanks,

Jagadish

Thanks,
Jag
damanaulakh88
Obsidian | Level 7

Hi,

Please use this below optimized code :-

data have;

    infile datalines missover;

    input pid  reason $ eventdt : date9. samdt : date9.;

    format eventdt samdt date9.;

datalines;

100  xxxy    23aug2011  14aug2011

100  xxxy    19dec2011

110  yyyy    31jul2010

120  zzzzz   14jun2010

120  zzzzz   27oct2010  28oct2010

120  zzzzz   02jan2011  17feb2011

;

run;

data want;

set have;

format samdt_old date9.;

retain samdt_old;

if samdt ne . then do;

samdt_old=samdt;

flag=1;

end;

else flag=0;

run;

proc print data=want;

run;

Output of the above code:-

==============================================

                                Obs    pid    reason      eventdt        samdt    samdt_old    flag

                                 1     100    xxxy      23AUG2011    14AUG2011    14AUG2011      1

                                 2     100    xxxy      19DEC2011            .             14AUG2011      0

                                 3     110    yyyy      31JUL2010            .              14AUG2011      0 

                                 4     120    zzzzz     14JUN2010            .             14AUG2011      0

                                 5     120    zzzzz     27OCT2010    28OCT2010     28OCT2010      1

                                 6     120    zzzzz     02JAN2011    17FEB2011     17FEB2011      1

===============================================

/Daman

sam369
Obsidian | Level 7

Hi All,

Thanks For the Help!!!. But it not solved my issue

Jagadish: You retain the new varaible but you never used it?

Daman: You o/p is not what i am looking for. You are retaining the last available date to all the Pids. Any way thanks for the help

Thanks

Sam

Jagadishkatam
Amethyst | Level 16

Hi Sam,

Thank you for the correction, yes i was suppose to remove it but i left it.

Please don't consider the retain statement in my code. Apart from that the code will give the output you desire.

Thanks,

Jagadish

Thanks,
Jag
DBailey
Lapis Lazuli | Level 10

if your data is sorted as you need it..

data want(drop=SamDt_Prev);

set have;

retain SamDt_Prev;

flag=0;

if samdt is missing then do;

     samdt = SamDt_Prev;

     flag=1;

     end;

SamDt_Prev=samdt;

run;

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
  • 6 replies
  • 1023 views
  • 0 likes
  • 5 in conversation