當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

SAS MACRO

發布時間：2023/12/20 编程问答 27 豆豆

生活随笔收集整理的這篇文章主要介紹了 SAS MACRO 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

提示：文章寫完后，目錄可以自動生成，如何生成可參考右邊的幫助文檔

文章目錄

**LESSON 1**
1.1: SAS macro是什么
1.2: Macro Basic info
- 1. macro trigger
- 2. 創建macro variable的三種方法
- - 1) %LET Statement
  - 2) CALL SYMPUTX routine in the DATA step
  - 3) INTO clause in the SQL procedure
- 3. Macro Variable 存儲位置等
- - 1）local and global tables
  - 2）%PUT Statement
1.3: 兩個Macro工具
- 1. Macro Variables
- 2. Macro Programs
- - 1）define macro program
  - 2）call/invoke macro program
  - 3）Macro Program Parameters
  - 4）SAS system options
1.4: Two valid set of statement in macro program
- 1）iterative statement %DO and %END
- 2）conditional processing %IF-THEN-DO
1.5: not straightforward basics
- 1) Macro Variable Reference with Period Delimiter
- 2) Multiple Ampersands for Indirect Reference
- 3) Compilation of Macro Quoting Functions
- 4) Execution Macro Quoting Functions
- 5) Macro IN Operator
**LESSON 2**
2.1: SAS I/O Functions
**LESSON 3**
3: Getting the most out of SAS macro and SQL
- - 十個example
  - - 1) Scenario 1
    - 2) Scenario 2
    - 3) Scenario 3
    - 4) Scenario 4
    - 5) Scenario 5
    - 6) Scenario 6
    - 7) Scenario 7
    - 8) Scenario 8
    - 9) Scenario 9
    - 10) Scenario 10
**LESSON 4**
4: SQL 基本語句
- 4.1 查詢
- - 4.1.1 單表查詢
- 4.2 連接查詢
- - 4.2.1 外連接查詢
  - 4.2.2 復合條件連接查詢
  - 4.2.3 自身連接查詢
  - 4.2.4 嵌套查詢
  - 4.2.5 內連接inner join
**LESSON 5**
5: SAS常用函數
- Numeric functions
**LESSON 6**
6: PROC SQL Programming Techniques for SAS Users and Programmers(接著上面sql的例子)
- Chapter 1
- - 1.1 Inner Join Construct
**LESSON 7—SQL 題目練習**
SAS BY TECHIEVENTURES
- 1. SAS PROC PRINT
- - Part 1:
  - Part 2：
  - Part 3：ID STATEMENT & BY STATEMENT
- 2. SAS DATE FUNCTION
- - Part 1: Date, Time, Today, Datetime
  - Part 2: DATEPART, TIMEPART
  - Part 3: Date, Week, Month, QTR, Year
  - Part 4: WEEKDAY, INTCK,INTNX,YRDIF
- 3. SAS CHARACTER FUNCTION
- - Part 1: UPCASE, LOWCASE, PROPCASE

提示：以下是本篇文章正文內容，下面案例可供參考

LESSON 1

參考教程
https://www.youtube.com/watch?v=P1LZ2_sjvtc&t=1369s

1.1: SAS macro是什么

SAS macro — 宏，是SAS的一種強大的編程特性。利用SAS macro，我們可以有效避免代碼的重復冗雜，macro可以反復使用，抓取動態變化的值等等。

1.2: Macro Basic info

1. macro trigger

SAS macro有兩個macro trigger，一個是&，另一個是%。

Ampersand - &：macro variable trigger
percent sign - %：macro program trigger

Note：如果Macro trigger表示的是string，需要加雙引號。

where month="&month" and year=&year;

2. 創建macro variable的三種方法

1) %LET Statement

基本用法：

%LET macro-variable = value;

例子：

%LET YearCurr = 2020; %let YearCurr = %sysfunc(year(%sysfunc(today()))); %let YearPrev = %eval(&YearCurr - 1);

2) CALL SYMPUTX routine in the DATA step

CALL SYMPUTX(macro-variable, value); data _null_;YC = year(today());YP = YC - 1;call symputx('YC', YC); #name is the first, value is the secondcall symputx('YP', YP); run; %put &YC &YP;

3) INTO clause in the SQL procedure

SELECT value INTO :macro-variable; data work.prdsale;set sashelp.prdsale;year=year+25; run; * One macro variable; * 讓YearMax表示用sql選取出來的最大的年份; proc sql;select put(max(year),4.)into :YearMaxfrom work.prdsale; quit;

3. Macro Variable 存儲位置等

1）local and global tables

local symbol tables
global symbol tables

2）%PUT Statement

