topic Re: Proc sql counting two variables in SAS Procedures

Proc sql counting two variables

lewis — Fri, 18 Jan 2013 01:40:22 GMT

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;

Re: Proc sql counting two variables

Linlin — Fri, 18 Jan 2013 02:15:20 GMT

data have;

input id dates;

cards;

1 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;

Re: Proc sql counting two variables

PGStats — Fri, 18 Jan 2013 02:16:06 GMT

proc sql;

select nbSamples, count(*) as nbPatients

from (

select accessionnumber, count(*) as nbSamples

from a.labresults_ontherapy

group by accessionnumber)

group by nbSamples;

quit;

Re: Proc sql counting two variables

lewis — Fri, 18 Jan 2013 04:27:19 GMT

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.

Re: Proc sql counting two variables

Tom — Fri, 18 Jan 2013 05:26:47 GMT

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;

Re: Proc sql counting two variables

lewis — Fri, 18 Jan 2013 14:39:13 GMT

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