SAS Day 23: SQL Count
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. 🙂
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
%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
%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;
%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;
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;
%put &pop_sex1; 3 %put &pop_sex2; 1
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! ✍