/* %PUT statement */ %put _all_; %put _user;%put Today is &YearCurr; /* display macro variable in log.

1.3: 兩個Macro工具

1. Macro Variables

Macro variable包括了SAS program中可以重復使用的值，可以小范圍的用于text substitution。

2. Macro Programs

1）define macro program

%MACRO 和 %MEND Statement

%macro mydttime;%let mydt=%sysfunc(date(),worddate.);%let mytime=%sysfunc(time(),timeampm.);%put The current date is &mydt..;%put The current time is &mytime..;%mend mydttime;

2）call/invoke macro program

%mydttime /* 這里不需要分號

Result:

%mydttime The current date is April 13, 2022. The current time is 9:37:48 AM.

3）Macro Program Parameters

position parameters
keyword paramaters

/* Position parameters -- such as function in R and Python*/ %macro cars(mke, cyl); proc print data=sashelp.cars;where make="&mke" and cylinders=&cyl;title "&mke &cyl Cylinders"; run; %mend cars;%cars(Ford, 8)/* keyword parameters */ %macro cars(mke=Audi, cyl=6); proc print data=sashelp.cars;where make="&mke" and cylinders=&cyl;title "&mke &cyl Cylinders"; run; %mend cars;%cars(cyl=4) /* overriding the default value of cylinders, mke is still equal to Audi.

4）SAS system options

MLOGIC
MPRINT
SYMBOLGEN

options mlogic mprint symbolgen; %carmake(Chevrolet)

1.4: Two valid set of statement in macro program

1）iterative statement %DO and %END

%macro allcars; %do i=1 %to 38;proc print data=sashelp.cars noobs;where make="%scan(&makes, &i, ~)";title "Make %scan(&makes, &i, ~)";run; %end; %mend allcars;%allcars

2）conditional processing %IF-THEN-DO

%macro carmake(mke); %if &mke=Chevrolet or &mke=Toyota %then %do;proc freq data=sashelp.cars;where make="&mke";tables cylinders;title "Make &mke";run; %end; %else %do;proc print data=sashelp.cars;where make="&mke";title "Make &mke";run; %end; %mend carmake;%carmake(Acura) %carmake(Toyota) options mprint; %carmake(Aura);

1.5: not straightforward basics

1) Macro Variable Reference with Period Delimiter

%let month=MAR; %let year=2020;%put &month&year; *MAR2020;%put &month&yeardata; *WARNING: Apparent symbolic reference YEARDATA not resolved.;%put &month&year.data; *MAR2020data;%put It is &month of &year.; %put It is &month of &year..;%let lib=sashelp; %let dsn=class;proc print data=&lib.&dsn; *ERROR: File WORK.SASHELPCLASS.DATA does not exist.; run; proc print data=&lib..&dsn; *語法正確沒有報錯，注意和上面的區別; run;

2) Multiple Ampersands for Indirect Reference

%let year=2020; %let var=location; %let city2020=Washington; %let location2020=District of Columbia;%put &city&year; *WARNING: Apparent symbolic reference CITY not resolved.; %put &&city&year; *Washington; %put &&&var&year; *District of Columbia;

3) Compilation of Macro Quoting Functions

%let step=data new%str(;) x=1%str(;); &step; run;*got the same answer; %let step=%str(data new;x=1;); &step run;%let title=Employee%str(%')s Report; %put &title;*%str cannot mask or cover up the meaning of macro trigger(&), but %nrstr can; %let company=R%nrstr(&)D as of &sysdate9; %put &company;

4) Execution Macro Quoting Functions

/*Mask Mnemonic Operator company是前面創建的macro variable，這里調用了;*/ %put &company;/*Incorrect*/ *ne: not equal; %macro check(company); %if ABC ne &company %then %do;%put Not a match; %end; %else %put Match; %mend check; %check(OR Insurance);/*correct*/ %macro check(company); %if ABC ne %superq(company) %then %do;%put ERROR- Not a match; %end; %else %put ERROR- Match; %mend check; %check(OR Insurance); %check(ABC);/*Mask ampersand*//*Incorrect*/ data _null_;call symputx('company', 'Smith&Johns'); run; %let newcmpy=&company; %put The new company is &newcmpy;/*correct*/ data _null_;call symputx('company', 'Smith&Johns'); run; %let newcmpy=%superq(company); %put The new company is &newcmpy;

5) Macro IN Operator

/*Default Delimiter*/ %macro carsubset(type) / minoperator; %if &type in SUV Truck Wagon %then %do;proc print data=sashelp.cars;where Type="&type";title "Report for &type";run; %end; %if &type in (Sedan Sports) %then %do;proc freq data=sashelp.cars;where Type="&type";table Make;title "Report for &type";run; %end; %mend carsubset;%carsubset(SUV); %carsubset(Truck); %carsubset(Sedan); %carsubset(Sports);*Specified delimiter; %macro carsubset(type) / minoperatormindelimiter=','; %if &type in SUV, Truck, Wagon %then %do;proc print data=sashelp.cars;where Type="&type";tables Make;run; %end; %mend carsubset;%carsubset(SUV); %carsubset(Truck); %carsubset(Wagon); %carsubset(Sedan); %carsubset(Sports);

LESSON 2

2.1: SAS I/O Functions

SAS中的I/O function可以用于檢索 metadata information

exist

proc contents data=sashelp.class; /*顯示出sashelp.class的info*/ %macro data1(dsn); %if %sysfunc(exist(&dsn))=1 %then %do;proc print data=&dsn(obs=10);run; %end; %else %put Not a Valid data set.; %mend data1; %data1(sashelp.class)

