亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

為了賬號安全,請及時綁定郵箱和手機立即綁定

SQL COUNT

標簽:
MySQL 大數據

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

image

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! ✍

點擊查看更多內容
TA 點贊

若覺得本文不錯,就分享一下吧!

評論

作者其他優質文章

正在加載中
  • 推薦
  • 評論
  • 收藏
  • 共同學習,寫下你的評論
感謝您的支持,我會繼續努力的~
掃碼打賞,你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃,即可進行掃碼打賞哦
今天注冊有機會得

100積分直接送

付費專欄免費學

大額優惠券免費領

立即參與 放棄機會
微信客服

購課補貼
聯系客服咨詢優惠詳情

幫助反饋 APP下載

慕課網APP
您的移動學習伙伴

公眾號

掃描二維碼
關注慕課網微信公眾號

舉報

0/150
提交
取消