[SAS] macro與PROC SQL的二三事v1

PROC SQL裡面,也可以與macro功能作應用

透過彼此互動,在PROC SQL執行的時候產生macro變數或是更新macro變數

本文利用範例說明一些例子,較深入的運用則不在本文範圍內,未來有機會再作介紹

使用者可以在PROC SQL程式裡面,利用SELECT statemen裡面加上INTO的句子去達到創macro變數的目的

與之前文章內提過的CALL SYMPUTCALL SYMPUTX功能類似,在步驟執行時去做創/更新macro變數的動作

而這個INTO句子在PROC SQL執行的時候,提供連結到macro變數symbol table,像一座橋。

基本上當PROC SQL執行時,你在裡面撰寫會產生macro變數的這個變數會被送到local macro symbol table

如果PROC SQL不是從一段macro程式內發送,則會被macro processor存在global macro symbol table

在PROC SQL內撰寫INTO句子的基本寫法

SELECT 欄位1, 欄位2, …
                 INTO :macro變數1, :macro變數2… 
                 FROM 哪個資料 
                 WHERE 條件 
                其他想加的句子
;

基本寫法有幾個要注意的地方

首先是INTO句子後面接的macro變數名稱,前面要用「:」冒號,而不是「&」

先介紹後面範例會使用到的資料檔poissonreg.csv檔

是來自UCLA SAS Data Analysis Examples→http://www.ats.ucla.edu/stat/sas/dae/poissonreg.htm

資料為兩間學校學生出席率的情況,內有ID、學校代碼、數學成績、語言藝術成績等變項

為了範例在學校變項的內容有略做一點修改,除數字coding之外也有字串對應

Macro與PROC SQL的運用範例(1)

這個例子是希望計算檔案pois2裡面,學校名稱為Wuling的數學成績平均以及考試的學生數

我們直接在select statement裡面作平均數與次數的計算,丟到新創的macro變數mnmath與nmath裡面

最後用%PUT在log file內作結果顯示

%let schoolname=Wuling;/*指定macro變數schoolname的值*/
proc sql noprint;
      select mean(math), count(math)/*算平均數與次數*/
                   into :mnmath, :nmath/*塞到這兩個macro變數內,注意冒號的使用*/
      from pois2/*來自檔案pois2*/
     where schooln=”&schoolname“;/*當變項schooln為macro變數schoolname的值則處理之*/
quit;
%put &schoolname mean of math=&mnmath, # of student=&nmath;

因為有下noprint選項加上使用%PUT輸出結果到log file,結果自然會出現在log file,如下↓

%put &schoolname mean of math=&mnmath, # of student=&nmath;
Wuling mean of math=42.20372, # of student=     159

我們可以看到,當school為Wuling的學生數有159,該數學平均為42.20372

使用PROC SQL也是挺方便的不是?

Macro與PROC SQL的運用範例(2)

接著要記錄第二個範例就有趣了

資料來自SAS help裡面的weather檔案,內有日期、飛機延遲原因、飛機延遲次數

proc sort data=weather out=daysorted;
    by day;/*利用變項day來做排序*/
run;
proc sql noprint;
    select day,flight,reason
       into :day1, :flight1, :reason1/*產生3個macro變數,他們的值分別是上一行3變項的第一筆觀測值*/
    from daysorted;/*從檔案daysorted*/
quit;
%put The first day of delays on &day1;
%put The first reason of delays was &reason1;
proc print data=daysorted(obs=5);/*只顯示前五筆觀測值*/
run;

在Log file內有一段關於%PUT結果如下↓

67   %put The first day of delays on &day1;
The first day of delays on 16DEC88
68   %put The first reason of delays was &reason1;
The first reason of delays was Fog
69   proc print data=daysorted(obs=5);
70   run;

而請PRINT procedure輸出前五筆觀測值出現在output視窗

我們可以得知當想將資料內的1個變項塞入macro變數中,只會塞入第一筆觀測值的值而已

就像macro變數day1的值=資料檔案day的第一個值16DEC88,以此類推

暫時寫到這邊,歡迎各位前輩先進補充建議

參考資料:

(1) SAS Macro Programming Made Easy, Second Edition