OPEN/ATTERN/CLOSE Functions
ATTRN Function returns the value of a numeric attribute for a SAS data set.

%macro data2(dsn); %let dsid=%sysfunc(open(&dsn)); %if &dsid ne 0 %then %do;%let numobs=%sysfunc(attrn(&dsid, nlobs));%let numvars=%sysfunc(attrn(&dsid, nvars));%let rc=%sysfunc(close(&dsid));proc print data=&dsn(obs=10);title1 "Data set &dsn with &numobs Observations and &numvars Variables";title2 "Ten observations in Report";run; %end; %else %put Not a Valid data set.; %mend data2; %macro data3(dsn); %if %sysfunc(exist(&dsn))=1 %then %do;%let dsid=%sysfunc(open(&dsn));%let numvar=%sysfunc(attrn(&dsid, nvars));%let char=0;%do i=1 %to &numvar;%if %sysfunc(vartype(&dsid, &i))=C %then %do;%let char=%eval(&char+1);%end;%end;%let rc=%sysfunc(close(&dsid));proc print data=&dsn(obs=10);title1 "Data set &dsn with &char Character Variables";title2 "Ten observations in Report";var _character_; /*如果只想顯示character形式的變量*/run; %end; %else %put Not a Valid data set.; %mend data3;%data3(sashelp.class)

External File Functions

%macro runpgms(path); filename mydir "&path"; %let dirid=%sysfunc(dopen(mydir)); %let dirnum=%sysfunc(dnum(&dirid));%do i=1 %to &dirnum;%include "&path\%sysfunc(dread(&dirid, &i))" / source2; %end;%let rc=%sysfunc(dclose(&dirid)); filename mydir clear; %mend runpgms;

LESSON 3

3: Getting the most out of SAS macro and SQL

https://www.youtube.com/watch?v=sM3sidYPT-o`

十個example

1) Scenario 1

/* Scenario 1 */ proc sql;title1 "Chevrolet";select model, type, msrp from sashelp.cars where make="Chevrolet" order by model; quit;

2) Scenario 2

/* Scenario 2 */ options mcompilenote=noautocall;%macro carfinder(car);*position parameter;proc sql;title1 "&car";select model, type, msrp from sashelp.cars where make="&car" order by model;*order by 1也可以;quit;%mend carfinder;%carfinder(Chevrolet) %carfinder(BMW) %carfinder(Bmw); *case sensitive;

3) Scenario 3

/* Scenario 3 */ %macro carfinder(car);proc sql noprint;select mean(msrp) format=dollar7. into: mean_msrp from sashelp.cars where make="&car";reset print;title1 "&car";title2 "Average MSRP: &mean_msrp";select model, type, msrp from sashelp.cars where make="&car" order by model;quit;%mend carfinder;%carfinder(Chevrolet);

4) Scenario 4

/* Scenario 4 */ proc sql noprint; select distinct type into :list separated by " " from sashelp.cars; quit; %put ERROR- &list; *標紅，- or : 都可以;

5) Scenario 5

/* Scenario 5 */ %macro charlist(var=,dsn=); *make them null values;%global list; *make macro variable into global variable;proc sql noprint; select distinct &var into :list separated by " " from &dsn; quit; %mend charlist;%charlist(var=make, dsn=sashelp.cars) %put ERROR- &list; %charlist(var=species, dsn=sashelp.fish) %put &list; %charlist(var=age, dsn=sashelp.class) %put WARNING- &list;

6) Scenario 6

/* Scenario 6 */ %macro carfinder(car);proc sql noprint;select mean(msrp) format=dollar7. into: mean_msrp from sashelp.cars where make="&car";reset print; *b/c we're doing two "select" in one macro program;title1 "&car";title2 "Average MSRP: &mean_msrp";footnote "Vehical types: &list";select model, type, msrp from sashelp.cars where make="&car" order by model;quit;%mend carfinder;%charlist(var=type, dsn=sashelp.cars) %put WARNING- &list..; %carfinder(Chevrolet);

7) Scenario 7

/* Scenario 7 */ *minoperator: in order to use macro IN operator in an expression; %macro carcheck(car) / minoperator;%charlist(var=make, dsn=sashelp.cars)%if &car in &list %then %do;%charlist(var=type, dsn=sashelp.cars)%carfinder(&car)%end;%else %do;%put ERROR: No &car.s.;%put NOTE: Cars include &list..;%end;%mend carcheck;%carcheck(Chevrolet) %carcheck(Chevy);

8) Scenario 8

/* Scenario 8 */ *try to clean up some macro variables; %macro deleteALL;*WINDOWING Environment; options nonotes; %local vars; proc sql noprint; select name into: vars separated by ' ' from dictionary.macros where scope='GLOBAL' and name not like 'SYS%' and name not like 'SQL%'; quit; %symdel %vars; options notes; %put NOTE: &sqlobs macro variable(s) deleted; %mend deleteALL;%deleteall %Put _user_;options nolabel; proc sql;select * from dictionary.tableswhere libname='SASHELP'; quit;options nolabel; proc sql;select * from dictionary.macros; quit;

