廣告贊助

SAS base 考試必備70真題 第二題

2. Given the following raw data records in TEXTFILE.TXT:

----|----10---|----20---|----30

John,FEB,13,25,14,27,Final
John,MAR,26,17,29,11,23,Current
Tina,FEB,15,18,12,13,Final
Tina,MAR,29,14,19,27,20,Current

The following output is desired:

Obs  Name  Month  Status    Week1   Week2   Week3   Week4   Week5

 1   John   FEB   Final       $13     $25     $14     $27       .
 2   John   MAR   Current     $26     $17     $29     $11     $23
 3   Tina   FEB   Final       $15     $18     $12     $13       .
 4   Tina   MAR   Current     $29     $14     $19     $27     $20

Which SAS program correctly produces the desired output?

(A)  
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dsd;
   input Name $ Month $;
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

 

(B)  
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dlm=',' missover; 
   input Name $ Month $;
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

 

(C)
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dlm=',';
   input Name $ Month $ @; 
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 


(D)
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dsd @;
   input Name $ Month $; 
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

 

Answer: (C)

★版權為本作者所有,若轉載請務必註明出處及作者名稱FengOrz,未經本作者同意不得作為商業用途。★

 

本題重點在於 INFILE 語句讀取原始資料時, MISSOVER, DSD, DLM, 與 @ 的運用。

(A)
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dsd;    /*用DSD*/
   input Name $ Month $;
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

DSD (Delimiter-Sensitive Data)可以做到三件事:(1) 忽略引號中的分號、(2) 忽略引號、(3) 將兩個連續的分號視為遺漏值。且其預設分隔符號為逗號,若分隔符號並非逗號,則要以 DLM 來指定。

本題 INPUT 語句讀原始資料時,先看第一列 John,FEB,13,25,14,27,Final ,看到 FEB 的時候停住,
這時用 IF 語句判定 Month=FEB 為真,故執行 INPUT Week1到 Week4 及 Status 。Week1 到 Week4 這四個變數以數值格式、Status變數以文字格式讀入,
接下來並不是我們直覺認為的繼續讀入第一列剩餘的資料 13,25,14,27,Final,因為我們並沒有加入 @ 符號告訴SAS還在同一列,故SAS會跳到原始資料的第二列 John,MAR,26,17,29,11,23,Current 來讀入,
此時 John 為文字,明顯不符 Week1 數值變數的規定,故為遺漏值;MAR 同理也遺漏;26 及 17 能順利寫入 Week3 和 Week4中;29被放入變數Status中變成文字。後面 format 再將變數格式改為金額。

 

(B)  
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dlm=',' missover/*用DLM及MISSOVER*/
   input Name $ Month $;
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

若原始資料每列長度不一,則用 MISSOVER 指派沒有值的資料變為遺漏值。
這邊也使用了 DLM ,指定逗點為分隔符號。
然而 INPUT 語句一樣在讀入 Name 和 Month 這兩個變數後,便跑去讀原始資料的第二列了。此處留意 MISSOVER 沒有讓 SAS 停在同一列的功能。

 

(C)
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dlm=',';   /*用DLM*/
   input Name $ Month $ @
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

正確答案。
這邊的 @ 叫 a trailing at,告訴 SAS 先停在此觀測值,先用 IF 語句來判定是否符合條件,接著繼續讀此列剩下的資料來放到變數中。

 

(D)
data WORK.NUMBERS; 
   length Name $ 4 Month $ 3 Status $ 7; 
   infile 'TEXTFILE.TXT' dsd @;   /*用DSD及@*/
   input Name $ Month $; 
   if Month='FEB' then input Week1 Week2 Week3 Week4 Status $;
   else if Month='MAR' then input Week1 Week2 Week3 Week4 Week5 Status $;
   format Week1-Week5 dollar6.;
run; 
proc print data=WORK.NUMBERS;
run; 

若真的下去跑,這題乍看之下跑的出來,但其實他 proc print 那邊執行的 data 會是(C)選項所建的 NUMBERS 的表。
去看log檔,則可看到錯誤訊息。原因是 infile 後面本就沒有加 @ 的寫法,@ 僅出現於 input 中。

 

 

★版權為本作者所有,若轉載請務必註明出處及作者名稱FengOrz,未經本作者同意不得作為商業用途。★

 

文章標籤
創作者介紹
創作者 FengOrz 的頭像
FengOrz

FengOrz的部落格

FengOrz 發表在 痞客邦 留言(0) 人氣()