122008
 
原文載點:http://www2.sas.com/proceedings/forum2007/052-2007.pdf

如果你曾經讀過去年的這一篇文章「Text Utility Macros for Manipulating Lists of Variable Names」,而且也親自使用過該原文作者所提供的 text utility macro 的話,想必一定會對其強大的批次 rename 功能印象深刻。不過這個 macro 有個缺點,那就是在使用前要先定義這個東西:
%let orig_vars = a01a a01b d01 d02 t1_r t2_r;

這個 %let 的作用是把所有原始變數集中定義成 orig_vars 這個變數,然後再讓 text utility 來使用。不過,如果當變數很多時,我們需要手動去輸入,仍舊是相當耗時。而之前我曾經發過這篇文章「Automatically Renaming Common Variables Before Merging」,裡面是利用 PROC SQL 來抓出舊變數名稱。不過,如果舊變數名稱是有規則可尋,比方說 y1~y1000 時( %let 不允許使用 y1-y1000 這種寫法),則這篇在 SAS Global Forum 2007 由 Ian Whitlock 發表的技術文件可以有很大的幫助。

這篇技術文件一共發佈了五個有用的 macro 讓使用者可以輕易操作大量變數名稱。以下就一一介紹:

。%ZIP
這個 macro 顧名思義就和壓縮檔 zip 一樣,可以組合數個不同的變數或 index 來產生有規則的變數。語法如下:
%zip (l1= , lv1= , sep1=, l2= , l2v= , sep2= , osep= )

變數解釋如下:
。l1= 第一列字串
。lv1= 取代第一列字串的字串(可省略不用)
。sep1= 用來區隔第一列字串的字元,預設值是空白(%str())
。l2= 第二列字串
。l2v= 取代第二列字串的字串(可省略不用)
。sep2= 用來區隔第二列字串的字元,預設值是空白(%str())
。osep= 用來區隔輸出字串的字元,預設值是空白(%str())

比方說想要生成「Ax By Cz」這三個字串,可使用下列程式:
%zip(l1=A B C, l2=x y z);

l1 所表示的「A B C」會去抓在 l2 相對應位置的「x y z」,這便是 %zip 所呈現的功能。

。XPROD
這是可以拿來產生連續字尾或字首的 macro。語法如下:
%xprod ( l1= , lv1= , l2= , lv2=, sep1=, sep2=, osep=) )

使用方式和 %zip 一模一樣,但產生出來的效果完全不同。比方說想要產生「lib.w1 lib.w2 lib.w3」這三個字串,可發現這三個字串只有尾數不同,所以可以用 %xprod 來產生:
%xprod(l1=lib.w, l2=1 2 3);

換句話說,在 %xprod 裡面,l1 是拿來放共同字串,l2 是拿來放會改變的字串。他甚至可以做出更複雜的效果。假設想要產生「a_x a_y b_x b_y c_x c_y」這六個字串,我們可以想像這是一個 3X2 的矩陣:
      x   y
a_
b_
c_

而實際上 %xprod 就是用這種原理去做字串合併,因此我們只需要輸入:
%xprod(l1=a_ b_ c_, l2=x y);

就可自動產生那六個字串出來。

。RANGE
這是拿來生成連續數字的程式。語法如下:
%range ( to=, from= , step= , sep= )

變數解釋:
。to= 連續數字最後一位數,預設值是 1
。from= 連續數字的第一位數,預設值是 1
。step= 跳號的間格數,預設值是 1
。sep= 分隔連續數字的字元,預設值是空格(%str( ))

顧名思義,如果要生出「1 2 3 4 5」這串連續數字,則可使用:
%range(to=5);

。REPLACE
這是拿來取代字元的 macro,特別是使用在重新命名的動作裡面。語法如下:
%replace ( l= , lv= , code= , key= , lsep=, osep=)

變數解釋:
。l= 輸入字串
。lv= 取代上列字串的字串(可省略不用)
。code= 包含 key 所代表的符號變數的字串,通常是在程式中不會被變動的字串
。key= 用來當作取代「l」代表的字串的符號變數,預設值是 #
。lsep= 用來區隔輸入字串的字元,預設值是空白(%str())
。osep= 用來區隔輸出字串的字元,預設值是空白(%str())