9) Scenario 9

因為有一些macro variable我們明確知道我們是刪不掉的，所以用"name not like"直接去掉。

%macro deleteALL; options nonotes; %local vars; proc sql noprint; select name into: vars separated by ' ' from dictionary.macros where scope='GLOBAL' and name not like 'SYS%' and name not like 'SQL%'and name not like 'STUDIO%'and name not like 'CLIENT%'and name not like 'GRAPH%'and name not like 'OLD%'and name not like 'SAS%'and name not like 'USER%'and name not like '/_%' escape '/'; quit; %symdel &vars; options notes; %put NOTE: &sqlobs macro variable(s) deleted.; %mend deleteALL; %deleteall;

10) Scenario 10

如果scenario 9運行兩遍的話我們會發現已經沒有macro variable可以被刪除，所以我們加上%global x y z city state season之后，運行結果會變成有6個macro variable被刪除。

LESSON 4

SQL相關學習來自：數據庫原理及應用（第2版）—雷景生等編著

4: SQL 基本語句

4.1 查詢

一般格式：

SELECT [ALL|DISTINCT]<目標列表達式>[,<目標列表達式>] FROM <表名或視圖名>[,<表名或視圖名>] [WHERE<條件表達式>] [GROUP BY <列名 1>[HAVING <條件表達式>]] [ORDER BY <列名 1>[ASC|DESC]];

4.1.1 單表查詢

對查詢結果進行分組
一般形式：

GROUP BY <分組依據列>[,...n] [HAVING<組提取條件>];

HAVING子句用于對分組后的結果進行過濾，功能類似WHERE，但用于組而不是單個記錄。在HAVING子句中可以使用聚集函數，但是在WHERE子句中則不能，HAVING通常和GROUP BY子句一起使用

聚集函數（Aggregate Functions）：
COUNT/SUM/AVG/MAX/MIN etc

SELECT prj_num 項目號 FROM Out_stock GROUP BY prj_num HAVING COUNT( * ) >=2;

4.2 連接查詢

4.2.1 外連接查詢

左外連接 LEFT OUTER JOIN：輸出結果我是列出左邊關系中所有的元祖，而不僅僅是列出連接屬性所匹配的元祖
右外連接 RIGHT OUTER JOIN：結果是列出右邊關系中所有的元祖
全外連接 FULL OUTER JOIN：結果是列出左邊關系和右邊關系中的所有元祖

4.2.2 復合條件連接查詢

WHERE 語句可以有多個連接條件

SELECT Out_stock, mat_num, speci, Out_stock, amount FROM Stock, Out_stock WHERE Stock, mat_num=Out_stock, mat_num ANDprj_num='20100015'

4.2.3 自身連接查詢

連接操作可以在兩個表之間進行，但同一個表也可以與自己進行連接

查詢同時使用了物資編號為m001和m002的搶修工程的工程號

SELECT A.prj_num FROM Out_stock A, Out_stock B /這里是給Out_stock 取了兩個別名分別為A和B/ WHERE A.prj_num = B.prj_num AND A.mat_num = 'm001' AND B.mat_num = 'm002';

因為Out_stock表中每一行只記錄了某搶修工程用得一種物資，所以我們需要通過自連接得到這一搶修工程使用的兩種物資信息

4.2.4 嵌套查詢

在SQL語句中，一個SELECT-FROM-WHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢

SELECT prj_name /*父查詢*/ FROM Salvaging WHERE prj_num IN(SELECT prj_num /*子查詢*/FROM Out_stockWHERE mat_num = 'm003');

以下整理自https://blog.csdn.net/qiushisoftware/article/details/80874463

https://blog.csdn.net/qq_33973359/article/details/105414617?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_baidulandingword~default-0.pc_relevant_paycolumn_v3&spm=1001.2101.3001.4242.1&utm_relevant_index=3

不相關子查詢
內部查詢的執行獨立于外部查詢，內部查詢僅執行一次，執行完畢后將結果作為外部查詢的條件使用。由子查詢向父查詢執行。

相關子查詢
內部查詢的執行依賴于外部查詢，外部查詢每執行一次，內部查詢也會執行一次。外部查詢先執行，取出外部查詢的一個元祖傳遞給內部查詢。
父→子→父

4.2.5 內連接inner join

內連接（等值連接），返回兩張表都滿足條件的部分。

圖源：
https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

LESSON 5

5: SAS常用函數

Numeric functions

https://www.youtube.com/watch?v=Xpk7RnwARxw

這些functions都是作用于行，而不是列
簡單例子：

DATA FUN1; A = 10; B = 20; C = 30; MIN_1 = MIN(A, B, C, -10, 100); MAX_1 = MAX(A, B, B, -10, 100); RUN;PROC PRINT DATA=FUN1; RUN;

the missing values will be eliminated. Mathemetical formula cannot handle missing values in this case.

