Identify missing session question

[bmerry1]

I'm sorry if this may be a little basic for this forum.  I'm trying to write a program to identify when a particular subject has missed a testing session.  My objective is to identify the session(s) missed and the session is only considered to be missed if a later test has occurred.  I was able to write a program that works using a series of 'IF' statements, however it seems to me that there must be a more efficient way.  Any advice would be greatly appreciated.

An example of the basic data is as follows:

SUBJECT               Test1          Test2          Test3          Test4

1001                         74               68               82              80

1002                         65                                  99              54

1003                         77                                                   22

I would want to identify

1002 - missing Test2

1003 - missing Test2 and Test3

Thank you again for your time and consideration.

-Brian

[Haikuo]

Not sure if this is exactly what you want.

data have;

infile cards truncover;

input (SUBJECT               Test1          Test2          Test3          Test4) (:$20.);

cards;

1001                         74               68               82              80

1002                         65               .                  99              54

1003                         77               .                .                    22

1004                         21               .              35                  .

;

data want;

  set have;

   array t test1-test4;

   flag=0;

    do i=dim(t) by -1 to 1;

         flag=ifn(not missing(t(i)),1,flag);

         if missing(t(i)) then t(i)=ifc(flag=1, 'missing','finished');

       end;

       drop flag i;

run;

proc print;run;

1	1001	74	68	82	80
2	1002	65	missing	99	54
3	1003	77	missing	missing	22
4	1004	21	missing	35	finished

Haikuo

[bmerry1]

Thank you Hai.kuo, that is helpful.  Although, I think my ultimate goal is to develop an output that shows only the subjects with missing sessions and identifies which sessions are missing.

[art297]

Then you could use something like:

data have;

infile cards truncover;

input (SUBJECT               Test1          Test2          Test3          Test4) (:$20.);

cards;

1001                         74               68               82              80

1002                         65               .                  99              54

1003                         77               .                .                    22

1004                         21               .              35                  .

;

data want (keep=status);

  set have;

  array t test1-test4;

  length status $30;

  flag=0;

  j=0;

  do i=dim(t) by -1 to 1;

    if flag eq 0 and not missing(t(i)) then flag=1;

    if flag and missing(t(i)) then do;

      j+1;

      if j eq 1 then status=vname(t(i));

      else status=catx(" and ",vname(t(i)),status);

    end;

  end;

  if j then do;

    status=catx(" ",put(subject,$4.),"- missing",status);

    output;

  end;

run;

proc print noobs;

run;

[Haikuo · Accepted solution]

Similar to Art's code, while tweaking my code:

data want;

  set have;

   array t test1-test4;

   flag=0;

   length missingsession $30.;

   call missing (missingsession);

    do i=dim(t) by -1 to 1;

         flag=ifn(not missing(t(i)),1,flag);

         if missing(t(i)) then t(i)=ifc(flag=1, 'missing','finished');

       if t(i)='missing' then missingsession=catx(',',missingsession,vname(t(i)));

       end;

       if not missing(missingsession) then output;

       drop flag i;

run;

1	1002	65	missing	99	54	Test2
2	1003	77	missing	missing	22	Test3,Test2
3	1004	21	missing	35	finished	Test2

Haikuo

[Linlin]

Haikuo,

Congratulations!!! Thank you for your contribution on the Forum!  Are you going to teach your children SAS?

[Haikuo]

Thanks, LinLin.  Seriously, Haikuo, you made yourself 'Master' by spamming the forum?

My kids probably not gonna learn SAS from me, unless I am mutating to justin bieber.

Haikuo

[art297]

Hey!  Why are you picking on Canadians now?  Anyhow, wanted to offer my congratulations as well.

[Haikuo]

My bad, Art, I should have seen this coming

Seriously, Art, I think they should have another level, say "Jedi Master", for say, 10,000 points. And then we can have a party whey you get there.

Haikuo

[bmerry1]

Thank you Arthur and Haikuo for all of your assistance.  Your solutions worked perfectly.  I really appreciate your help.

Brian

[art297]

  I think the award for reaching 10,000 points should be a free, never ending license for a full copy of SAS, as well as an expense paid trip to an SGF to receive an award   That would definitely motivate more people to contribute to the forums.

[PGStats]

Congrats Haikuo. Justin Bieber, that's an idea for an avatar!

Beyond Master, there should be Demiurge!

PG

[Haikuo]

Thanks, PG. Just realize that this forum is full of Canadians! And I can't agree more to Art's suggestion on 10k mark.

Haikuo

[data_null__]

Thanks to Astonishing who pointed out that I don't even understand the problem.  After rereading the problem  I believe this may be closer.  Perhaps someone will check it.

data grades;
   input SUBJECT:$4. Test1-Test4;
   cards;
1001    74  68  82  80
1002    65  .   99  54
1003    77  .   .   22
1004    .   .   .   44
1005    65  .   .   54
1006    77  .   77   .
;;;;
   run;
proc print;
   run;
proc transpose;
   by subject;
   var test4-test1;
   run;

data;
   set;
   by subject col1 notsorted;
   retain f;
   if first.subject then f=0;
   if first.col1 and not missing(col1) then f=1;
   if missing(col1) and f then output;
   run;
proc sort;
   by subject _name_;

proc transpose prefix=Missed out=_data_(drop=_:);
   by subject;
   var _name_;
   run;
proc print;
   run;

Close enough?

data grades;
   input SUBJECT:$4. Test1-Test4;
   cards;
1001    74  68  82  80
1002    65  .   99  54
1003    77  .   22  .
;;;;
   run;
proc transpose out=_data_(where=(missing(col1)));
   by subject;
   var test:;
   run;
proc transpose prefix=Missed out=_data_(drop=_:);
   by subject;
   var _name_;
   run;
proc print;
   run;

[Astounding]

Hmmmm ... looks like it works for this batch of data.  But it would over-report if test4 were missing but test3 were not.  Anyway, here's another variation:

data want;

   set have;

   array test {4};

   length missing_session $ 30;

   do i=4 to 1 by -1 until (test{i} > .);

   end;

   if i > 1 then do i=1 to i;

     if test{i}=. then missing_session=catx(', ' , missing_session, vname(test{i}));

  end;

run;

Beauty is in the eye of the beholder??