Proc SQL Basic

Các lệnh cơ bản của Proc SQL

Written by Thanh Tra
Nov 14, 2024

Mục lục

• Tổng quan
• Các thao tác với một bảng
  • Mô tả bảng
  • Tạo bảng
    • Tạo bảng mới hoàn toàn
    • Tạo bảng từ bảng đã có sẵn
  • Tạo cột mới
  • Chỉnh sửa các cột
• Các thao tác với nhiều bảng
  • Join các bảng
  • Nối các bảng

Tổng quan

Proc SQL trong SAS là procedure được sử dụng nhiều nhất khi phân tích dữ liệu. Nhiệm vụ chính của ‘Proc SQL’ như sau:

Nhiệm vụ của Proc SQL

Procedure có cấu trúc tương tự như cú pháp SQL Server do đó, những người đã thành thạo ngôn ngữ SQL Server có thể sử dụng luôn Proc SQL với chú ý như sau:

• Procedure SQL bắt đầu bằng PROC SQL.
• Kết thúc bằng QUIT; (khác với data step kết thúc bằng RUN;.
• Luôn có dấu chấm phảy ; để kết thúc một query.

PROC SQL;
  /*Statements*/;
QUIT;

Cú pháp tổng quát của Proc SQL như sau (chú ý rằng, các mệnh đề phải theo thứ tự này):

PROC SQL [OPTIONS]
  CREATE TABLE [TABLE_NAME] AS
    SELECT [COLUMNS] [FUNCTION(COLUMNS)]
    FROM [TABLE_NAME]
      WHERE [CONDITIONS]
        GROUP BY [COLUMNS]
          HAVING [CONDITIONS]
            ORDER BY [COLUMNS];
QUIT;

Trong đó:

• PROC SQL [OPTIONS] thì options có thể bao gồm các tùy chọn sau:
  • NOPRINT không in kết quả của proc SQL ra màn hình SAS Report. Điều này đôi khi dẫn tới chương trình bị chậm. Do đó khuyến khích luôn luôn dùng tùy chọn này.
  • OUTOBS= số lượng quan sát trả về trong kết quả của proc SQL. Tùy chọn này tương đương với lệnh Select top 100 trong SQL Server.
  • NOERRORSTOP procedure sẽ không dừng lại nếu có một phần code bị sai. Tùy chọn này hữu ích nếu trong proc SQL có nhiều lệnh liên tiếp.
  • NUMBER hiện số thứ tự cho mỗi dòng khi output data.
  • …
• CREATE TABLE [TABLE_NAME] Lưu thông tin kết quả câu lệnh vào bảng TABLE_NAME.
• SELECT là mệnh đề được dùng nhiều nhất, có thể dùng với các vai trò sau:
  • lựa chọn các giá trị (cột, hàm với cột) từ một bảng. Ví dụ SELECT DISTINCT MAKE FROM SASHELP.CARS Chọn tất cả các giá trị khác nhau của cột MAKE.
  • lựa chọn các thông tin khác. Ví dụ SELECT MIN(MSRP) FROM TEST Chọn giá trị nhỏ nhất của cột MSRP
• FROM [TABLE_NAME] tên bản cần trích xuất dữ liệu.
• WHERE tập hợp các điều kiện, ngăn cách bởi AND/OR.
• GROUP BY thực hiện tính toán kết quả theo nhóm. Ví dụ:

  PROC SQL NOPRINT;
  SELECT CONTRACT_NO, COUNT(*) FROM T24CRD
    GROUP BY CONTRACT_NO
  QUIT;
  

  câu lệnh này sẽ tính toán số lượng (COUNT(*)) theo từng hợp đồng (CONTRACT_NO).

• ORDER BY Sắp xếp kết quả theo chiều tăng dần của một hoặc nhiều cột. Thêm DESC nếu muốn sắp xếp kết quả theo thứ tự giảm dần
• HAVING Thường dùng cùng với GROUP BY. Các điều kiện sẽ được thực hiện sau khi GROUP BY. Khác với WHERE chỉ thực hiện điều kiện trước khi GROUP BY. Ví dụ:

  PROC SQL NOPRINT;
  SELECT CONTRACT_NO, COUNT(*) FROM T24CRD
    GROUP BY CONTRACT_NO
    HAVING COUNT(*) <1000
  QUIT;
  

  kết quả của câu lệnh sẽ trả ra chỉ các hợp đồng mà có số lượng nhỏ hơn 1000.

Các thao tác với một bảng

Mô tả bảng

Để lấy thông tin các cột trong bảng, ta sử dụng DESCRIBE như sau:

PROC SQL NOPRINT;
	DESCRIBE TABLE SASHELP.CARS;
QUIT;

Kết quả nhận được như sau trong log:

Output từ mệnh đề Describe

Từ kết quả nhận được, ta có thể lấy được các thông tin hữu ích như sau:

• Tên và label của bảng.
• Định dạng của các cột (ví dụ char(40), num).
• Label của các cột (nếu có)

Kết quả trả ra tương tự như khi sử dụng PROC CONTENTS.

Tạo bảng

Để tạo bảng, ta có hai trường hợp: Tạo bảng từ bảng có sẵn và bảng mới toàn toàn.

Tạo bảng mới hoàn toàn

Để tạo bảng mới hoàn toàn, ta dùng cú pháp:

PROC SQL NOPRINT;
	CREATE TABLE TEMP00 (NAME CHAR(20), 
                      AGE NUM(8), 
                      PERCENT NUM(8) FORMAT=PERCENT10.3);
QUIT;

Với mỗi cột, cần chú ý như sau:

• Định dạng chữ (character) là CHAR(XX) và số (numeric) là NUM(XX)
• Format tương tự như data format.

Để chèn thêm dữ liệu vào bảng có sẵn, ta sử dụng INSERT INTO:

PROC SQL NOPRINT;
	INSERT INTO  TEMP00 VALUES('Tran Can Nam', 27, 0.85);
QUIT;

Nếu số lượng values trong câu lệnh trên không bằng số cột thì chương trình sẽ bảo lỗi. Để dữ liệu vào một số cột ta làm như sau:

PROC SQL NOPRINT;
	INSERT INTO  TEMP00(NAME, AGE) VALUES('Tran Duc Viet', 24);
QUIT;

Kết quả nhận được như sau:

NAME	AGE	PERCENT
Tran Can Nam	27	0.85
Tran Duc Viet	24

Lưu ý rằng các cột không được chọn sẽ nhận giá trị missing

Tạo bảng từ bảng đã có sẵn

Để tạo bảng từ bảng đã có sẵn, ta sử dụng CREATE TABLE. Ví dụ:

PROC SQL NOPRINT;
	CREATE TABLE TEMP01 AS 
		SELECT * FROM   SASHELP.CARS
			WHERE MAKE='Acura';
QUIT;

Kết quả như sau:

Make	Model	Type	Origin	DriveTrain	MSRP	…	Length
Acura	MDX	SUV	Asia	All	$36945	…	189
Acura	RSX Type S 2dr	Sedan	Asia	Front	$23820	…	172
Acura	TSX 4dr	Sedan	Asia	Front	$26990	…	183
Acura	TL 4dr	Sedan	Asia	Front	$33195	…	186
Acura	3.5 RL 4dr	Sedan	Asia	Front	$43755	…	197
Acura	3.5 RL w/Navigation 4dr	Sedan	Asia	Front	$46100	…	197
Acura	NSX coupe 2dr manual S	Sports	Asia	Rear	$89765	…	174

Mệnh đề WHERE, có thể dùng như sau:

• WHERE COL_NAME IS NULL/WHERE COL_NAME IS MISSING khi giá trị của cột là missing (NULL theo ngôn ngữ SQL).
• WHERE COL_NAME IS NOT NULL/WHERE COL_NAME IS NOT MISSING khi giá trị của cột không phải là missing.
• WHERE COL_NAME IN ("VALUE1", "VALUE2", ...) khi giá trị của cột thuộc một số giá trị.
• WHERE COL_NAME NOT IN ("VALUE1", "VALUE2", ...) khi giá trị của cột không thuộc các giá trị cho trước.
• WHERE COL_NAME BETWEEN VALUE1 AND VALUE2 khi giá trị của cột nằm giữa hai giá trị.
• WHERE COL_NAME LIKE "ABC%" khi giá trị của cột bắt đầu bằng ABC. Ví dụ các giá trị ABC123, ABC, ABCc đều thỏa mãn điều kiện này.
• WHERE COL_NAME LIKE "A_C%" khi giá trị của cột có hai ký tự A và C các nhau một ký tự. Ví dụ các giá trị AZC123, ABC, AXCc đều thỏa mãn điều kiện này.

Tạo cột mới

Cột mới có thể được tạo bởi mệnh đề AS như sau:

PROC SQL NOPRINT;
	CREATE TABLE TEMP02 AS 
		SELECT MAKE, COUNT(*) AS TOTAL, MEAN(MSRP) AS MEAN_MSRP FORMAT=DOLLAR8. LABEL="Mean MSRP"
			FROM   SASHELP.CARS
				GROUP BY MAKE
					ORDER BY MAKE;
QUIT;

Kết quả như sau:

Make	TOTAL	MEAN_MSRP
Acura	7	$42939
Audi	19	$43308
BMW	20	$43285
Buick	9	$30538
Cadillac	8	$50474

Chú ý khi tạo cột mới bằng các hàm với cột (MEAN, SUM, MIN, MAX, …), ta nên đặt format và label cột mới để dữ liệu hiển thị đẹp hơn.

Để tạo cột mới với điều kiện, ta sử dụng CASE WHEN THEN ELSE END như sau:

PROC SQL NOPRINT OUTOBS=5;
	CREATE TABLE TEMP00 AS 
		SELECT MSRP, 
			CASE 
				WHEN MSRP<=30000 THEN "Cheap"
				WHEN MSRP>30000 AND MSRP<=40000 THEN "Medium"
				WHEN MSRP>40000 THEN "Expensive"
			END 
		AS COMMENT 
			FROM SASHELP.CARS;
QUIT;

Kết quả nhận được như sau: | MSRP | COMMENT | | — | —| | $36,945 | Medium | | $23,820 | Cheap | | $26,990 | Cheap | | $33,195 | Medium | | $43,755 | Expensive |

hoặc có thể viết gọn lại như sau:

PROC SQL NOPRINT OUTOBS=5;
	CREATE TABLE TEMP00 AS 
		SELECT MSRP, 
			CASE 
				WHEN MSRP<=30000 THEN "Cheap"
				WHEN MSRP<=40000 THEN "Medium"
				ELSE "Expensive"
			END 
		AS COMMENT 
			FROM SASHELP.CARS;
QUIT;

Cách viết gọn này tương tự như ELSE-IF. Có thể viết gọn hơn nữa như sau:

PROC SQL NOPRINT OUTOBS=5;
	CREATE TABLE TEMP00 AS 
		SELECT MSRP, 
			CASE Origin
				WHEN "Asia" THEN "Chau A"
				WHEN "USA" THEN "Hoa Ky"
				WHEN "Europe" THEN "Chau Au"
			END 
		AS COMMENT 
			FROM SASHELP.CARS;
QUIT;

Với các cột mới, ta có thể sử dụng với CALCULATED. Mệnh đề này thật sự hữu ích khi so sánh với SQL Server. Ví dụ như sau

PROC SQL NOPRINT OUTOBS=5;
	CREATE TABLE TEMP00 AS 
		SELECT MSRP, 
			CASE 
				WHEN MSRP<=30000 THEN "Cheap"
				WHEN MSRP>30000 AND MSRP<=40000 THEN "Medium"
				WHEN MSRP>40000 THEN "Expensive"
			END 
		AS COMMENT,
		CAT("Extra ", (CALCULATED COMMENT)) AS COMMENT2
			FROM SASHELP.CARS;
QUIT;

Kết quả nhận được như sau:

MSRP	COMMENT	COMMENT2
$36,945	Medium	Extra Medium
$23,820	Cheap	Extra Cheap
$26,990	Cheap	Extra Cheap
$33,195	Medium	Extra Medium
$43,755	Expensive	Extra Expensive

Chỉnh sửa các cột

Để thêm các cột, ta sử dụng ALTER TABLE như sau:

PROC SQL NOPRINT;
	ALTER TABLE  TEMP02
	ADD TOTAL_MSRP NUM FORMAT=DOLLAR8.;
QUIT;

Để đổi tên cột, ta có thể dùng SELECT [COL] AS [NEW COL] hoặc cú pháp tương tự data step. Ví dụ

PROC SQL NOPRINT;
	CREATE TABLE TEMP03 AS 
		SELECT  Make, TOTAL, MEAN_MSRP, TOTAL_MSRP AS SUM_MSRP
		FROM TEMP02;
QUIT;

hoặc

PROC SQL NOPRINT;
	CREATE TABLE TEMP03(RENAME=(TOTAL_MSRP=SUM_MSRP)) AS 
		SELECT * FROM TEMP02;
QUIT;

Để chỉnh sửa cột, ta sử dụng MODIFY như sau:

PROC SQL NOPRINT;
	ALTER TABLE  TEMP02
	MODIFY MEAN_MSRP FORMAT=DOLLAR8.3;
QUIT;

Các thao tác với nhiều bảng

Join các bảng

Cơ sở dữ liệu chứa nhiều bảng khác nhau, mỗi bảng lưu trữ một loại thông tin. Khi xử lý dữ liệu cần kết hợp thông tin từ nhiều bảng.

Kết hợp các bảng

Cú pháp thường dùng là join

PROC SQL NOPRINT;
	CREATE TABLE TABLE_C AS SELECT A.COL1, B.COL2
		FROM TABLE_A AS A
		LEFT|INNER JOIN TABLE_B AS B
		ON A.ID=B.ID
		AND A.ID2=B.ID2
QUIT;

Trong đó Inner Join trả về các giá trị chung giữa hai bảng.

Inner Join

Left Join giữ nguyên bảng bên trái và lấy các giá trị chung từ bảng bên phải. Nếu không có giá trị thì sẽ đặt bằng NULL

Left Join

Để lấy các giá trị thuộc bảng bên trái mà không thuộc bảng bên phải ta sử dụng Inner Join kết hợp với where:

PROC SQL NOPRINT;
	CREATE TABLE TABLE_C AS SELECT A.COL1, B.COL2
		FROM TABLE_A AS A
		LEFT|INNER JOIN TABLE_B AS B
		ON A.ID=B.ID
		WHERE B.ID IS NULL
QUIT;

Kết quả được minh họa như sau:

Outter Join

Nối các bảng

Để nối bảng A với bảng B, ta sử dụng cú pháp như sau:

PROC SQL NOPRINT;
	CREATE TABLE TABLE_C AS SELECT * FROM A
		UNION ALL CORR
		SELECT * FROM B
QUIT;

Chú ý cú pháp UNION ALL CORR sẽ sắp xếp lại các cột có cùng tên rồi nối hai bảng lại với nhau. Nếu chỉ sử dụng UNION ALL thì hai bảng sẽ nối với nhau theo đúng thứ tự các cột.

---

Related Docs

• Proc SQL Advanced
• SAS Macros for Credit Scoring documentation
• Tổng quan
• Data Aggregation