DATA TEST1; SUB_1 = 45; SUB_2 = 20; SUB_3 = 30; SUB_4 = 25; SUB_5 = 40; SUB_6 = 80; SUM_1 = SUM(OF SUB_1 - SUB_3); RUN;PROC PRINT DATA = TEST1; RUN;

first and last functions
https://www.youtube.com/watch?v=71oRJrb5aF8
截圖源自：SAS Help Center

data emp; input id age; cards; 1 50 1 51 2 45 2 67 3 78 3 45 4 56 4 58 4 77 5 78 ; run;proc sort data=emp;by id; run;data emp_temp; set emp; by id; t_f = first.id; t_l = last.id; run;data emp_temp; set emp; by id; t_f = first.id; t_l = last.id; if t_f = 1; /*get the unique observations*/ run; data emp_temp; set emp; by id; t_f = first.id; t_l = last.id; if t_f=1 then cnt=1; else cnt+1; run;

proc means
https://www.youtube.com/watch?v=0KkCahcBKtY

https://www.listendata.com/2015/01/sas-detailed-explanation-of-proc-means.html#:~:text=PROC%20MEANS%20is%20one%20of,variance%20and%20sample%20t%2Dtest.

PROC MEANS is one of the most common SAS procedure used for analyzing data. It is mainly used to calculate descriptive statistics such as mean, median, count, sum etc. It can also be used to calculate several other metrics such as percentiles, quartiles, standard deviation, variance and sample t-test.
可以做描述性統計分析

proc means data=SASHELP.CLASS; run;

proc freq
會給表格中的每一個變量建立頻率表

proc freq data=SASHELP.CLASS; run;

LESSON 6

6: PROC SQL Programming Techniques for SAS Users and Programmers(接著上面sql的例子)

https://www.youtube.com/watch?v=TncJXz_iHeE

Chapter 1

1.1 Inner Join Construct

LESSON 7—SQL 題目練習

https://www.bilibili.com/medialist/play/ml1058913098/BV1q4411G7Lw?oid=55971363&otype=2
https://zhuanlan.zhihu.com/p/38354000
https://zhuanlan.zhihu.com/p/43289968

查詢課程編號為“01”的課程比“02”的課程成績高的所有學生的學號（重點）

-- 查詢課程編號為“01”的課程比“02”的課程成績高的所有學生的學號（重點） select a.s_id from (select s_id, c_id, s_score from Score where c_id = '01') as a inner join (select s_id, c_id, s_score from Score where c_id = '02') as b on a.s_id=b.s_id where a.s_score > b.s_score

思路：首先我們想要有一個表格，這個表格中有s_id+該學生01課程的成績+該學生02課程的成績，這樣我們就能通過簡單的邏輯解決這個問題。用inner join自關聯之后便可以得到類似的一張表格。
inner join選出的是兩個表格重復的部分

我們也可以帶著成績+學生姓名一起看

select a.s_id "s_no", a.s_score "01", b.s_score "02" from (select s_id, c_id, s_score from Score where c_id = '01') as a inner join (select s_id, c_id, s_score from Score where c_id = '02') as b on a.s_id=b.s_id inner join Student as c on c.s_id=a.s_id where a.s_score > b.s_score

查詢沒學過“張三”老師課的學生的學號、姓名（重點）
方法一：進行多層嵌套
思路：先找出學過張三老師課的學生，然后取反

select * from Course where t_id = (select t_id from Teacher where t_name = "張三");

我們先查詢到張三老師教數學，課程編號為02
然后可以查詢學過02這門課程的學生有哪些：

select s_id from Score where c_id = (select c_id from Course where t_id = (select t_id from Teacher where t_name = "張三"));

然后再取反得到沒學過：用not in

select s_id, s_name from student where s_id not in ( select s_id from Score where c_id = (select c_id from Course where t_id = (select t_id from Teacher where t_name = "張三") ));

第二種解法：先連表再取not in

select s_id, s_name from Student where s_id not in ( select s_id from Score as s inner join Course as c on s.c_id = c.c_id inner join Teacher as t on c.t_id = t.t_id where t.t_name="張三") ;

查詢學過編號為“01”的課程并且也學過編號為“02”的課程的學生的學號、姓名（重點)

select s_id, s_name from Student where s_id in ( select a.s_id from (select s_id from Score where c_id = "01") as a inner join (select s_id from Score where c_id = "02") as b on a.s_id = b.s_id );

查詢至少有一門課與學號為“01”的學生所學課程相同的學生的學號和姓名（重點）
寫法1：

select s_id, s_name from Student where s_id in ( select distinct s_id from Score where c_id in ( select c_id from Score where s_id = "01") and s_id != "01");

寫法2：

select a.s_id, a.s_name from Student as a inner join ( select distinct s_id from Score where c_id in ( select c_id from Score where s_id = "01") and s_id != "01") as b on a.s_id = b.s_id;

寫法3：

select distinct st.s_id, st.s_name from Student as st inner join Score as sc on st.s_id = sc.s_id where sc.c_id in( select c_id from Score where s_id = "01" ) and st.s_id != "01";

