Help using Base SAS procedures

Proc sql counting two variables

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Proc sql counting two variables

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;


Accepted Solutions
Solution
‎01-18-2013 12:26 AM
Super User
Super User
Posts: 7,039

Re: Proc sql counting two variables

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;

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Proc sql counting two variables

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;

Respected Advisor
Posts: 4,919

Re: Proc sql counting two variables

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

PG
New Contributor
Posts: 3

Re: Proc sql counting two variables

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.

Solution
‎01-18-2013 12:26 AM
Super User
Super User
Posts: 7,039

Re: Proc sql counting two variables

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;

New Contributor
Posts: 3

Re: Proc sql counting two variables

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 6348 views
  • 3 likes
  • 4 in conversation