Proc sql counting two variables

[lewis]

Hi all,

I have a question that has been bugging me for a while now.  Problem:  I am given unique patient id's (accessionnumber) and blood sample test dates with patients receiving multiple blood samples.  The question is how to count the number of samples each patient receives (No. of Samples) and how many patients are in each sample size (No. of patients).  I have no problem finding the No. of samples, but how do i count how many people are in each sample size ie. how many people have one sample, how many people have two etc.  I have included code for what i have done.  Thanks!

proc sql; select distinct count(*) as  nofpats from (

select distinct  accessionnumber from

a.labresults_ontherapy) ; quit;

[Linlin]

data have;

input id dates;

cards;

1 01/15/2013

1 01/15/2013

2 01/15/2013

2 01/15/2013

2 01/15/2013

2 01/15/2013

3 01/15/2013

4 01/15/2013

;

proc sql;

create table want

    as select size,count(*) as number

       from(select id,count(*) as size from have

    group by id,dates) group by size;

quit;

  proc print;run;

[PGStats]

proc sql;

select nbSamples, count(*)  as nbPatients

from (

     select  accessionnumber, count(*) as nbSamples

     from a.labresults_ontherapy

     group by accessionnumber)

group by nbSamples;

quit;

PG

[lewis]

Thanks for all the help guys.  I need the final output to be slightly different though.

No. of Samples        No. of Patients (ie  # ofpatients with that number of blood draws)

1                                   3

2                                   6

3                                   1

4                                   7

5                                   13

6                                   1

The code I added previously gives the left column.  The right count column for each # of samples is the problem.

[Tom · Accepted solution]

proc sql;

  select

      nofsamp "No. of Samples"

     ,count(*) as  nofpats "No. of Patients"

  from

    (select

        accessionnumber

      , count(*) as nofsamp

     from a.labresults_ontherapy

     group by 1

    )

   group by 1

  ;

quit;

[lewis]

Awesome thanks Tom!  I was really having problems with relating the two columns.  I believe PGStats reply to pretty close as well.