查詢和“01”號同學所學課程完全相同的其他同學的學號(重點)
01號同學學了01， 02， 03三門課
-選出所學的課不在（01， 02， 03）的同學排除掉
-剩下的同學肯定選了（01， 02， 03）中的某幾門課
-再判斷所學的課程數是否等于3

select * from Student where s_id in ( select s_id from Score where s_id != "01" group by s_id having count(distinct c_id) = (select count(distinct c_id) from Score where s_id = "01")) and s_id not in (-- 選了和01同學不一樣的課的同學 select distinct s_id from Score where c_id not in ( select c_id from Score where s_id = "01"))

查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績（重點）

select a.s_id, a.s_name, AVG(s_score) from Student as a inner join Score as b on a.s_id = b.s_id where a.s_id in ( select s_id from Score where s_score < 60 group by s_id having count(distinct c_id) >= 2) group by s_id, s_name; select st.s_id, st.s_name, AVG(sc.s_score) from Student as st inner join Score as sc on st.s_id = sc.s_id where sc.s_score < 60 group by st.s_id, st.s_name having count(c_id) >= 2;

按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
(很重點)

select s_id "學號", MAX(case when c_id = "01" then s_score else null end) "01", MAX(case when c_id = "02" then s_score else null end) "02", MAX(case when c_id = "03" then s_score else null end) "03", MAX(case when c_id = "04" then s_score else null end) "04", avg(s_score) "平均成績" from score group by s_id order by avg(s_score) desc

使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計各分數段人數：課程ID和課程名稱

select c.c_id, c.c_name, sum(case when sc.s_score <= 100 and sc.s_score > 85 then 1 else 0 end) as "[100, 85)", sum(case when sc.s_score <= 100 and sc.s_score > 85 then 1 else 0 end) as "[85, 70)", sum(case when sc.s_score <= 100 and sc.s_score > 85 then 1 else 0 end) as "[70, 60)", sum(case when sc.s_score <= 100 and sc.s_score > 85 then 1 else 0 end) as "[60, 0)" from Score as sc inner join Course as c on sc.c_id = c.c_id group by c.c_id, c.c_name

如果用COUNT()函數的話，else后面需要改成NULL

查詢1990年出生的學生名單（重點year）

將出生年月中的年份選出來

select * from Student where year(s_birth) = 1990 select month("1990-12-21")

這里的年月日需要加引號，如果不加引號的結果是NULL
可以是“YYYY-MM-DD”, “YYYYMMDD”, “YYYY/MM/DD”, "YYMMDD"的形式

查詢所有學生的課程及分數情況（重點）：帶課程名稱和學生名稱
這種表達方式思路簡單但是復雜

select a.*, b.s_name, c.c_name from Score as a left join (select s_id, s_name from Student) as b on a.s_id = b.s_id left join (select c_id, c_name from Course) as c on a.c_id = c.c_id

顯示出來的格式是這樣的，但是更簡潔的是：s_id, s_name, 語文成績，數學成績，英語成績，化學成績（思路類似上幾道題的max形式）

select a.s_id "學號", b.s_name "姓名", MAX(case when a.c_id = "01" then a.s_score else null end) "語文", MAX(case when a.c_id = "02" then a.s_score else null end) "數學", MAX(case when a.c_id = "03" then a.s_score else null end) "英語", MAX(case when a.c_id = "04" then a.s_score else null end) "化學" from Score as a left join Student as b on a.s_id = b.s_id group by a.s_id

查詢沒有學全所有課的學生的學號、姓名(重點)
先展示一種比較典型的錯誤寫法：

select a.*, b.s_name from (select s_id, count(c_id) "選課數量" from Score group by s_id having count(distinct c_id) < (select count(distinct c_id) from Course)) as a left join (select s_id, s_name from Student) as b on a.s_id = b.s_id

這種寫法的問題在于漏掉了一門課都沒選的同學，由于這個同學沒有進行選課，所以他也沒有在Score這張表里的記錄

select st.s_id, st.s_name from Student as st left join Score as sc on st.s_id = sc.s_id group by st.s_id, st.s_name having count(distinct sc.c_id) < (select count(distinct c_id) from Course);

一定一定要注意這個ONLY FULL GROUP BY 的問題！！！！！！！
前面select了什么后面group by就一定要放進去！！！！！

SAS BY TECHIEVENTURES

https://www.youtube.com/watch?v=XSwxaqrJibY&list=PL4zQKvfkH85gD0rTaAFUDVM9XVWCa8Vhp&index=5

1. SAS PROC PRINT

Part 1:

PROC PRINT DATA = SASHELP.CLASS; RUN;

如果不聲明的話會輸出最近創建的dataset

PROC PRINT DATA = SASHELP.CLASS N = "TOTAL_STUDENTS = "; RUN;

表格最后一行會顯示出observation的總數

NOOBS

PROC PRINT DATA = SASHELP.CLASS NOOBS; RUN;

表格中最左邊一行的Obs會被去掉

DOUBLE

PROC PRINT DATA = SASHELP.CLASS DOUBLE; RUN;

在UNIVERSITY EDITION中效果看不出來