舉例來說,如果在套入一組資料時要順便改變某些變數名稱,則程式碼為:
data ww;
set w(rename=(x=__x y=__y z=__z);
run;

則可使用 %replace 來簡化:
data ww;
set w(rename=(%replace(l=x y z, code=#=__#));
run;

這個程式會先把 l 所定義的 x y z 用依序一個 # 來取代,而 code 的作用就是把 x y z 一一套入「#=__#」的框架裡面,進而產生「x=__x y=__y z=__z」這三個字串出來。

另外還有更進階的寫法,就是把 %replace 放進另一個自創的 %rename 裡面,程式如下:
%macro rename ( list, pref=__) ;
%replace ( l=&list, code = # = &pref# )
%mend rename ;


%rename 的第一個參數就等於 %replace 的第一個參數,而 pref 就等於輸出字串裡面共有的字串(此例預設值是「__」)。因此原來的程式可以更簡化為:
data ww;
set w(rename=(%rename(x y z)));
run;

這時「x y z」就等於「list」,也等於「l」,而 pref 所表示的「__」被丟進 %replace 的 code 參數裡面,則 code 就變成「code= #=__#」,這就便回原來只用 %replace 的那個程式碼了。

%replace 也可以使用在變數轉換上面。比方說上面這個例子,在 rename 後要把新變數轉換成 best 32. 的格式。如果不用額外的 macro 則語法是:
data ww;
set w(rename=(%rename(x y z)));
x=input(__x, best32.);
y=input(__y, best32.);
z=input(__z, best32.);
run;

同樣地,我們可以另外自製一個命為 %CHAR2NUM 的 macro,然後套用 %replace 去寫。方法如下:
%macro char2num ( list , pref = __ ) ;
%replace ( l=&list
, code= %str(# = input(&pref#,best32.);)
)
%mend char2num ;

這個新 macro 的參數用法和 %rename 一樣,但我們來看看 %replace 在裡面發會什麼功用。l 去呼叫 list 所定義的字串,而 code 生出「#=input(&pref#,best32.);」,其中 # 就是暫時替代 l/list 參數所定義的字串,pref 是利用預設值「__」,則 code 其實就是產生下面這三行:
x=input(__x, best32.);
y=input(__y, best32.);
z=input(__z, best32.);

因此原本的程式可以改寫成:
data ww;
set w(rename=(%rename(x y z)));
%char2num(x y z);
run;

。QT
這個 macro 只是很簡單地幫每個字元加上雙引號。語法如下:
%qt ( l=, lv= , lsep= %str( ), qt = %str(%"), osep=%str( ) )

變數解釋:
。l= 輸入字串
。lv= 取代上列字串的字串(可省略不用)
。lsep= 用來區隔輸入字串的字元,預設值是空白(%str())
。qt= 幫每個字元左右加上的符號,預設值是雙引號(")
。osep= 用來區隔輸出字串的字元,預設值是空白(%str())

因此,如果要生成出「"a" "b" "c"」只需要簡單地使用:
%qt(l=a b c);

以下是各 macro 的原始碼:
。ZIP
%macro zip
( l1= /* first list */
, lv1= /* external variable override for first list */
, sep1=%str( ) /* separator between the joined elements */
, l2= /* second list */
, lv2= /* external variable override for second list */
, sep2=%str( ) /* separator between the joined elements */
, osep=%str( ) /* separator between new elements */
) ;
/* %zip ( l1= a b , l2= c d ) produces ac bd
so does
%let list1 = a b ;
%let list2 = c d ;
%zip (lv1=list1, lv2=list2)
If lists do not have same length shorter length used and
warning to the log. Empty lists result in empty list and
no message.
If the LV options are used then L1, L2, and ZIP_: should be
avoided for variable names.
*/
%local zip_i zip_1 zip_2 zip_list ;
%if %length(&lv1) = 0 %then
%let lv1 = l1 ;
%if %length(&lv2) = 0 %then
%let lv2 = l2 ;
%do zip_i = 1 %to &sysmaxlong ;
%let zip_1 = %qscan(%superq(&lv1) , &zip_i, &sep1 ) ;
%let zip_2 = %qscan(%superq(&lv2) , &zip_i, &sep2 ) ;
%if %length(&zip_1) = 0 or %length(&zip_2) = 0 %then
%goto check ;
%if &zip_i = 1 %then
%let zip_list = &zip_1&zip_2 ;
%else
%let zip_list = &zip_list&osep&zip_1&zip_2 ;
%end ;
%check:
%if %length(&zip_1) > 0 or %length(&zip_2) > 0 %then
%put WARNING: Macro ZIP - list lengths do not match - shorter used. ;
%unquote(&zip_list)
%mend zip ;


。XPROD
%macro xprod
( l1= /* first list */
, lv1= /* external variable override for first list */
, sep1=%str( ) /* separator between elements of first list */
, l2= /* second list */
, lv2= /* external variable override for second list */
, sep2=%str( ) /* separator between elements of second list */
, osep=%str( ) /* separator between elements of new list */
) ;
%local xp_i xp_j xp_1 xp_2 xp_list ;
%if %length(&lv1) = 0 %then
%let lv1 = l1 ;
%if %length(&lv2) = 0 %then
%let lv2 = l2 ;
%do xp_i = 1 %to &sysmaxlong ;
%let xp_1 = %qscan(%superq(&lv1), &xp_i, &sep1) ;
%if %length(&xp_1) = 0 %then %goto endloop1 ;
%do xp_j = 1 %to &sysmaxlong ;
%let xp_2 = %qscan(%superq(&lv2), &xp_j, &sep2) ;
%if %length(&xp_2) = 0 %then %goto endloop2 ;
%if &xp_i = 1 and &xp_j = 1 %then
%let xp_list = &xp_1&xp_2 ;
%else
%let xp_list = &xp_list&osep&xp_1&xp_2 ;
%end ;
%endloop2:
%end ;
%endloop1:
%unquote(&xp_list)
%mend xprod ;


%RANGE
%macro range /* second more efficient version due to Chang Chung */
( to=1 /* end integer value */
, from=1 /* starting integer value */
, step=1 /* increment integer */
, osep=%str( ) /* sparator between integers */
) ;
/*
return sequence of integers starting at &FROM going to &TO
in steps of &step
%range(to=5) produces 1 2 3 4 5
*/
%local rg_i ;
%do rg_i = &from %to &to %by &step ;
%if &rg_i = &from %then
%do;&rg_i%end ;
%else
%do;&osep&rg_i%end ;
%end ;
%mend range ;


。REPLACE
%macro replace
( l= /* value list */
, lv= /* external variable override for value list */
, lsep=%str( ) /* separator between values */
, code= /* block of code containing symbolic variable */
, key=# /* symbolic variable to replace (#abc# etc.) */
, osep=%str( ) /* separator between new elements */
/* may be %str(;) when code is statement */
/* if so remember to add closing semicolon */
) ;
%local rg_i rg_w rg_list ;
%if %length(&lv) = 0 %then
%let lv = l ;
%if %length(%superq(&lv)) = 0 /*or %index(%superq(code),&key) = 0*/ %then
%do ;
%let rg_list = %superq(code) ;
%goto mexit ;
%end ;
%do rg_i = 1 %to &sysmaxlong ;
%let rg_w = %qscan(%superq(&lv),&rg_i,&lsep) ;
%if %length(&rg_w) = 0 %then %goto mexit ;
%if &rg_i = 1 %then
%let rg_list = %sysfunc(tranwrd(%superq(code),&key,&rg_w)) ;
%else
%let rg_list =
&rg_list&osep%sysfunc(tranwrd(%superq(code),&key,&rg_w)) ;
%end ;
%mexit:
%unquote(&rg_list)
%mend replace ;


Contact information
Ian Whitlock
29 Lonsdale Lane
Kennett Square, PA 19348
Ian.Whitlock@comcast.net
 Posted by at 6:14 上午
112008
 
Today was site maintenance day for me. There were several pages that I needed to update. I have to be careful when I start working on these pages because I see a shiny thread and I follow it. Before I know it, it's lunch time and I've been reading interesting SAS tidbits and haven't completed my maintenance tasks. As you have seen in previous posts, I like to share my finds with you. When I see something interesting, I pop over to this blog to tell you about it.

Do you know that ZIP codes are constantly being updated? I didn't, but Liz Simon does. She maintains the ZIP code data and the Maps Online Web site. In response to several customer requests, she put together a tip about updating your ZIP code data for use with SAS maps. She updates the SASHELP.ZIPCODE data set quarterly and makes it available to you for download. Quarterly?! They change that much? Who knew?

Here's the tip teaser.
The zip file provided in the download is a SAS data set in transport format, and you should use this file to replace your existing SASHELP.ZIPCODE data set.
Continue reading "Keeping Up with ZIP Codes Changes"
032008
 
I was in a conference room waiting for a meeting to start the other day. Several people in the room were talking about an interesting numbers trick that Clarke Thacher had posted to his internal blog. All agreed that others might be intrigued by his "Magic Trick." I talked to Clarke and got his permission to make it available for you here. The following is Clarke's Magic Trick.

Here's a magic trick to amuse and befuddle your (geeky) friends.


  1. Take any positive value.

  2. Add a special value to it.

  3. Subtract that value out again.

  4. You have rounded your original value to the nearest whole integer.

Here's a log that demonstrates this magic:

25         data null;
26 format m n best20.;
27 magic = &magic;
28 input m @@;
29 put m= +1 @15 @;
30 n=m+magic;
31 put ' => ' @;
32 n=n-magic;
33 put n=;
34 cards;

m=0.25 => n=0
m=1.25 => n=1
m=1.5 => n=2
m=1.75 => n=2
m=2.25 => n=2
m=2.5 => n=2
m=2.75 => n=3
m=1000.0001 => n=1000
m=1000.5 => n=1000
m=2047.25 => n=2047

Now you can challenge your audience to guess the value of MAGIC. You might want to offer a prize to the first person who can give the correct answer. Note: This trick will not work on the mainframe.




Continue reading "Numbers and Magic Tricks"
282008
 
Contributed by Sean Gargan and Judi Rourke-O'Briant, SAS Publishing

You may not notice the crowds at the SAS Publishing Bookstore but the virtual aisles are about to see a big increase in traffic. Beginning in September, the bookstore will open its doors internationally. Customers from around the world will be directed to the SAS Publishing Bookstore to discover the latest titles from SAS Press and SAS documentation.

To accommodate customers outside the US and Canada, a link to Amazon.com will be added to each book description. This link will take you directly to the book description page within Amazon.com. In Amazon.com’s own words “Amazon.com can ship to virtually any address in the world”. In addition, a growing list of international resellers will be made available for purchasing books locally.

Using the Google book search facility also provides customers with a list of local resellers. If you’re in Spain, you’ll see Casa del Libro if you’re in Germany, you’ll see Buchkatalog.de. Try it from your own location to see the list of local booksellers that carry SAS Publishing titles.

Our objective is to make it easy (and local) for you to buy books about SAS.

Should you need assistance with your order and want to have a real-time, text message conversation with a US customer service rep, use the new Live Chat feature. It will be added to each page in the SAS Publishing bookstore beginning in early September. Live Chat will be available (in English) Monday through Friday, 9:00am to 5:00pm EST.

To all our international customers who will now be using the SAS Publishing Bookstore, welcome! Accommodating international customers in one catalog is a new venture for us, if you have issues or concerns, please let us know so we can address them. Of course, if you like what you see, we’d be happy to hear that as well. Until next time.
282008
 

当你使用WEB浏览器调用存储过程时,且时GET方式并且含有中文信息,存储过程有可能会返回如下错误信息
Transcoding failure.STP: A failure occurred in execution set up in context.

解决办法:
找到storedprocess WEB所在目录,更改如下文件:
web-inf\web.xml

把其中的输出编码改为中文编码即可,如GB2312,如下:


          storedprocessservlet

 Posted by at 7:18 上午
272008
 
Whew! I've been speeding around information stops all over the Web. That's quite a workout. As alluded to in my last post, you can find really useful information in some unlikely places. Given that I challenged you to broaden your Internet stops, I've been trying to do the same. Because time is limited, accepting that challenge for myself meant that I ignored my usual stomping grounds. Feeling traitorous, I returned to them this morning and right away found a gem to share with you. I found this gem on my way through the SAS discussion forums.

Cynthia@sas responds to a poster in the SAS Procedures forum who asked "Is there a way to count the frequency of strings within a character variable?" Cynthia provides a short SAS program that performs a frequency count of the words in the first chapter of Melville's Moby Dick. In her post, she writes:
Interestingly enough, after you eliminate all the articles and prepositions and pronouns, the most frequently used word in the first chapter of Moby Dick is 'sea' (13 times) followed by 'water' (8 times). The words 'ship', 'soul', 'man' and 'whale' each occur 3 times. Anyway, the relevant part of that program is shown below -- I had to get rid of a stray '?' in the chapter, which is why the compress is in the code. Also, I turned everything to lower case, so 'The' and 'the' would get counted the same when I did a frequency on the WORD variable.

See the post to get the code. Anybody want to count the words in the speeches given at the two political conventions?
242008
 
原文載點:http://www.lexjansen.com/mwsug/2007/Tutorials/T10-2007.pdf

ODS 系統在 SAS V9.1 版中已經逐漸發揮他的威力,到了 V9.2 版後更是往上提升了一個階層。不過很多人以為用用 ODS 把圖或報表直接輸出就已經是最方便的,殊不知他可以結合最簡單的 PROC PRINT 程序將輸出結果強化。這一篇在 NESUG 2007 發表的技術文件詳細記載了如何讓 ODS 搭配 PROC PRINT 來產生更美觀的報表,也同時揭露了一些在 SAS 手冊裡都沒有介紹的指令。

簡單來講,如果要用 PROC PRINT 程序加上 ODS 來列印報表,在完全沒有額外設定,只使用預設值的情況下,語法大概就諸如此款:

你拍攝的 2008-08-22_1738。

然後得到的結果是:
你拍攝的 2008-08-22_1734。

如果你覺得上面這種呈現方式有點像是墓碑上刻出來的文字,而想要換成別的樣式或顏色,那麼就得靠 PROC PRINT 程序手冊裡面都很少提到(至少 SAS online documentation 沒有寫)的 style option!

style option 基本的撰寫格式如下:
你拍攝的 2008-08-22_1738。

以這個圖例說明:
你拍攝的 2008-08-22_1755。

利用四個 style 指令將 Obs 標題顏色、表格內資料背景顏色、Obs 列顏色以及底部 total 底色一次進行修改。

接下來又一步一步來介紹不同 style 指令的用法。

1. 更改標題列顏色

如果想要將標題列著成黃色,如下圖所示:
你拍攝的 2008-08-23_1621。

則可以用下面這道程式來實現:
你拍攝的 2008-08-23_1623。

其中,黃色標記處 style(header)={background=yellow}; 即是讓標題列變成黃色的關鍵。

2. 更改總和列顏色和字型和大小

PROC PRINT 裡面有個 SUM statement 可以計算每個欄位的總和,如想要更改此處的背景顏色、字型樣式和字型大小,如下圖所示:
你拍攝的 2008-08-23_1626。

則可以使用這道程式:
你拍攝的 2008-08-23_1630。

黃色標明處 style (grandtotal)={background=grayee font=(Arial) font_size=6}; 即是更改由橘色標明處 SUM statement 所算出來的兩個總和欄位的背景顏色(grayee)、字型(Arial)和字型大小(font_size)。如果沒有 SUM statement,則黃色標明處的語法將不會有任何作用。

3. 更改表格資料欄背景顏色

如想要改變表格資料的背景顏色,如下圖所示:
你拍攝的 2008-08-23_1637。

則語法如下:
你拍攝的 2008-08-23_1652。

黃色標明處 style(data)={background=grayee} 即是用來設定資料欄位背景顏色。附帶一提,標題列會自動斷成兩行是利用紅色標明處的 split='*' 進行設定,然後在橘色標明處 label statement 裡面將標題要斷行的地方打上 * 即可。

4. 更改分類總和欄位背景顏色

如果有使用 by statement 來計算不同分類欄位的總和,則其背景顏色也可以修改,如下圖所示:
你拍攝的 2008-08-23_1657。

語法如下:
你拍攝的 2008-08-23_1659。
你拍攝的 2008-08-23_1701。

此程式由於有使用 by origcity; 和 sum statement 來計算每個程式 sales2005 和 sales2006 的總和,所以要修改分類總和欄位的背景顏色,就得用黃色標明處的 style(total)={background=light green}; 來設定。

5. 更改特定欄位字體顏色

若想指定某個欄位的字型顏色,如下圖所示:
你拍攝的 2008-08-23_1704。

可使用下列程式:
你拍攝的 2008-08-23_1707。
你拍攝的 2008-08-23_1706。

黃色標明處的 var month 後面加上 /style={foreground=red}; 就可以更改 month 這個欄位字型顏色。此外,一開頭的 SAS logo 是用 title3 後面的 "^S=preimage='c:\ben\qp\SAS_logo.bmp'}" 所貼上。

總結:作者有坦承這篇文件沒有把 22 個 trick 都用上,不過會在 conference 裡面說明。不過由於沒有她在會議裡面的任何影音檔,所以無法得知其他沒有在文件裡面的小技巧是什麼。但上述這些語法已經足夠讓表格變的更漂亮,因此有興趣的人可以玩玩不同組合最出現什麼情況。

CONTACT INFORMATION
Your comments and questions are valued and encouraged. Contact the author at:
Ben Cochran
The Bedford Group
Raleigh, NC 27607
Office: 919.831.1191
Fax: 919.831.1191
Email: bedfordgroup@nc.rr.com
Web: www.bedford-group.com
 Posted by at 4:13 上午
202008
 
As August draws to a close and school systems all over the country are calling students back to their desks, I can't help but daydream about lavish vacation spots with warm breezes and cool blue water. But, this post isn't about vacationing. It is about finding information in out-of-the-way places. These sites aren't hidden or hard to find, they just exist on a different path than you usually take to your content. I'd like to introduce you to just a few things I found in places that I recently discovered.

Discovery 1
This is a tip from Kirk Paul Lafler that was published in the SESUG spring 2008 e-Newsletter.
Title:    PROC SQL Join Algorithms and the _METHOD Option

PROC SQL _METHOD;
SELECT MOVIES.TITLE, RATING, ACTOR_LEADING
FROM MOVIES,
ACTORS
WHERE MOVIES.TITLE = ACTORS.TITLE;
QUIT;

Read the tip to find out what this code can be used for.

Discovery 2
Here's a quick tip from the SAS UK office that appeared in their April In The Know e-Newsletter.
Question:    Is there a way to check the syntax of a step prior to executing the step?
Answer:    Yes, use the option 'Cancel' on the 'Run' statement. The CANCEL option terminates the step without executing it and prints a warning message that the step was not executed at the user's request.
Read the tip for example code.

Discovery 3
One last tip for you from the SAS Programmers group on Facebook. This tip was posted by Elinor Jane Leland Zenta, who wrote:
In the SAS window click tools => options => keys
In the F12 spot (which should be empty) type in
output;clear;log;clear;wpgm;submit
When you run your programs press F12 which will clear your log and output windows and submit your program.

Share your discoveries
Do you visit a location that others may not know about? If so, take a minute to share the location with the Support News blog readers by posting a comment below. Alison Bolen shared several lists of Web addresses that might be of interest to SAS professionals. You can view her list of Online communities for SAS users and SAS professionals in the sascom voices blog.
202008
 

一直觉得SAS得帮助做的很棒,想不到9.2考虑得更加完善
居然提供了一个Learning to Use SAS 这个帮助,很完善得例子,把SAS得每个模块都讲了,非常多,并且有详细得例子,例子看起来也特别简单易懂
如图:


大家把例子全部过一遍,基本上就把SAS得功能全部走一遍了
很好
如下一个调用外部DLL函数的例子:
 /****************************************************************/
 /*          S A S   S A M P

 Posted by at 7:19 上午