SAS Day 23: SQL Count

Background:

In order to present the data to the audience in a nice way, we often generate tables, figures, and listings from the existing datasets.  There are many data processing steps, such as Merge, Transformation. Among them, One of the most commonly used technique is Summarize the Object Count using SQL.

We will introduce 4 basic count techniques in SQL, **Total Count,  Unique Count,  Count By Group, Count with Conditions and show an amalgamation count sample. So we can all be SQL Count experts. :)

[caption id=“attachment_1492” align=“alignnone” width=“750”]

CopyrightFreePictures / Pixabay[/caption]

Sample Dataset

Basic SQL Syntax for Counting:

proc sql noprint;
  select count(distinct x) into: n  from data  
  group by variable1, variable2 ...
  where by condition1 and condition2...
  having condition
  order by variable2
;
quit;

1. Total Count

Suppose we simply want the Total Record Number.

proc sql noprint;
  select count(usubjid) into: pop from adtte;
quit;

%put pop

Output:

%put &pop;
27

2. Unique Count:

Suppose we would like to count the Unique patient in the dataset,
then we need to use the option Distinct.

proc sql noprint;
  select count(distinct usubjid) into: pop_unique from adtte;
quit;

%put pop_unique

Output:

%put &pop_unique;
7

3. Count By Group

From the sample dataset, we see there are two disease category: FL and MZL, suppose we want to know how many distinct patients in each disease group, we will use the option “Group By”

proc sql noprint;
  select count(distinct usubjid) into: pop_diag1 -: pop_diag2  from adtte
  group by disease ;
quit;

Output:

%put &pop_diag1 ;
5

%put &pop_diag2 ;
2

4. Count with Conditions

Suppose we are only interested in the patients that have Non-Missing Censor information, and we would like to separate the patients based on the Censor status. In order to achieve the goal, we will add the “Where” statement.

proc sql noprint;
  select count(distinct usubjid) into: pop_cnsr0 -: pop_cnsr1  from adtte
  where cnsr^=. 
  group by cnsr;
quit;

Output:

Note: some patients may have both 0, 1 censor status

%put &pop_cnsr0 ;
5

%put &pop_cnsr1 ;
5

5. Amalgamation sample

Suppose we want to select the distinct patients by Gender group with Non-missing Censor Info and Age smaller than 40.

proc sql noprint;
  select count(distinct usubjid) into: pop_sex1 -: pop_sex2  from adtte
  where cnsr^=. and age<40
  group by sex;
quit;

Output:

%put &pop_sex1;
3
%put &pop_sex2;
1

Summary:

We went over the fundamental options(Where, Group by, Distinct) for SQL select today. Yet there are many fun options like Having, Oder by, Min, Max in SQL when we generating tables.  We will explore them Next time!

Happy Studying! ✍