LABELS

PROC PRINT DATA = SASHELP.CLASS LABEL; LABEL NAME = "NAME_STUDENT"HEIGHT = "HT_IN_CMS"WEIGHT = "WT_IN_LBS"; RUN;

FIRSTOBS / OBS

FIRSTOBS

PROC PRINT DATA = SASHELP.CLASS (FIRSTOBS=17) N NOOBS; RUN;

結果：

2) OBS

PROC PRINT DATA = SASHELP.CLASS (OBS=5); RUN;

顯示了數據集的前五條

PROC PRINT DATA = SASHELP.CLASS (FIRSTOBS=5 OBS=10); RUN;

顯示了數據的第6-10條

Part 2：

VAR: 選擇變量 + 變量的順序

1）直接選擇變量

PROC PRINT DATA = SASHELP.CLASS; VAR NAME AGE HEIGHT; RUN;

2）根據類型選擇變量

PROC PRINT DATA = SASHELP.CLASS; VAR _NUMERIC_; RUN; PROC PRINT DATA = SASHELP.CLASS; VAR _CHAR_; RUN; PROC PRINT DATA = SASHELP.CLASS; VAR _ALL_; RUN;

Part 3：ID STATEMENT & BY STATEMENT

ID STATEMENT
1）sex這一列會將前面本來的id給覆蓋掉

PROC PRINT DATA = SASHELP.CLASS; ID SEX; RUN;

結果：

2）SEX會變成第一列，AGE會變成第二列

PROC PRINT DATA = SASHELP.CLASS; ID SEX AGE; RUN;

3）如果ID后面什么都不加的話，就僅僅是去掉obs那一列

PROC PRINT DATA = SASHELP.CLASS; ID; RUN;

4）數據集樣式的改變

PROC PRINT DATA = SASHELP.CLASS; ID AGE / STYLE(DATA) = {BACKGROUND = BLUE FOREGROUND = WHITE}STYLE(HEADER) = {BACKGROUND = YELLOW FOREGROUND = BLACK}; RUN;

結果：
會讓表格看起來更fancy一點

BY STATEMENT

Note: 在用BY STATEMENT之前，需要先SORT數據集

PROC SORT DATA = SASHELP.CLASS OUT = CLASS1; BY SEX; RUN;PROC PRINT DATA = CLASS1; BY SEX; RUN;

結果：
SEX變量不在數據集中了

在BY STATEMENT后面也可以用STYLE，it can be applied in a different way

STYLE(DATA) = {BACKGROUND = BLUE FOREGROUND = WHITE}STYLE(HEADER) = {BACKGROUND = YELLOW FOREGROUND = BLACK}; RUN;

結果：

PRACTICES
1）Find the sum of Sales and Returns of the sashelp.shoes dataset for every region

PROC SORT DATA = SASHELP.SHOES OUT = SHOES2; BY REGION; RUN;PROC PRINT DATA = SHOES2; BY REGION; SUM SALES RETURNS; RUN;

2）Group the sashelp.class by sex and find the sum of all the numeric variables in the data set

PROC SORT DATA = SASHELP.CLASS OUT = CLASS2; BY SEX; RUN;PROC PRINT DATA = CLASS2; BY SEX; SUM _NUMERIC_; RUN;

2. SAS DATE FUNCTION

Part 1: Date, Time, Today, Datetime

SAS DATE FUNCTION
需要注意的是這里的format問題，前面也有提到過；

DATA FUN_EXAMPLES; D1 = DATE(); D2 = TODAY(); D3 = TIME(); D4 = DATETIME(); FORMAT D1 D2 DDMMYY10. D3 TIME8. D4 DATETIME10.; RUN;PROC PRINT DATA = FUN_EXAMPLES; RUN;

結果：

如果想要DATETIME()顯示的更全一點就改成DATETIME18.，結果如下：

Part 2: DATEPART, TIMEPART

DATEPART: It extracts the date part of a SAS datetime value as a date value
TIMEPART: It extracts the time part of a SAS datetime value as a date value; Returns the time as hours, minutes and seconds.

DATA DATA_1; A = DATETIME(); R1 = DATEPART(A); R2 = TIMEPART(A); FORMAT A DATETIME18.; FORMAT R1 DATE9.; FORMAT R2 TIME8.; RUN;PROC PRINT DATA = DATA_1; RUN;

結果：

DATEJUL

DATA JULIAN_1; P = 18365; Q = 2018365; R = 22215; R1 = DATEJUL(P); R2 = DATEJUL(Q); R3 = DATEJUL(R); FORMAT R1 R2 R3 DATE9.; RUN;PROC PRINT DATA = JULIAN_1; RUN;

結果：

Part 3: Date, Week, Month, QTR, Year

DATA DATE_EXP_1; P = "14-DEC-15"D; R1 = DAY(P); R2 = MONTH(P); R3 = QTR(P); R4 = YEAR(P); FORMAT P DATE9.; RUN;PROC PRINT DATA = DATE_EXP_1; RUN;

結果：

WEEK

1）例子1：