(2) Introduction to SAS.  UCLA: Academic Technology Services, Statistical Consulting Group,
from http://www.ats.ucla.edu/stat/sas/dae/poissonreg.htm



11 Responses to “[SAS] macro與PROC SQL的二三事v1”

  1.   Brian Says:

    如果想要將一個變項中所有觀測值塞進macro變數中,可以將程式改寫成 into :day1 separated by ‘ ‘ (每一個觀測值用空格分開)

  2.   Shrimp Li Says:

    謝謝Brian補充!!!

  3.   Dora Says:

    Such a deep aswner! GD&RVVF

  4.   Stan Says:

    您好,可否請問在into :day1 separated by ‘, ‘ 這類的情況下若還想group by有可能的方式嗎?
    例如男性的得出一個觀測值塞入的macro變數,女性的得出另一個。
    謝謝。

  5.   Shrimp Li Says:

    Hello, 如果你的問題是依照性別分別得出兩個macro variables
    可以在一個proc sql裡面利用where分別寫兩個select satement
    例如:
    PROC SQL NOPRINT;
    SELECT * INTO :male SEPARATED BY ‘ ‘ FROM
    OUT1 WHERE gender=1;
    SELECT * INTO :female SEPARATED BY ‘ ‘ FROM
    OUT1 WHERE gender=0;
    QUIT;

  6.   Stan Says:

    Shrimp Li 大大,非常感謝您的指導
    基本上是這個意思沒錯,只是面臨到的是group變項裡的項目會增長,無法一個一個寫出來…
    例如:
    Name Note
    AA 喝酒
    AA 抽菸
    AA 存款
    BB 吃飯
    BB 睡覺
    CC 打牌
    .. …
    .. …
    .. …

    **因為Name會一直增加下去,無法一個一個寫在Where
    若用SAS傳統語法可用retain,但想要用SQL來寫,連結”Note”文字,group by “Name”,達成如下:
    Name Note
    AA 喝酒,抽菸,存款
    BB 吃飯,睡覺
    CC 打牌
    .. …
    .. …
    .. …

    或是形成A的macro variables- 喝酒,抽菸,存款
    B的macro variables- 吃飯,睡覺
    C的macro variables- 打牌

    請問這樣是有辦法用 into , separated by , 和Group 之類的達成嗎?

    抱歉回的有點晚,非常感謝您!

  7.   shrimp Li Says:

    針對若要用sql寫
    Name Note
    AA 喝酒,抽菸,存款
    BB 吃飯,睡覺
    CC 打牌
    .. …
    .. …
    .. …

    目前想到的是, 先將data transpose後黏在一起, 再用into輸出

    另外問題中提到**因為Name會一直增加下去,無法一個一個寫在Where
    也可以使用sql的distinct功能撈出name做成可跳動的macro variable, 再做where輸出
    這樣就不用一行一行輸入

    目前想到這些~ 供參

  8.   Brian Says:

    以下程式應該可以解決你的問題,
    可以產出 aa, bb, cc 的macro variable, 參考看看囉

    proc sql;
    select count(distinct(name)) into: total from a;
    select distinct name into: name1 separated by ‘ ‘ from a;
    %macro a;
    %global &name1;
    %do i = 1 %to &total;
    select note into: %sysfunc(scan(&name1,&i,’ ‘)) separated by ‘,’ from a
    where name = “%sysfunc(scan(&name1,&i,’ ‘ ))”;
    %end;
    %mend a;
    %a;
    quit;

  9.   Stan Says:

    非常感謝兩個先進的指導 ^^
    Thank you very much!!!

  10.   cheng Says:

    您好
    想請問一下有關欄位的值轉為聚集變數該怎麼做?
    例子:ID欄位要轉為varID1~varIDn
    ID
    BEN
    Jenny
    Winnie
    john
    .
    .
    .
    要輸出巨集變數到log為
    varID1=BEN
    varID2=jenny
    VarID3=Winnie
    VarID4=john

  11.   Shrimp Li Says:

    直轉橫嗎?如果是MSSQL有pivot可以使用
    看起來你的N是未知?

    PROC TRANSPOSE可以完成你的目標嗎?


total of 2288483 visits