Iterative Data Step Programming

Accepted Solution Solved
Reply
Contributor AD
Contributor
Posts: 31
Accepted Solution

Iterative Data Step Programming

I'm not really sure what SAS terms to use to describe what I'm trying to do, so the title of this discussion might be a little misleading, but I'll explain the problem and hopefully somebody will be able to answer the question.

I've got a data set with 4 variables: (1) Examinee_ID, (2) Exam_ID, (3) Exam_Date, and (4) Pass_Fail_Status.  An examinee can have multiple records because he or she can take a particular exam multiple times.  He or she can also take multiple exams.  He or she would have received a "P" (i.e., passing) or an "F" (failing) for each exam.  Furthermore, all patterns are possible regarding pass_fail_status over time. 

I would like to create a new variable called "Attempt" that follows the following rules:

1) If an examinee takes an exam for the first time, then Attempt for that record should be classified as "FT" (first-time).

2) If an examinee takes an exam and fails, then the examinee's NEXT Attempt shoud be classified as "RT" (re-take).

3) If an examinee takes an exam and passes, then the examinee's NEXT Attempt shoud be classified as "RC" (re-certification).

Below is an example dataset that follows those rules. For example, Examinee 2 took Exam 2 a total of 4 times.  His first attempt is classified as "FT".  His 2nd attempt is classified as "RC" because on his previous attempt he passed the exam.  His 3rd attempt is classified as a "RT" because he failed on his 2nd attempt.  His 4th attempt is classified as "RC" because he passed his 3rd attempt.  Also note that Examinee 3 took 2 different exams, taking Exam 1 twice and Exam 2 three times.

  Row   Examinee_ID  Exam_ID    Exam_Date     Pass_Fail_Status  Attempt

   1                1                  1              10/1/2011                F                    FT

   2                1                  1             11/15/2011               P                    RT

   3                2                  2               2/3/2005                 P                    FT

   4                2                  2               1/3/2008                 F                    RC

   5                2                  2               2/4/2008                 P                    RT

   6                2                  2              8/10/2012                P                    RC

   7                3                  1               6/4/2009                 P                    FT

   8                3                  1              9/10/2011                F                    RC

   9                3                  2               1/3/2008                 P                    FT

   10              3                  2              7/22/2011                F                    RC

   11              3                  2              8/10/2011                F                    RT

I want to be create the the Attempt variable using a DATA step or using PROC SQL.  Either is fine for me.  Thanks for your help!

Andy


Accepted Solutions
Solution
‎10-10-2012 04:18 PM
Super User
Posts: 5,513

Re: Iterative Data Step Programming

Assuming your data are in order:

data want;

   set have;

   by examinee_id exam_id exam_date;

   length prior_pf $ 1;

   if first.exam_id then attempt='FT';

   else if prior_pf='F' then attempt='RT';

   else attempt='RC';

   prior_pf = Pass_Fail_Status;

   retain prior_pf;

   drop prior_pf;

run;

It's untested code, but should be good to go.

View solution in original post


All Replies
Solution
‎10-10-2012 04:18 PM
Super User
Posts: 5,513

Re: Iterative Data Step Programming

Assuming your data are in order:

data want;

   set have;

   by examinee_id exam_id exam_date;

   length prior_pf $ 1;

   if first.exam_id then attempt='FT';

   else if prior_pf='F' then attempt='RT';

   else attempt='RC';

   prior_pf = Pass_Fail_Status;

   retain prior_pf;

   drop prior_pf;

run;

It's untested code, but should be good to go.

Contributor AD
Contributor
Posts: 31

Re: Iterative Data Step Programming

Posted in reply to Astounding

Sweet!  Sorted the data first, then ran this and it worked perfectly. Thanks!

Contributor AD
Contributor
Posts: 31

Re: Iterative Data Step Programming

Posted in reply to Astounding

How do I mark this answer as "correct"?  Or is that someone else's responsibility?

Super Contributor
Posts: 1,636

Re: Iterative Data Step Programming

Of course it is your responsibility. You need to log in first, then look for the GREEN button. If you don't see the GREEN button now, you need to come back later.

Contributor AD
Contributor
Posts: 31

Re: Iterative Data Step Programming

Ok, it's been over a week since Astounding's post, and I still don't see a green button.  I "liked" his response, but that and "branch" and "reply" are the only options available.

Super User
Posts: 19,822

Re: Iterative Data Step Programming

Look at the first post you made and make sure you're logged in.

Super Contributor
Posts: 1,636

Re: Iterative Data Step Programming

That is too bad.Smiley Sad Astounding must be crying bitterly.:smileycry:

PROC Star
Posts: 7,480

Re: Iterative Data Step Programming

@  : You aren't the only one who has indicated not being able to mark answers as being correct.  What brand of browser are you using and which version of that browser?

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 399 views
  • 1 like
  • 5 in conversation