DATA Step, Macro, Functions and more

Identify missing session question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Identify missing session question

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


Accepted Solutions
Solution
‎10-16-2012 01:13 PM
Respected Advisor
Posts: 3,156

Re: Identify missing session question

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Identify missing session question

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

Occasional Contributor
Posts: 13

Re: Identify missing session question

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.

PROC Star
Posts: 7,474

Re: Identify missing session question

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;

Solution
‎10-16-2012 01:13 PM
Respected Advisor
Posts: 3,156

Re: Identify missing session question

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

Super Contributor
Posts: 1,636

Re: Identify missing session question

Haikuo,

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

Respected Advisor
Posts: 3,156

Re: Identify missing session question

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

PROC Star
Posts: 7,474

Re: Identify missing session question

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

Respected Advisor
Posts: 3,156

Re: Identify missing session question

My bad, Art, I should have seen this coming Smiley Wink

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

Occasional Contributor
Posts: 13

Re: Identify missing session question

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

Brian

PROC Star
Posts: 7,474

Re: Identify missing session question

  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 Smiley Happy  That would definitely motivate more people to contribute to the forums.

Respected Advisor
Posts: 4,925

Re: Identify missing session question

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

 

Beyond Master, there should be Demiurge!

PG

PG
Respected Advisor
Posts: 3,156

Re: Identify missing session question

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

Respected Advisor
Posts: 3,799

Re: Identify missing session question

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=_Smiley Happy;
   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=_Smiley Happy;
   by subject;
   var _name_;
   run;
proc print;
  
run;
Super User
Posts: 5,509

Re: Identify missing session question

Posted in reply to data_null__

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??

🔒 This topic is solved and locked.

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

Discussion stats
  • 17 replies
  • 388 views
  • 3 likes
  • 7 in conversation