Counting distinct tests

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 444
Accepted Solution

Counting distinct tests

so here is the data:

I am not supposed to use proc sql here and i need to count the number of distinct tests per id meaning that if i have tests taken in the same date it counts as one.

Now i can delete the duplicates and then count the tests but  how do i do the counting without deleting them?

                                                                   id    test_date           score

                                      22 29/12/1999     20

                                      22 29/12/1999     30

                                      23 01/11/1999     10

                                      23 01/11/1999     10

                                      23 08/12/1999     10

                                      23 19/04/2000     50

                                      23 20/06/2000    100

                                      23 15/08/2000    120

                                      23 10/09/2000    180

                                      23 11/09/2000    180

                                      23 12/09/2000    180

                                      24 01/08/2001    160


Accepted Solutions
Solution
‎09-29-2013 11:08 AM
Respected Advisor
Posts: 3,156

Re: Counting distinct tests

By using 2xDOW, it seems to give what you want. If not, please reply with an example of your wanted outcome.

data want;

do until (last.id);

set have;

   by id test_date;

   ct+first.test_date+(-ct*first.id);

end;

do until (last.id);

set have;

   by id test_date;

if last.test_date then output;

end;

run;

Haikuo

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: Counting distinct tests

Hope this can get you started:

data have;

input  id:$ test_date:ddmmyy10.           score;

format test_date ddmmyy10.;

cards;

                                      22 29/12/1999     20

                                      22 29/12/1999     30

                                      23 01/11/1999     10

                                      23 01/11/1999     10

                                      23 08/12/1999     10

                                      23 19/04/2000     50

                                      23 20/06/2000    100

                                      23 15/08/2000    120

                                      23 10/09/2000    180

                                      23 11/09/2000    180

                                      23 12/09/2000    180

                                      24 01/08/2001    160

;

/*If data is presorted by id, test_date*/

data want;

set have;

   by id test_date;

   ct+first.test_date+(-ct*first.id);

   if last.id;

run;

Haikuo

Super Contributor
Super Contributor
Posts: 444

Re: Counting distinct tests

Thanks Haikuo .This does count the tests but i probably forgot to mention that i also need to print each unique test date per id along with the counts Smiley Happy

Solution
‎09-29-2013 11:08 AM
Respected Advisor
Posts: 3,156

Re: Counting distinct tests

By using 2xDOW, it seems to give what you want. If not, please reply with an example of your wanted outcome.

data want;

do until (last.id);

set have;

   by id test_date;

   ct+first.test_date+(-ct*first.id);

end;

do until (last.id);

set have;

   by id test_date;

if last.test_date then output;

end;

run;

Haikuo

Super Contributor
Super Contributor
Posts: 444

Re: Counting distinct tests

thanks a lot Haikuo!

Super User
Posts: 11,343

Re: Counting distinct tests

Or using Hia.kuo's data:

proc summary data=have nway;

   class id test_date;

   output out=want (drop=_type_ rename=(_freq_=n));

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 246 views
  • 6 likes
  • 3 in conversation