DATA DATE_EXP_2; P = "14-DEC-15"D; R1 = WEEK(P, "U"); R2 = WEEK(P, "V"); R3 = WEEK(P, "W"); FORMAT P DATE9.; RUN;PROC PRINT DATA = DATE_EXP_2; RUN;

結果：

2）例子2：

DATA DATE_EXP_3; P = "30-DEC-15"D; R1 = WEEK(P, "U"); R2 = WEEK(P, "V"); R3 = WEEK(P, "W"); FORMAT P DATE9.; RUN;PROC PRINT DATA = DATE_EXP_3; RUN;

結果：

3）例子3：
這里2016年是leap year，所以也會影響week的結果

DATA DATE_EXP_3; P = "30-DEC-15"D; R1 = WEEK(P, "U"); R2 = WEEK(P, "V"); R3 = WEEK(P, "W"); FORMAT P DATE9.; RUN;PROC PRINT DATA = DATE_EXP_3; RUN;

結果：

Part 4: WEEKDAY, INTCK,INTNX,YRDIF

INTNX STATEMENT
description: Increments a date, time, or datetime value by a given time interval, and returns a date, time, or datetime value. (From SAS HELP CENTER)
語法：
INTNX( interval <.shift-index>, start-from, increment <, ‘alignment’>)
INTNX( custom-interval, start-from, increment <, ‘alignment’>)

例子來源URL：
https://www.listendata.com/2017/01/sas-intnx.html
1）例子1：在給定的日期上加7天

DATA TEMP; mydate = '02JAN2017'D; day=INTNX('day', mydate , 7); FORMAT mydate day date9.; RUN;PROC PRINT DATA = TEMP; RUN;

2）例子2：下周日是幾號？

DATA TEMP; mydate = '02JAN2017'd; nextsunday=INTNX('week', mydate , 1); FORMAT mydate nextsunday date9.; RUN;PROC PRINT DATA = TEMP; RUN;

3）例子3：Suppose you need to find out the first day of a specific day

DATA TEMP; SET sashelp.citiday; FIRSTDAY=INTNX('month', date , 0); FORMAT FIRSTDAY date9.; PROC PRINT DATA = TEMP; VAR date FIRSTDAY; RUN;

3）例子4：上周二是幾號

data temp; mydate = '10JAN2017'd; lasttuesday = intnx('week.4', mydate , -1, 'end'); format mydate lasttuesday date9.; proc print; run;

4）例子5：區間內的調整

data temp; mydate = '31JAN2017'd; beginning=intnx('year ', mydate , 1, 'b'); middle=intnx('year ', mydate , 1, 'm'); end=intnx('year ', mydate , 1, 'e'); sameday=intnx('year ', mydate , 1, 's'); format mydate beginning middle end sameday date9.; proc print; run;

結果：

5）例子6：DATETIME 格式

data temp; mydt = '29JAN2017:08:34:00'dt; seconds=intnx('second', mydt , 1); minutes=intnx('minute', mydt , 1); hours=intnx('hour', mydt , 1); days=intnx('dtDay', mydt , 1); weeks=intnx('dtWeek', mydt , 1); format mydt seconds minutes hours days weeks datetime20.; proc print NOOBS; run;

INTCX STATEMENT
https://www.listendata.com/2016/12/sas-intck-function-with-examples.html

1）例子1：計算兩個日期之間的間隔

data temp; date1 = '01JAN2015'd; date2 = '01JAN2017'd; no_of_years = intck ('YEAR', date1, date2); format date1 date2 date9.; proc print data = temp; run;

2）計算兩個日期之間間隔的年份，季度等等

data temp; date1 = '01JAN2015'd; date2 = '01JAN2017'd; no_of_years = intck ('YEAR', date1, date2); no_of_semiyears = intck ('SEMIYEAR', date1, date2); no_of_quarters = intck ('QUARTER', date1, date2); no_of_months = intck ('MONTH', date1, date2); no_of_weeks = intck ('WEEK', date1, date2); no_of_days = intck ('DAY', date1, date2); format date1 date2 date9.; proc print data = temp noobs; run;

結果：

3）calculate the number of 4 months interval between two dates

data temp; date1 = '01JAN2015'd; date2 = '01JAN2017'd; no_of_4months = intck ('MONTH4', date1, date2); run;

結果：

4）檢查日期出現的次數

data temp; date1 = '31JAN2015'd; date2 = '31DEC2016'd; diff = intck ('YEAR', date1, date2); diff2 = intck ('YEAR.3', date1, date2); format date1 date2 date9.; proc print; run;

3. SAS CHARACTER FUNCTION

Part 1: UPCASE, LOWCASE, PROPCASE

DATA CASE_1; A = "TEChieVENTures"; UP_CASE1 = UPCASE(A); LO_CASE1 = LOWCASE(A); PROP_CASE1 = PROPCASE(A); PC_CASE2 = UPCASE("test"); LO_CASE2 = LOWCASE("TECHIE"); RUN; PROC PRINT DATA = CASE_1; RUN

總結

以上是生活随笔為你收集整理的SAS MACRO的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

SAS
macro