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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

17 REPLIES 17
Haikuo
Onyx | Level 15

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
Calcite | Level 5

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
Opal | Level 21

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

Haikuo,

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

Haikuo
Onyx | Level 15

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
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

bmerry1
Calcite | Level 5

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

Brian

art297
Opal | Level 21

  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.

PGStats
Opal | Level 21

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

 

Beyond Master, there should be Demiurge!

PG

PG
Haikuo
Onyx | Level 15

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__
Jade | Level 19

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
PROC Star

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 17 replies
  • 1321 views
  • 3 likes
  • 7 in conversation