Currently this column mode only supports selecting (highlighting) but not editing. Hoo, it’s better than nothing.

• SDTM 3.1.2
• SDTM 3.1.1
• Define.xml 1.0
• ADaM 1.0

You can get the corresponding configuration files (validation rules) online or in the software folder (in ..\opencdisc-validator\config with extension of .xml). Since SDTM 3.1.2 has the most rich set of validation rules from Janus, WebSDM and of course additional  OpenCDISC rules by itself, its configuration file (config-sdtm-3.1.2.xml) deserves more attention. Better understanding of config-sdtm-3.1.2.xml is the first step to customize the software according to business needs. Followings are some personal tips and tricks to play and even “torture” the file, using Notepad++, web browsers (IE and Firefox), Excel with MSXML and SAS XML Mapper.

1. DON’T use the Windows default Notepad to open and edit the xml file

while the reason:

if you use Notepad to open a XML file, almost you get nothing but strings and strings.

and another supporting reason, see bellowing picture.

2. USE Notepad++ or other REAL text editors to open and edit it

Notepad++ makes the difference. It supports multiple tabs view, XML syntax highlighting and XML tags match and other fancy stuff never in the plain Notepad. And like OpenCDISC, it’s free, both in sense of free beer and free speech.

Other real text editor, include Vim, UltraEdit and such, but for most users, I still think Notepad++ is the most handy one.

3. At first, use a web browsers to review it

It is the web view of config-sdtm-3.1.2.xml. The secret is a style file, define-1.0.xsl in ..\opencdisc-validator\config\schematron. This is another story of dichotomy. The config-sdtm-3.1.2.xml file itself is only used to store metadata (machine-readable), while the style file (also a XML file) used to instruct how to display it (human-readable). Within some proper internal interface, web browsers (I tested in IE and Firefox; Google Chrome doesn’t work). Excel can also render this XML file well (only test on Excel 2010 and 2007) while Web view is much better:

4. The real awesome job: use Microsoft XML parser or other XML parsers to dig into XML structure

I use Excel 2010 with Microsoft XML parser (MSXML 6.0. You can get the version of your MSXML by visiting this website in IE and you will get the different results when switching to other web browsers because Firefox and Chrome use other parsers).

You can also get a instance of each XML tag:

5. The real awesome job: use SAS XML Mapper to get the tabulation view

And you may want to exact all the tables in the XML file with tabulation view, ideally, in SAS dataset:

For example, the first few rows in config-sdtm-3.1.2.xml:

and the corresponding SAS dataset:

Actually you can put all the data in XML into a big dataset but with lots of redundancies. To use SAS XML Mapper (the latest version is 9.3), you should design a mapping file to tell the structure of the XML file. For the simple ODM dataset, you indicate the table name, column name and path, type and length:

It never be fun to play with XML files. SAS XML Mapper is supposed to read CDISC ODM based XML files automatically (OpenCDISC XML files are called ODM compliant), but at least for this config-sdtm-3.1.2.xml, it failed and that’s why we should create a mapping file (see above) by ourselves. Fortunately you don’t need to write it from scratch (it would be thousands of lines of codes):

• find a CDISC ODM based XML file that SAS XML Mapper can read automatically, e.g., in http://www.cdisc.org/define-xml, a file named define-example1.xml works well.
• use AutoMap function in SAS XML Mapper to get the mapping file.
• modify the mapping file to fit your needs.
• for details, refer SAS XML mapping syntax.

6. Final Notes for Excel

Right click config-sdtm-3.1.2.xml then open with “Microsoft Excel”:

Option 2 will go to section 3. If go with option 1:

Option 1-1 and 1-2:   tabulation view in section 5

Option  1-3:  tag view in section 4.

Today, Jan 23, is the first day of Chinese New Year, and it is Monday, the first day of work week–it is always joyful to have such coincidenceJ. HAPPY NEW YEAR!

I had a big move this year. Actually I passed through multiply new years of 2012: I took my flight from Beijing in Jan 1 (happy new year!) and landed in Raleigh, North Carolina also in the same day due to the time difference among China and US where I take a new job as a Life Sciences Consultant at d-Wise Technologies. I’m not supposed to be a pure SAS programmer any more, but I don’t want to change the theme of my blog (“Hello World by A SAS Programmer“) since my most handy language is still SAS while my role will expose me more SAS products and solutions.

Chinese New Year is also called “Spring Festival” and “spring” is always the key word for the holiday:

(Chinese Characters for “the Beauty of Spring”. I wrote these years before in Beijing)

And I also want to spread my spring greetings with few sentences from Walt Whitman, These I Singing in Spring:

THESE, I, singing in spring, collect for lovers,

. . .

Collecting, I traverse the garden, the world—but soon

I pass the gates,

Now along the pond-side—now wading in a little, fearing not the wet,

Now by the post-and-rail fences, where the old stones

thrown there, pick’d from the fields, have accumulated,

(Wild-flowers and vines and weeds come up through

the stones, and partly cover them—Beyond these I pass,)

Far, far in the forest, before I think where I go,

Solitary, smelling the earthy smell, stopping now and then in the silence,

Alone I had thought—yet soon a troop gathers around me,

Some walk by my side, and some behind, and some embrace my arms or neck,

They, the spirits of dear friends, dead or alive—thicker

they come, a great crowd, and I in the middle,

Collecting, dispensing, singing in spring, there I wander with them,

Plucking something for tokens—tossing toward whoever is near me;

. . .

F3: run SAS codes (in batch mode)
F4: close other two windows (the current active window is Log window after F3 running; F4 jump to SAS file with full window)

F5: jump to SAS file
F6: jump to Log file
F7: jump to lst file (list output)
F8: keep only the current window (full window)

****************

Details and Credits

1. The first post on Vim and SAS I read is by Xiaowei Wang in Chinese.

The original SAS syntax file took from Zhenhuan Hu.

Kent Nassen also maintains some Vim functions to run SAS codes and check log.

2. To run SAS codes using F3:

map <F3> :w<CR>:!SAS % -CONFIG “C:\Program Files\SAS\SASFoundation\9.2\nls\en\SASV9.CFG“<CR>:sp  %<.lst<CR>:sp  %<.log<CR>

3. Close other windows using F4:

map <F4> :close<CR>:close<CR>

4. Keep only current window using F8:

map <F8> : only<CR>

5. Jump to SAS file using F5:

map <F5> :e %<.sas<CR>

6. Jump to Log file using F6:

map <F6> :e %<.log<CR>

7. Jump to Lst file using F7:

map <F7> :e %<.lst<CR>

Followings are my personal collections of SAS macro Repositories (I will keep it update according to new sites reached and your inputs). Most of them are rich, well documented and friendly for navigation and review:

/***General***/

1. SAS Macros by Richard DeVenezia

http://www.devenezia.com/downloads/sas/macros/index.php

Richard is a very active contributor in SAS-L. He also plays with Java, Perl, PHP and JavaScript and you can find all these codes in his homepage. Besides the well organized macros, there are some interesting utilities:

http://www.devenezia.com/downloads/sas/samples/

2. Roland‘s SAS Macros

http://www.datasavantconsulting.com/roland/Spectre/maclist2.html

Roland, a proficient SAS programmer from Europe, also supply two SAS applications:

Spectre – a Practical and Educational Clinical Trials Reporting Engine
http://www.datasavantconsulting.com/roland/Spectre/index.html

RGPP -Graphical Patient Profiler
http://www.datasavantconsulting.com/roland/RGPP/rgpp.html

And some tips:
http://www.datasavantconsulting.com/roland/sastips.html

3. Chris‘s SAS Macros

http://sas.cswenson.com/downloads/macros

I just found Chris’ site weeks before and Chris is a pretty cool programmer: in one of his fun macros, an error message will be such kind of form:

ERROR: MWA HA HA! You fool! You are cursed with leprosy!

4. sconsig SAS Coding Tips and Techniques 

http://www.sconsig.com/sastip.htm

rich while badly for navigating and review

5. Arnold Schick’s macros

http://schick.tripod.com/macros.html

also some macros collected from SAS-L :

http://schick.tripod.com/p-index.html

6. Rodney A. Sparapani ‘s Macro

http://www.mcw.edu/PCOR/Education/SASMacros.htm

Rodney is best known for his contribution to SAS support in ESS (Emacs Speaks Statistics) as a cool programmer and WinBugs and Bayesian then he works as a statistician.

Rodney’s site also contains lots of statistical stuff.

/***Statistics***/

1. Mayo Clinic Locally Written SAS Macros
http://cancercenter.mayo.edu/mayo/research/biostat/sasmacros.cfm

or in

http://mayoresearch.mayo.edu/biostat/sasmacros.cfm

2. Paul D. Allison
http://www.ssc.upenn.edu/~allison/#Macros
new site: http://www.pauldallison.com/Download3.html

Paul is a prolific writer with books on SAS and statistics.

3. MCHP SAS Macros

http://mchp-appserv.cpe.umanitoba.ca/viewConcept.php?conceptID=1048

4. Ralph O’Brien ,UnifyPow: A SAS Module for Sample-Size Analysis
http://www.bio.ri.ccf.org/Power/

5. Usual Dietary Intakes: SAS Macros for the NCI Method
http://riskfactor.cancer.gov/diet/usualintakes/macros.html

6. Clinician’s corner: SAS macros
http://www.medicine.mcgill.ca/epidemiology/Joseph/PBelisle/sas-macros.html

/***Graph***/

1. Robert Allison’s SAS/Graph Examples!
http://robslink.com/SAS/Home.htm

My first stop for SAS graphics.

2. SAS Graphic Programs and Macros by Michael Friendly
http://www.datavis.ca/sasmac/

more popular in academia.

WPS uses a lite version of Eclipse as GUI(WPS Workbench; the “lite” means WPS Workbench can’t be extensible as the original Eclipse but really with shorter response time). Besides its Project Explore for folders and files management(left panel), I also love its Outline in right panel to show the SAS programming elements and errors information in log window:

Then I’d like to switch to SAS itself. Frankly speaking, at least in IDE part, WPS looks pretty better than the current corresponding SAS:

Of course I hold the principle of “substance over form”, but if available, the form itself also make people comfortable and enjoyable (for example, the Apple products…). As far as I know, the new version of SAS DI Studio and Enterprise Miner both have pretty much improvement in GUI from ergonomic point of view. Even for code editor, Enterprise Guide Editor  is now more superior than so called SAS Enhanced Editor. But as a SAS programmer (not only the SAS user), I may spend all my day in the Base SAS window!

I also spent some time to configure VIM as a relative simple SAS IDE as a temporally replacement (F3 to run, F5 to jump to program window, F6 to log window, F7 to output window just as the same as in SAS IDE):

It’s simple but always can do the job as SAS itself while looks really cool to comfort myself as a programmer. So, what’s going on in the next release? Still wait.

I use python-2.7.2, the final Python 2.x release most because of the various modules support for learning purpose. The book helps me to get the quick review of Python is Think Python: How to Think Like a Computer Scientist by Allen Downey.

Also, I begin to use CodeColorer for this blog to insert codes.

draco dormiens nunquam titillandus

You can get the meaning using Google search of course—but not in Google Translator (Google Translator can’t recognize all of such Latin words!). Jian posted a concise Perl way to parse webs which contain this Latin phrase and key words “mean”,  “means” and such and you can always find page like

“draco dormiens nunquam titillandus,” which means “never tickle a sleeping dragon.”

My SAS approach can’t return a human readable sentence like this one but a 100% word to word machine translation and you can use it to translate any Latin sentence which happens not appear in any singe web page. The usage is also very simple:

filename L2E url ‘http://jiangtanghu.com/docs/en/Latin2Eng.sas’;
%include L2E;

%Latin2Eng(draco dormiens nunquam titillandus)

and you get:

Obs   draco         dormiens                               nunquam                         titillandus

1    dragon    sleep, rest                 at no time, never            tickle, titillate, provoke
2     snake     be/fall asleep          not in any circumstances     stimulate sensually
3                   behave as if asleep
4                   be idle, do nothing

and also (2*4*2*2=) 32 Cartesian combinations to feel the meaning if needed.

Then you can also test the words by Julius Caesar:

%Latin2Eng(Veni Vidi Vici)

and get:

Obs   Veni       Vidi                                                                     Vici

1     come    see, look at                                                     conquer, defeat, excel
2                 consider                                                           outlast
3                (PASS) seem, seem good, appear, be seen      succeed

This SAS translator is based on WORDS (version 1.97FC) by William Whitaker and the codes still needs some modifications when any unexpected special symbols popped up in the translating page.

MapReduce is a programming framework and really language independent, so SAS users can also get the basic idea from their daily programming practices and here is just a simple illustration using data step array (not array in Proc FCMP or matrix in IML). Data step array in SAS is fundamentally not a data structure but a convenient way of processing group of variables, but it can also be used to play some list operations like in Python and other rich data structure supporting languages(an editable version can be founded in here):

Follow code above, the programming task is to capitalize a string “Hadoop” (Line 2) and the “master” method is just to capitalize the string in buddle(Line 8): just use a master machine to processing the data.

Then we introduce the idea of “big data” that the string is too huge to one master machine, so “master method” failed. Now we distribute the task to thousands of low cost machines (workers, slaves, chunk servers,. . . in this case, the one dimensional array with size of 6, see Line 11), each machine produces parts of the job (each array element only capitalizes a single letter in sequence, see Line 12-14). Such distributing operation is called “map”. In a MapReduce system, a master machine is also needed to assign the maps and reduce.

How about “reduce”?  A “reduce” operation is also called “fold”—for example, in Line 17, the operation to combine all the separately values into a single value: combine results from multiple worker machines.

Tom Davenport reported an observation that Silicon Valley is becoming more analytical since companies in the Valley such as Google, Facebook, eBay, LinkedLn all have strong presences in analytics. Besides such predominant companies, I’d also like to add Yahoo to the list although Yahoo is no longer in its peak. Yahoo is the largest sponsor and contributor of Hadoop, an open source framework for distributed processing of so called “big data”. When taking a look at the outstanding Facebook data team or LinkedIn data team, we can see that Hadoop is also one of the most overwhelmingly successful technical factors. Such Valley companies themselves are the huge consumers of big data and have strong incentives to develop analytical solutions beyond their high technology product pipelines.

Analytical staffs in LinkedLn also helps a lot to promote the widely usage of the term “data scientist”. They identify themselves as data scientists and that’s really cool. Now more and more statisticians are also very glad to accept this brand new title. According to a survey in JSM (2011, Miami), more than 85% (164) statisticians there considered themselves “data scientists”.

McKinsey also released a report this May on big data and the huge gap of qualified analytical talents. You know when a management consulting firm begins to talk something technical, it is no longer a fashion to follow the discussion of the concept. To embrace the challenge of big data, one or the team needs multidiscipline background—basically speaking, computer science and statistics (and data mining or machine learning is just an interdisciplinary subject of them). Here is an ambitious list on “How do I become a data scientist”:

http://www.quora.com/Educational-Resources/How-do-I-become-a-data-scientist

For these learning plans, just feel the meaning and don’t take it too seriously. Check yourself and set up your own priority.

Notes for SAS Programmers

For SAS programmers, I read an exciting post besides High Performance Computing that SAS will also play with Hadoop by introducing some functionality in SAS/Access and SAS Data Integration Studio.

For SAS programmers with no IT background, it is not a good idea to jump into algorithms and data structures and other hard core computer courses immediately. Instead I recommend to take the full advantages of SAS language and system itself to dive into computer world gradually:

1. Learn and practice and practice SAS Proc SQL which is compliant with the SQL-92 standard. SQL is the common language in database world and SAS Proc SQL can help you switch smoothly to Oracle SQL, Teradata SQL, MySql SQL and other SQL implementations although there are some non-critical differences in details.

2. Dig into the operating system specific documentation of SAS, for example in SAS 9.3,  SAS 9.3 Companion for Windows or SAS 9.3 Companion for UNIX Environments or others depending the OS you are working on. They are the critical important documentations but unfortunately often missed in SAS programmers’  reading list.

Such docs will help SAS programmers to deal with the machines and expose to the wide computer world in a way that a SAS programmer can understand. You can’t expect to be an expert on computer via such docs, but at least you can communicate fluently with internal IT staff.

3. Then you get all the confidences to play with computer and can switch to any other topics interested in the list above!

In the previous post, I just mentioned that Fisher’s Iris Data is embedded officially in SASHELP library in SAS 9.2. Note that even in SAS 9.1.3, you can also find this data with several instances from some demos in user guide (just search "Iris" in "SAS Help and Documentation" accompany with you SAS product), for example, in SAS 9.1.3 IML.

Iris dataset is so important and popular that researchers round the world use it as benchmark to test and compare their algorithms and also as pedagogical purpose. It is also the overwhelming No. 1 dataset considering popularity in UCI Machine Learning Repository. Here 4 errors in SASHELP.iris listed for your consideration if interested and if you find some slightly differences in outputs following some demos out of SAS using this data:

Error 1: Line 35, the PetalWidth of Setosa should be 2 mm, not 1 mm;

Error 2: Line 38, the SepalWidth of Setosa should be 36 mm, not 31 mm;

Error 3: Line 38, the PetalLength of Setosa should be 14 mm, not 15 mm;

Error 4: Line 119, the PetalLength of Virginica should be 69 mm, not 70 mm.

For errors 1-3, there is also an interesting story in statistical literature. In 1936, Fisher the Great published his famous paper, The use of multiple measurements in taxonomic problems and the Iris data also attached (called Fisher Version in this post). In the following years (until today), people cited this paper and the Iris data Fisher Version is also replicated and distributed worldwide and then a version with above errors 1-3 might gain a very dominant popularity (I don’t know the source of there errors). In UCI Machine Learning Repository, the dataset iris.data is the one with such 3 errors (called UCI Version as well).

We could see that the duplicated UCI Version is even more popular in some extension than its original Fisher Version (SASHELP.iris also seems to be copied from UCI Version). Story goes on. In 1998, James Bezdek and other scholars just found the three discrepancies between Iris Fisher Version and UCI Version (and in some published papers using the same version of data). You can read it in Will the Real Iris Data Please Stand Up?

Bezdek then proposed to use the original Fisher Version of Iris, and UCI Machine Learning Repository also documented these three errors and added new dataset called bezdekIris.data (Bezdek Version) which is exactly Fisher Version (iris.data kept and I think it is because now the so called error version is also valuable).

Return to error 4 and I can’t figure out why and I might as well call it Iris SAS Version. Note that the unit in SAS Version is millimeter (mm), while others version all use centimeter (cm).

The interesting part is that I also check the Iris data in SAS 9.1.3 IML mentioned before and not surprising, it is exactly the Fisher Version (you can also find a right one in a demo from SAS 9.2 IML Studio 3.2).

The following codes generate several Iris versions:

iris_uci: UCI Version with both CM and MM as unit

bezdekiris_uci: Bezdek Version or Fisher Version with both CM and MM as unit

iris_mm: UCI Version with MM as unit and attributes alike SASHELP.iris, SAS Version

bezdekiris_mm: Bezdek Version or Fisher Version with MM as unit and attributes alike SASHELP.iris, SAS Version

filename iris URL "http://archive.ics.uci.edu/ml/machine-learning-databases/iris/";

%macro getIris(input);
data &input._uci;
    length _Species $15.;
    length  Species $10.;
    infile iris(&input..data) dlm=’,';
    input _SepalLength _SepalWidth _PetalLength _PetalWidth _Species $;

    Species=propcase(scan(_Species,2));

    array iris {*}  _SepalLength _SepalWidth _PetalLength _PetalWidth;
    array iris_mm{4} SepalLength  SepalWidth  PetalLength  PetalWidth;

    do i=1 to dim(iris);
        iris_mm{i}=iris{i}*10;
    end;

    drop i;

    label   _sepallength=’Sepal Length (cm)’
            _sepalwidth =’Sepal Width (cm)’
            _petallength=’Petal Length (cm)’
            _petalwidth =’Petal Width (cm)’
            _Species =’Iris Species’;

    label   sepallength=’Sepal Length (mm)’
            sepalwidth =’Sepal Width (mm)’
            petallength=’Petal Length (mm)’
            petalwidth =’Petal Width (mm)’
            Species =’Iris Species’;
run;

data &input._mm (label="Fisher’s Iris Data (1936)");
    set &input._uci;
    drop _:;
run;

%mend getIris;

%getIris(iris)
%getIris(bezdekIris)

Tell me something about Alfred, male or female? age? height and weight?

Oracle database (version 9 and below) had a well known default demo account SCOTT with a password, TIGER (and TIGER was the name of the real person Bruce Scott ’s cat, see) and in this account, there are some tables named DEPT, EMP, BONUS and SALGRADE (you can read their meaning). Almost every Oracle DBA learn SQL using these database and an joke just says that in DBA’s meetings, people just  warm up saying “how about Smith?” And you should know that in the database, Smith is a clerk and his boss is Ford (whose boss is Jones)!

In the beginning I also raise a question for SAS programmers: who is Alfred? Don’t give quick answer such that “Alfred who”. Actually, you should already go through with Alfred very well as a SAS programmer:

proc print data=sashelp.class;
    where name="Alfred";
run;

As a clinical SAS programmer, I play with data, get acquaintance with the data and subjects and then subjects are no longer “subject”. They have identities and  Alfred is a 14 years old boy. I have such habit mostly because in clinical world, data are very expensive (not like the massive transaction data in financial industry) and should be took more care.

I dare say that “class” is the most famous SAS dataset in sashelp library and then in the SAS world. The first dataset used for demo is almost this “class”. I just did a quick Google search, “sas sashelp.class” returns about 44,400 results. Hope you can find any other SAS datasets to beat it.

Alfred in “class” pops into my mind because today, I do find a strong candidate. In SAS 9.2 (and 9.3), the sashelp library has a new member, Iris. YES, it is the “Fisher Iris Flower Data”, which can be safely considered the most famous and most  used dataset in machine learning and data mining papers and statistical applications. Currently it has only 859 hits in Google, I think the number will reach high accompany with the wide use of SAS 9.2 and above, and to enforce my prediction, I will definitely play with the Iris data in the following future!

You Could be a SAS® Nerd If . . .

Here I’m glad to find that I am roughly a 20% SAS nerd (12 matched in all 57 lists):

8. You blog SAS-related comments and technical solutions frequently.

9. You have more than five SAS blogs in your RSS feed.

10. Your home page is support.sas.com, sasCommunity.org, SAS-L, or LexJansen.com.

11. You know more than ten SAS keyboard shortcuts.

12. You get excited when you find a new match-merge technique that performs better than the one you developed the week before.

21. You have more than one version of SAS on your machine or network so you can compare and contrast program, processing and output differences.

28. You spend your Friday evenings and weekends responding to SAS-L posts, entering sasCommunity blog entries, and reading the latest “hot” SAS topic on LexJansen.com.

38. The first thing you read in the morning is the “Tip of the Day”.

45. You subscribe to five or more SAS groups on LinkedIn, sasCommunity, and Facebook and you use a tabbed browser so you can be online with all of them at the same time.

47. You spend your evenings and weekends SAS-L’ing, Googling and Binging looking for elegant SAS technical solutions.

50. You proudly proclaim that you’re a SAS programmer when asked by a fellow passenger, “What do you do for a living?”

51. You’re amazed when your fellow airline passenger replies, “What is a “SAS programmer?”.

I also asked Kirk if he is a 100% SAS nerd and Kirk replied, NO. He said he is a 99% SAS nerd:)

As a learner, I benefit frequently from lots of high quality blogs. But I’m also slightly lazy as a reader: I read almost all blogs in Google Reader rather than returning to the original web pages and adding some comments. Here I’d like to post a series of SAS bloggers and their blogs (that I subscribed and read frequently) to extent my thanks, share with friends and sometimes amuse the bloggers themselves:)

In this page, honor belongs to Jian Dai and his Yahoo group, “SAS Academy”:

http://tech.groups.yahoo.com/group/sas_academy/

Wait. You may ask: a Yahoo group?

Jian actually is a blogger for a collective blog, Clinical Trial New Technologies & Best Practices. But here I’d like to refer this Yahoo group which actually serves as his own blog and I also use Google Reader to subscribe its RSS feed:

launched at December of 2008

444 posts till July 2011, averagely 14 posts per month (and roughly 1 post for every 2 days!)

And all in all, most (and almost every) posts are under Jian’s name and few posts come from other’s hands are just outlier-like(less than 10). Note that I just read all the historical messages in a weekend and got the impressions. In the following page, I might retrieve all the data from the SAS Academy and supply accurate reports.

Recently Jian posted a C implementation of a SAS tokenizer based on Finite-state Machine(FSM). A tokenizer is a lexical analyzer of a specific programming language and in this case you can just imagine it is a small part of SAS Supervisor to recognize all tokens. In SAS Academy, Jian posted lots wonderful (and beautiful) implementation of lots of general or specific problems using C, Perl, JScript, JavaScript, PowerShell, VBS/VBA, . . . and even ProLog, a general logic programming language!

Wait wait. You may wonder: is Jian a SAS programmer?

I don’t know how Jian identify himself as a programmer and it would be best to leave this question to himself. But don’t worry. There is no such phrase engraved at the door of Jian’s SAS Academy:

Let no one ignorant of C/Perl enter.

Actually Jian is a SAS/statistical/clinical programmer by job and he of course has lots of interesting SAS codes posted, including SAS implementation of Lambda Expression and recursion(Jian is also an active conference speaker on this topic). –Some clinical programmers without any computer background would find it difficult to understand the CS part even of Jian’s SAS codes, for example, Lambda Expression. Suggestion: Google it and you will enter a totally new joy world.

Jian plays like a geek, and he is always making efforts to extend the horizon and scope of SAS programming language by taking advantage from computer science(actually he had some posts titled play like a CS pro). I have great experience by exploring Jian’s SAS Academy and very often, a popular book title pops up in my mind:

Programming Pearls

If you want sometimes programming for fun, just for fun, that’s it.

First step: define the scope

For my purpose, I only need the titles and publish dates of Rick’s posts. It is so called the “metadata” of the blog. I do not need all the post contents. By the way, if all information needed, you can use a blog backup tool, or write codes to retrieve all the pages of http://blogs.sas.com/iml at the maximum depth, or simply, you can write to Rick and say: hey Rick, could you please send me all the contents of your blog? And Rick may go to the management console of his own blog, export all the contents to an XML file and get back to you.

Second step: analyze the web pages

Browse to the right panel of Rick’s blog, in the ARCHIVES frame, click “Older”:

And you get

This page just gives a big picture of Rick’s blog (ARCHIVE section is always a good place to get such metadata, for example, archives for my blog). But we need more. Click “view topics” for example of September, 2010:

This page is exactly what we want with titles and dates. Open an editor to write codes immediately to read all the information in this page?—wait. Currently this blog has posts across 11 months and you can expect the increase. You should design a dynamic method to read all the topics pages: Sep 2010, Oct 2010, … and, today().

Return to the archives page. RCM (right click your mouse) and select “View page source” if you use Google Chrome web browser (“View Source” in IE; “View Page Source” in Firefox) and you get all the HTML scripts (Note: you DO not need any knowledge of HTML to understand this post). Copy and paste them into a text editor supporting HTML syntax highlighting (such as Notepad++). Search all instances of “view topics” we mentioned before:

We are lucky. They are 11 instances of “view topics” accompanying with 11 hyperlinks for the currently 11 months’ archives of Rick’s blog. We can read such 11 hyperlinks to a macro array for dynamic retrieval.

Then we return to the single topics page, for example of September, 2010. Review the HTML source file. Search for “posted_by_date” and we get 14 instances which is same as the number of posts in September 2010:

We should also need to locate all the instances of titles. Search “/iml/index.php?/archives/” and we get 17 responses:

We see 3 instances at end of the finding results don’t contain any titles. You can check other pages to confirm such pattern. Yes we can use regular expressions to parse the HTML pages to locate more exactly for the titles. But for a quick job and due to the relative simple HTML pages, some basic SAS character functions are enough for our purpose. In the following codes, limited regular expressions are used only to remove HTML tags such as “<a href=”.

After such explorative search of HTML scripts, we can get the basic idea where can we find the interested information. Then we begin to coding work.

Third step: Coding at last!

For our purpose, we should first read the archive page to get all the topics links to a macro array, then read the all the topics pages dynamically. Finally, we should also add the all the calendar dates with holidays. Some friends may find that they met piece of the following codes before. Yes, such codes just assembled some skills what I learned from Art Carpenter, Richard DeVenezia, Jian Dai and lots of programmers before!

3-1: read archive page

%let URL=http://blogs.sas.com/iml/index.php?/archive;

filename archive URL "&URL";

data archive;
    length text $1024;
    infile archive lrecl=1024;
    input text $;
    text= _infile_;
    if index(text, ">view topics<") then output;
run;

data  archive1;
    set archive;
    summary=scan(text,4,’"’);
run;

3-2: read all topics pages

data _null_;
    set  archive1 end=eof;
    I+1;
    II=left(put(I,2.));
    call symputx(‘summary’||II,summary);
    if eof then call symputx(‘total’,II);
run;

%macro readit;
    %do i=1 %to &total;
        filename f&i URL "&&summary&i";   

        data f&i;
            length text $1024;
            infile f&i lrecl=1024;
            input text $;
            text= _infile_;
            if index(text, "/iml/index.php?/archives/") or index(text, "posted_by_date") then output;
        run;

    /*remove HTML tags;*/
    data ff&i;
        set f&i; 
        prx=prxparse("s/<.*?>//");
          call prxchange(prx,99,text);
        drop prx;

        flag=ifn(mod(_n_,2),1,2);
        grpn=&i;
        if index(text,"201") and length(text)<10 then delete;/*be carefull! hard coding;*/
        seq=_n_;
     run;

     /*transpose data;*/
     data fff&i;
         set ff&i;
        by grpn seq flag;

        retain title;
        if first.flag then title=lag(text);
        if flag=1 then delete;
     run;

    %end;

%mend readit;
%readit

%macro getall;
    data Rick;
        set %do i=1 %to &total; fff&i %end; ;
        seq=seq/2;   
        drop flag;
    run;   
%mend getall;
%getall

data rick2;
    set rick;

    datetime=scan(text,2,",");     
    year=scan(text,2,".");       
    month=scan(scan(text,2,","),1);
    day=scan(scan(text,2,","),2);     
    week=scan(text,6);            
    dt=compress(catx("",day,substr(month,1,3),year));  
    worddat=input(dt,date9.);                        
    format worddat  ddmmyy10.;                        

    m=scan(put(worddat,ddmmyy10.),2);             
    my=compress(catx("",year,m));                     
run;

proc sort ;
    by  worddat descending seq ;
run;

It is also interesting to add additional information for further analysis, such as all calendar dates during Rick’s blogging history and holidays.

3-3: get all calendar dates

proc sort data=rick2 out=rick3 nodupkey;
    by my;
run;

data _null_;
    set rick3 end=eof;
    I+1;
    II=left(put(I,2.));
    call symputx(‘year’||II,year);
    call symputx(‘month’||II,m);
    if eof then call symputx(‘total’,II);
run;

%macro getCalendar;
    %do i=1 %to &total;
        data calendar&i;
            date1 = mdy (&&month&i,1,&&year&i);
            date2 = intnx (‘month’, date1, 1) – 1;

            do worddat = date1 to date2;
                  wim = intck (‘week’, date1, worddat);
                  dim = worddat-date1+1;               
                  output;
            end;

            format worddat  ddmmyy10.;           
            keep   worddat  dim ;
      run;
    %end;
%mend getCalendar;
%getCalendar;

%macro allCalendar;
    data Calendar;
        set %do i=1 %to &total; calendar&i %end; ;   
    run;   
%mend allCalendar;
%allCalendar

3-4: get holidays

I have no specific idea about US holidays and just referenced the two pages:

http://support.sas.com/kb/24/655.html 

http://www.opm.gov/Operating_Status_Schedules/fedhol/2011.asp

Also, the holidays and observances should manully modified according to personal working schedule. So this section serves only for demonstration.

filename hld url "http://jiangtanghu.com/docs/en/US_holiday.sas";

%include hld;

%US_holiday(2010)
%US_holiday(2011)

data holiday;
    set holiday2010 holiday2011;
run;

proc sort ;
    by  worddat;
run;

3-5: put all together

data rick_all;
    merge rick2 calendar holiday;
    by worddat; 
    if worddat <’03Sep2010′d then delete;    
    if worddat >’15Jul2011′d then delete;
    drop today;
run;

A pooled version (beta) also available at

http://jiangtanghu.com/docs/en/SAS_Blogs.sas

non-fiction on  rose,

fiction on blue,

poetry on yellow

The SAS blog writer, author of Statistical Programming with SAS/IML Software, Rick Wicklin of SAS Institute,  also leads a strong “color revolution” in SAS blog community:

In an interesting personal statement, Blogging, Programming, and Johari Windows, Rick summarizes his rich and colorful blogging rhythms according to the above Johari window:  

• Mondays, writes introductory notes (corresponding to the upper right quadrant of Johari window). 
• Wednesdays, experimental articles on sampling, simulation and other statistical programming topics(lower left quadrant).
• Fridays, on explorative analysis of data (upper left quadrant).

So what about the lower right quadrant? Rick rediscovers and exposes what he once knew. Just suppose that, Rick picks up some codes he wrote before (ten years ago maybe) with big surprise: oh, who on earth wrote such damned clever beautiful codes? He or she must be in his/her aggressive youth. –then Rick wrote them all in blog.

Here I produced a summary table for Rick’s blogging activities (numbers per month per weekday; before July 16, 2011 Beijing time; next following post would introduce how to use SAS to analyze data from website such as Rick’s blog):

Key findings:

• Rick is really a frequent and productive blogger with averagely 0.5 posts per day!
• Rick DOES keep his words. Most of the posts are published in Friday, Wednesday and Monday(44, 44, 42 posts respectively).
• None posted in Saturdays and Sunday.

Rick began his writing since September  3. 2010, Friday.  Up to July 15. 2011, Friday, there are 48 Fridays, 46 Wednesdays and 46 Mondays.  Only 10 colored weekdays (4 Fridays, 2 Wednesdays and 4 Mondays) passed with no posts and most all them are due to national holidays:

06/09/2010 , Monday       : Labor Day
24/11/2010 , Wednesday: round Thanksgiving Day
26/11/2010 , Friday         : round Thanksgiving Day
22/12/2010 , Wednesday
24/12/2010 , Friday         : Christmas Day
27/12/2010 , Monday
31/12/2010 , Friday         : New Year’s Day
30/05/2011 , Monday      : Memorial Day
10/06/2011 , Friday
04/07/2011 , Monday      : Independence Day

At least in 4 holidays (most in Monday), Rick was also active in writing:

11/10/2011, Monday, Columbus Day: How Do You Reshape a Matrix?
11/11/2010, Thursday, Veterans Day: It’s Here!
17/01/2011, Monday, Birthday of Martin Luther King, Jr.: On the Flip Side: Exchanging Rows and Columns
21/02/2011, Monday, Washington’s Birthday: How to Build a Vector from Expressions

Amazing Rick keeps a fixed writing pattern and in next following post, detailed analysis and SAS codes will be presented so you can also keep eyes on the metadata of your favorite bloggers’ writing and may rise a question like:

Hey Rick, what’s up in Jun 10, 2011, Friday?

My finger hurts,

I can not dance

Tango in Bastille.

So… any ideas?

…

… …

… … …

OK. I am a terrible Haiku writer. The sentences are not self explained without  /*commentaries*/ . So the story behind the scene…

One day my remote workstation was very very slow due to some network issues. But I still needed to write and run SAS codes in the server which is located in France. It really hurt my fingers from the ergonomic point of view.

“Tango” was my project code. But why Bastille? oh, that day happened to be round the Bastille Day in France. The performance of the remote server that time was just as terrible as the Bastille I want to break so I could do my project work more smoothly!

—————————–

Note that after my completing this Tango Haiku, I did a Google search and found that there is really a French book called Bastille tango!

• Two-sided confidence intervals for the single proportion: comparison of seven methods.
  Newcombe RG, Stat Med , Volume 17 , 8 (April 1998) pp.857-872
• Interval estimation for the difference between independent proportions: comparison of eleven methods.
  Newcombe RG, Stat Med , Volume 17 , 8 (April 1998) pp.873-890

This post serves as an implementation using SAS accompany with the first paper on confidence intervals for single proportion(method 1-7). Additional 4 methods also provided:

1.  Simple asymptotic, Without CC                       
2.  Simple asymptotic, With CC                          
3.  Score method, Without CC                            
4.  Score method, With CC                               
5.  Binomial-based, ‘Exact’                             
6.  Binomial-based, Mid-p                               
7.  Likelihood-based                                    
8.  Jeffreys                                            
9.  Agresti-Coull,z^2/2 successes                       
10. Agresti-Coull,2 successes and 2 fail                
11. Logit 

The codes are available at

http://jiangtanghu.com/docs/en/CI_Single_Proportion.sas 

It is a purely SAS/Base(data step and SQL) approach. I prefer data steps because it can be seamlessly incorporate into production work which looks like:

/*r is the observed number of events/responders in n observations;*/

%macro _Wald(r,n,alpha=0.05);

      p=&r/&n;
      z = probit (1-&alpha/2);
      sd=sqrt(&n*p*(1-p)); *Standard Deviation ;
      se=sd/&n;            *standard error;
      me=z*se;             *margin of error;
      p_CI_low = p-me;
      p_CI_up  = p+me;
     p_CI=compress(catx("","[",put(round(p_CI_low,0.0001),6.4),",",put(round(p_CI_up,0.0001),6.4),"]"));
%mend _Wald;

data test;
      input r n;
     %_Wald(r,n);
      keep r n p p_CI;
datalines;
81 263
15 148
0 20
1 29
29 29
;  
run;

proc print data=test;run;

But when simulation required (in methods 6-7), some SQL clauses pop up. Admit that It makes the codes less elegant. In this situation, matrix operation language such as SAS/IML or R will do a better job.

Note that in SAS 9.2, PROC FREQ can produce the following five intervals(method 1, 3, 9 ,8, 5):

Wald                  
Wilson                
Agresti-Coull         
Jeffreys              
Clopper-Pearson (Exact)

For the five intervals in SAS 9.2, a good reference is Confidence Intervals for the Binomial Proportion with Zero Frequency by Xiaomin He and Shwu-Jen Wu:

www.pharmasug.org/download/papers/SP10.pdf

Also, an equivalent R version of the eleven methods by Abteilung Kriminologie is also available at

http://www2.jura.uni-hamburg.de/instkrim/kriminologie/Mitarbeiter/Enzmann/Software/prop.CI.r

Such R repository is also a good resource for SAS users. I almost “translate” R implementations directly to SAS for this post.

Dive into CDISC Express (1): Introductory

Dive into CDISC Express (2): Create a New Study

Dive into CDISC Express (3): Navigate mapping file

Dive into CDISC Express (4): Data manipulation techniques

A more friendly PDF version of these all CDISC Express series is also available in

http://jiangtanghu.com/docs/en/CDISCExpress.pdf

The following tasks, such as generating SDTM domains and define.xml, need just some clicking button work in CDISC Express using a well designed mapping file. Few words needed due to the software.

5. Step 3 of 6: Validate mapping file (Validate_Mapping_File.sas)

It would be back and forth to design, validate then modify and re-validate the mapping file. And sure finally, you will get all the work done, at least no syntax error (how to avoid semantic errors is upon your domain knowledge). A validated mapping file, named mapping.xls will be copied to …\ doc\Mapping file – validated version\ from the working file, tmpmaping.xls. You will see

The corresponding log file in folder …\ log\

A report in …\results\Mapping Validation\, named Mapping_validation.html

Also the temporary datasets in …\tempdata\ and …\temp\:

6. Step 4 of 6: Generate SDTM datasets (generate_SDTM.sas)

If mapping file is OK, generating SDTM domains is just clicking the button. After submitting the codes, you will see the log file, reports, SDTM datasets and temporary datasets in corresponding folders:

7. Step 5 of 6: Validate SDTM datasets (Validate_SDTM_Domains.sas)

The outputs files of validating SDTM datasets are all located in C:\Program Files\CDISC Express\SDTM Validation\:

8. Step 6 of 6: Generate Define.xml and xpt (generate_Definexml.sas)

Get the final define.xml file and SAS transport files (.xpt):

9. Recommended reading and action taken

For a quick start and deep understanding, you could read the official documentations in the following sequence:

C:\Program Files\CDISC Express\documentation\FAQ.htm

C:\Program Files\CDISC Express\documentation\Quick Start.htm

C:\Program Files\CDISC Express\documentation\User guide.htm

A video tutorial would be also helpful:

C:\Program Files\CDISC Express\documentation\videotutorial.htm

A must-read conference paper, An Excel Framework to Convert Clinical Data to CDISC SDTM Leveraging SAS Technology by Sophie McCallum and Stephen Chan of Clinovo, supplies a wonderful discussion the architectures of CDISC Express:

http://www.lexjansen.com/pharmasug/2011/ad/pharmasug-2011-ad08.pdf

Of course, you do not need to review all the pages then get the confidence to use the software. Learn by doing and just dive into it. There is an opportunity for your practicing and you will also have a change to win an iPad2 from Clinovo’s CDISC Express Contest:

http://www.clinovo.com/cdisc/game

The due day is July 15^th and I already submitted my work. That’s fun.

Dive into CDISC Express (1): Introductory

Dive into CDISC Express (2): Create a New Study

Dive into CDISC Express (3): Navigate mapping file

4.3 Data manipulation techniques in CDISC Express

CDISC Express supplies relative rich sets of data manipulation techniques assembling with SAS languages used for data mapping. Following is a not limited listing and I will keep it updated.

4.3.1 Reference one dataset

A raw dataset name appear in “Dataset” column indicate a “set” operation in SAS.

All dataset options can be used when referencing a dataset, such as

siteinv(drop=invcode)

siteinv(rename=(invcode=inv))

siteinv(where=(invcode ne “”))

You can also reference an external dataset. You should incorporate the external file in spreadsheet with name beginning with an underscore, “_”, and “_visits” in this case:

Then you can use it in any domains needed, e.g., TV domain:

There is a macro %cpd_importlist used to import the external dataset, “_visits”. Again, this macro roots in C:\Program Files\CDISC Express\macros\function_library\.

Using a macro call to re-sharp or modify an input dataset offers great flexibility referencing data. We will also discuss the benefits later on.

4.3.2 Assignment

You can assign a number, string and a dataset variable with any valid SAS functions to a SDTM domain variable in “Expression” column.

Sometimes a temporary variable needed for later calculation. You can produce such temporary variable in “Dataset” column with an assignment in the “Expression” column just similar with any other domain variables. Two differences: first, such temporary variables named begin with an asterisk, “*”; second, all temporary variables will not be included in the final domain. Once created, such temporary variables can be used for any other expressions.

There are three special symbols used in “Dataset” column of CDISC Express. Asterisk, “*” indicates a temporary variable, while other two are

Tilde, “~” : indicate a variable used for supplemental domain (SUPPQUAL).

Number sign, “#”: indicate a variable used for comments domain (CO).

Another symbol, at sign, “@”, used in “Expression” column, indicated referencing a variables produced before:

In this case, “AGEU” uses “AGE” as input, while “AGE” is calculated before. “@AGE” just indicates the dependency. In concept, it looks like the “calculated” option in SAS PROC SQL:

proc sql ;

select (AvgHigh – 32) * 5/9 as HighC ,

(AvgLow – 32) * 5/9 as LowC ,

(calculated HighC – calculated LowC)

as Range

from temps;

quit;

4.3.3 Match-merging

We already got a math-merging example before. If “all” appears as a dataset in the “Dataset” column, all the previous datasets should be merged first for later processing by the common key specified in “Merge Key” column. If no key assigned, patient ID is used by the system.

CDISC Express also supports two types of join, inner join and outer join (left, right, full) using data steps. The implementation has slightly difference with standard SQL, but the ideas are same.

We add a new column, “Join”, usually beside the “Merge Key” column.

There are two values for “Join”, “O” or “I” while “O” stands for “outer join” and “I”, “inner join”. A join indicator “I” equals a dataset option “in=” in action while “O” means no. Use the above as illustration, the corresponding SAS codes behind look like

data temp;

merge demog(in=a) siteinv(in=b);

by sitecode;

if b;

run;

This is so called “right outer join”. The combination of “I” and “O” in these two datasets can perform all the four types of join, one inner join and three outer join:

As we could see, if no “Join” column specified, CDISC Express will perform inner join by default.

So far CDISC Express cannot support multiply merge keys. For example, the following file is illegal currently:

Dataset	Merge Key
arm	siteid, grpno
armdescri	siteid, grpno

The developer Romain indicated that such enhancements would be raised to the next round of product road map and he also proposed a work around. To use multiple keys for merging, we can create a temporary variable holding such multiple keys as a concatenation then this temporary variable can be used as a single merging key.

4.3.4 Concatenating

Above we discussed lots about “merge” operation in CDISC Express. This section dedicated for “set” operation. We already know how to “set” one dataset for referencing, but how to “set” multiple datasets, i.e, “Concatenating”?

Symmetrically, an “all” appears in “Dataset” column indicating merging operation, an “all (stack)” indicates concatenating operation:

The above file can be also translated to SAS codes for better understanding:

data height;

set vtsigns(where=(height ne .));

VSTESTCD="HEIGHT";

VSTEST ="Height";

VSORRES =put(height,best12.);

VSORRESU="cm";

VSSTRESC=put(height,best12.);

VSSTRESN=height;

VSSTRESU="cm";

run;

data weight;

set vtsigns(where=(weight ne .));

VSTESTCD="WEIGHT";

VSTEST ="Weight";

VSORRES =put(weight,best12.);

VSORRESU="kg";

VSSTRESC=put(weight,best12.);

VSSTRESN=weight;

VSSTRESU="cm";

run;

data vs;

set height weight;

STUDYID =study;

DOMAIN =&domain;

USUBJID =%CONCATENATE(_variables=study sitecode patid);

VSSEQ =%SEQUENCE();

. . .

run;

4.3.5 Transpose

Clinical SAS programmers do lots of transpose operation to re-sharp the raw data to fit the CDISC standards. Currently there is no explicit guide in CDISC Express on how to transpose, but this is not the end of story.

There are two types of transpose:

Type I: from a wide dataset (more variables, less observations) to a long dataset (less variables, more observations), e.g. transposing a one-row-per-subject datasets to a multiple-row-per-subject dataset

Type II: from a long dataset (less variables, more observations) to a wide dataset (more variables, less observations), e.g. transposing a multiple-row-per-subject dataset to a one-row-per-subject datasets

As good practices, in SAS we always use data steps with “output” statement to perform type I transpose and use PROC TRANSPOSE for type II. Although CDISC Express doesn’t support transpose operation in an explicit way, at least you can perform type I transpose and surprisingly we already saw it before!

Just back to section of concatenating. The example is taken from C:\Program Files\CDISC Express\studies\example2\.

We can see the input data vtsigns is typical wide table (more variables, less observations):

And the final domain VS is a typical long table (less variables, more observations):

So obviously, such concatenating operation just did a wonderful type I transpose, from a wide table to a long table! More often, the compact SAS codes for type I transpose look like:

data vs;

set vtsigns;

if height ne . then do;

VSTESTCD="HEIGHT";

VSTEST ="Height";

VSORRES =put(height,best12.);

VSORRESU="cm";

VSSTRESC=put(height,best12.);

VSSTRESN=height;

VSSTRESU="cm";

output;

end;

if weight ne . then do;

VSTESTCD="WEIGHT";

VSTEST ="Weight";

VSORRES =put(weight,best12.);

VSORRESU="kg";

VSSTRESC=put(weight,best12.);

VSSTRESN=weight;

VSSTRESU="cm";

output;

end;

. . .

run;

4.3.6 All others: use macro!

Now we discussed almost all the common data derivation techniques in programmers’ daily life and the corresponding implementation in CDISC Express. At least we have one question unsolved: how to perform type II transpose, i.e. from a long table to a wide table?

It would be an open question for the developers of the application. But we can also solve this problem in current framework: use macro, customized macro. You can use macros in “Expression” and “Dataset” column. Macro used in “Dataset” column returns a dataset, while macro in “Expression” column returns series of string: that’s the basic structure you should consider when customize your own macros. For more, you can reference the macros in C:\Program Files\CDISC Express\macros\function_library\. For example, &concatenate used in “Expression” column; &cpd_importlist in “Dataset” column.

So it would be convenient to create temporary datasets using macros imbedded type II transpose operation in “Dataset” column. Every thing SAS can do, you can also implement it in CDISC Express. Just use macros, in “Expression” and “Dataset” column accordingly.

The raw data varies according to trial design and clinical data capture system and procedures. It is impossible and impractical to anticipate the CDISC SDTM converter such as CDISC Express to map all the data just clicking a button. The introducing of CDISC Express doesn’t keep programmers away. It just keeps most of the trivial work away from programmers’ daily life and let them more concentrated on creative work and be productive and efficient.

Following would be the close of such pages.

Dive into CDISC Express (1): Introductory

Dive into CDISC Express (2): Create a New Study

4. Step 2 of 6: Generate mapping file

Generating template (blank) mapping file only needs pieces of effort by submitting generate_mapping_template.sas. The toughest one is to fill it with mapping rules according to specified study.

4.1 Get the blank template mapping file (generate_mapping_template.sas)

To get the blank template mapping file, just fill the one line of macro call in generate_mapping_template.sas:

%createmapping(filespec=SDTM_Specs_3_1_1.xls, Dom=CM AE TV, req=YES, perm=YES, exp=YES);

Also, you can specify SDTM implementation version, 3.1.1 or 3.1.2. For domains (&Dom), DM, CO and SUPPQUAL will be created automatically; you should list others accordingly:

SDTM 3.1.1: SV CM EX AE DS MH DV EG IE LB PE QS SC VS TV TI XD SU XR XS XE TR (Total: 22)

SDTM 3.1.2: AE CE CM DA DS DV EG EX FA IE LB MB MH MS PC PE PP QS SC SE SU SV TA TE TI TS TV VS (Total: 28)

You should also choose the “CORE” variable (REQUIRED, PERMISSIBLE and EXPECTED) by triggering &req, &perm, and &exp to “YES” or “NO”. Note that

REQUIRED and EXPECTED variables must always be included (req=YES, exp=YES);

PERMISSIBLE variables included if needed (perm=YES or perm=NO)

Submit generate_mapping_template.sas and you can get a blank template mapping file tmpmapping.xls in C:\Program Files\CDISC Express\temp\.

Copy it to C:\Program Files\CDISC Express\studies\CLINCAP\doc\Mapping file – working version for example used for study “CLINCAP” and then fill all the blank columns (it NEEDS efforts!).

If this mapping file passes the validation process, a final version named mapping.xls will be copied automatically to C:\Program Files\CDISC Express\studies\CLINCAP\doc\Mapping file – validated version\ for later processing.

Note that if you already have some validated mapping file for other studies, it would serve as a good start rather than using the blank template from the scratch.

4.2 Navigate mapping file

Let’s take a look at the “real” worked mapping file for a demo study first, in C:\Program Files\CDISC Express\studies\example1\doc\Mapping file – working version\.

The first sheet is a welcome dashboard:

Then StudyMetadata sheet, a XML metadata specification used to generate define.xml. you need only add some information in “Values” column:

The FORMAT sheet:

Such format structure is similar with the one we export the format from a format catalog using

proc format library=library cntlout=format_out;

run;

In most production environment, programmers get formats from clinical data management group. If the entire formats are assigned into proper libraries (work or library), you don’t need to export such formats into this spreadsheet. Of course in the format sheet, you can type some customized format.

A typical domain sheet (AT LAST!) that needs efforts and our understanding of the software, DM for example:

From the ‘Dataset’ column, three raw datasets from C:\Program Files\CDISC Express\studies\example1\source\ needed to map into DM domain, demog, siteinv and eligassess. Note that you can use any data step options such as drop=, rename=, where= for the input datasets.

At the last of ‘Dataset’ column, “all” indicates that all the previous datasets mentioned above should be merged together for final processing.

In the ‘Merge Key’ column, ‘sitecode’ is designed to datasets demog and siteinv which means demog and siteinv should be merged by the common key, ‘sitecode’.

As we mentioned, all the previous datasets should be merged at last. But there is no common key settled in the ‘Merge Key’ column. It is a common rule: if no key specified for merge, USUBJID is used by default.

The third column is ‘CDISC variable’, which list all the needed variables according to implementation version. An important note: you do not need to implement all the variables according to the order as they appear in the blank template mapping file. In the previous blank file, “AGE” in DM domain is ordered in Line 12, but in this working file, “AGE” is calculated in the second last order. The variable order of final DM domain will be as same as the blank one.

It makes sense in practice. For example, the sequential variable, e.g. AESEQ is ordered after USUBJID, but you can only get the sequential number when all other variables well done. So SEQ variables are always computed in the final stage in a working mapping file.

“Expression” column specify the mapping rule from raw datasets to SDTM domains. Assignments, expressions and macro calls (rooted in C:\Program Files\CDISC Express\macros\function_library\) are allowed in this column and most of them are straightforward. We will discuss more in the following section.

Sum up, we can “translate” this mapping sheet to SAS codes for better understanding of CDISC Express architecture:

data tem1;

    set demog;

    STUDYID=study;

    DOMAIN =&domain;

    USUBJID=%CONCATENATE(_variables=study sitecode patid);

    SUBJID =patid;

RFSTDTC=%D_RFST(_dataset=trtinf,_date=trtinfdt,_key=patid,_ivrsds=ivrs,_ivrsdt=randdat);

RFENDTC=%SORTLOOKUP(_dataset=disc,_variable=fupdat,_key=patid,_sort_variable=fupdat,_keep=last);

    SITEID =sitecode;

    INVID =invcode;

   BRTHDTC=%FORMAT(_variable=brthdat,_format=yymmdd10);

   SEX =%GENDER(_gender=sex);

   RACE =%D_RACE();

   ETHNIC =upcase(ethnic);

   COUNTRY="USA";

    DMDTC =%FORMAT(_variable=formdat,_format=yymmdd10);

   ARMCD ="";

    ARM ="";

run;

 

data tem2;

    merge tem1 siteinv(drop=invcode);

    by sitecode;

    INVNAM=trim(lname)||","||fname;

run;

 

data dm;

    merge tem2 eligassess;

    by patid;

  AGE =year(infcondt)-year(brthdat)-(month(brthdat)>month(infcondt))-(month(brthdat)=month(infcondt) and day(brthdat)>day(infcondt));

AGEU=%CONVERTIF(_if_variable=@AGE,_if_value=.,_then_value=, _else_value=YEARS);

run;

Following will be some explore the data manipulation techniques in CDISC Express, such as merge, transpose.

Dive into CDISC Express (1): Introductory

3. Step 1 of 6: Create a new study (create_new_study.sas)

Open create_new_study.sas in C:\Program Files\CDISC Express\programs\, you can see only one line of a macro call:

%addnewstudy(studyname=my new study);

Just assign a study name to the macro variable, &studyname, e.g, “CLINCAP”:

%addnewstudy(studyname= CLINCAP);

Submit the codes, you can find a folder named “CLINCAP” with the same structure as the two demo studies imbedded in this application(example1 and example2) in C:\Program Files\CDISC Express\studies\, see(the left and right panels are folders and files before and after the execution of create_new_study.sas. The following the same):

Folder ‘doc’ is used to hold the mapping files;

Folder ‘log’ used to hold log files generated by following macro calls, such as generating SDTM domains;

Folder ‘results’ and its subfolder will hold all the outputs, such as define.xml, SAS transport file, validation reports and SDTM datasets;

Folder ‘source’ holds all the clinical raw data used as inputs for SDTM domains;

Folder ‘tempdata’ holds all the temporary datasets generated by following macro calls.

Also, a configuration file named CLINCAP_configuration.sas put in C:\Program Files\CDISC Express\programs\study configuration\. This file is used to set some study level parameters, such as lab and toxicity specifications (details in C:\Program Files\CDISC Express\specs\Lab specs\).

Two versions of SDTM implementation guides are supported by CDISC Express, CDISC SDTM Implementation Guide Version 3.1.1 and Version 3.1.2. You can find the corresponding specification files in C:\Program Files\CDISC Express\specs\SDTM specs\:

SDTM_Specs_3_1_1.xls

SDTM_Specs_3_1_2.xls

The choosing of SDTM implementation version is also coded in the configuration file, in Line 41:

%LET SDTMSPECFILE=SDTM_Specs_3_1_1.xls;

Version 3.1.1 is used by default. You can also choose Version 3.1.2 if needed:

%LET SDTMSPECFILE=SDTM_Specs_3_1_2.xls;

Assign a study name and choose a SDTM implementation version. That’s all needed in step 1. Let’s take few minutes to navigate the software. CDISC Express is a set of macros and Excel files. It is important to know the file structure first:

C:\Program Files\CDISC Express\

├─documentation :FAQ, Quick Start, User Guide

├─macros

│ ├─ClinMap :system level macros

│ └─function_library :study level macros

├─programs :"action taken" macros

│ ├─study configuration :study parameters configuration

├─SDTM Validation :For validation of SDTM domains

│ └─study1

├─specs :specification files

│ ├─Excel engine :ExcelXP tagset file

│ ├─Lab specs :lab and toxicity

│ ├─Mapping validation :validation rules

│ ├─SDTM specs :hold two versions of SDTM implementation

│ └─SDTM Terminology :SDTM codelist(including NCI terminology)

├─studies

│ ├─example1

└─temp :hold temporary data not specified to any studies

As we already got, all the “action taken” programs such as create_new_study.sas are located in C:\Program Files\CDISC Express\programs\. In create_new_study.sas, one macro is called, %addnewstudy, which is in C:\Program Files\CDISC Express\macros\ClinMap\.

Note that in C:\Program Files\CDISC Express\macros\, there are two sets of macros in different folders:

C:\Program Files\CDISC Express\macros\ClinMap\: this folder holds all “system” level macros used by the application only. No modification encouraged.

C:\Program Files\CDISC Express\macros\function_library\: macros used for mapping among studies. You can also create you own macro in this folder. The application imbedded macros also documented in user guide.

Following will be the most important part, mapping file.

Really she is the strangest creature in the world, far from heroic, variable as a weathercock, “bashful, insolent; chaste, lustful; prating, silent; laborious, delicate; ingenious, heavy; melancholic, pleasant; lying, true; knowing, ignorant; liberal, covetous, and prodigal”— in short, so complex, so indefinite, …

            –Virginia Woolf, The Common Reader, First Series (1925)

I don’t know if Ernest Hemingway is still one of the recognized dominant writers in colleges English education. At least once  a time he WAS. In an extreme form, he used only nouns and verbs to construct sentences.

In my personal English education(as Second Language), admittedly that there is also an absence of adjectives. It is just wonderful, nice, great, cool, weird, awesome,  and all in all, everything is OK or not OK, good or not good. In writing, my sentences lack of tone and shades. I write only technical articles in English and people can often well manage the so-called technical borings when acquiring information, knowledge, and opinions.

In reading when I try to just read for the sake of reading itself, I also find it is difficult to dig into pure literature pages where rich adjectives assembled heavily. I only read smoothly technical papers. So when I happened to have a paper book of Woolf and also loaded a corresponding public domain e-book in Kindle, I read the most intensive instances of adjectives ever. It is really totally different experiences.

There would be three types in any languages. For English:

in the top, pure literature, Shakespeare-like;

middle, which could be called the universal or international English; it is be the dominate English among nations in business, technology and even academia; most of the popular writers also utilize such sort of English to extent their global reputation;

bottom, the street language, slangs, talk-show.

I penetrate the English through the middle like almost all of the ESL learners. It is the most convenient and effective way in a very short run. But for a leap in long run, some friends just suggest that I should go down the street or climb up to the top. Ok I am on the way. Virginia Woolf is the first stop and I keep the first notes.

CDISC Express is the first and only practically usable open source CDISC converter I even met. I wrote a post a month ago when I first tested it with great interests and reported some issues to its fix system. Then I also had the great opportunity to discuss the software via email with its core developer, Romain Miralles. This post is just my personal notes on how to use and dig into the software, and will be best serve as a working documentation. You can return to me for any questions and comments.

By the way, there is an opportunity for your practicing and you will also have a change to win an iPad2 from Clinovo’s CDISC Express Contest:

http://www.clinovo.com/cdisc/game

The due day is July 15^th and I already submitted my work. That’s fun.

1. Download and Installation

You can get CDISC Express for free in

http://www.clinovo.com/cdisc/download

It is a window application and will be installed by default in

C:\Program Files\CDISC Express\

After installation, this path will be coded as a macro variable &CDISCPATH in the following six SAS files which are all located in C:\Program Files\CDISC Express\programs\:

create_new_study.sas

generate_Definexml.sas

generate_mapping_template.sas

generate_SDTM.sas

Validate_Mapping_File.sas

Validate_SDTM_Domains.sas

The macro variable reads as

%LET CDISCPATH = C:\Program Files\CDISC Express;

If you change the destination folder at the installation stage, e.g., to D:\CDISC Express\, the value of the macro variable &CDISCPATH will be changed accordingly in the six files mentioned before:

%LET CDISCPATH = D:\CDISC Express;

Note that if you want copy the whole folder of files to another destination, you should at least manually change the value of &CDISCPATH in such six files or add some codes to capture the path accordingly. From this point of view, the path setting of CDISC Express is not completely portable. Recommend that if you have such needs, just re-install the software in any destination you want. It will not write any records into registry and you can have many copies in one machine.

The following discussion assumes the software roots in C:\Program Files\CDISC Express\.

2. Working Flow

You can follow all the 6 action steps one by one coded in

C:\Program Files\CDISC Express\programs\

1) Create a new study (create_new_study.sas)

Simple and easy. Just assign a new study name in a macro call and run.

2) Generate mapping file (generate_mapping_template.sas)

This is the critical and most time consuming part. You should design mapping rules for every domain needed in Excel spreadsheets (the MAPPING FILE). If done, all other tasks, such as generate SDTM datasets, SAS transport files, define.xml and validation, can be well done by just clicking buttons

.

3) Validate mapping file (Validate_Mapping_File.sas)

For validating the mapping file, just click the button. As mentioned, the most important work is designing mapping file. It would be back and forth to design mapping file and validate it.

4) Generate SDTM datasets (generate_SDTM.sas)

If mapping file is OK, click the button.

5) Validate SDTM datasets (Validate_SDTM_Domains.sas)

Click the button.

6) Generate Define.xml (generate_Definexml.sas)

Click the button.

Following part will dig into the software step by step.

It’s wonderful. It’s awful. It’s wonderfully awful and awfully wonderful. –Frommer’s Las Vegas 2010

June at last! June, July and August (great thanks to French colleagues!) would be the best time to prepare a paper for next year’s SAS Global Forum (SGF, 2012 in Orlando, Florida). SGF2012 will hold at the “real” Walt Disney at Orlando against Las Vegas, the adults’ Walt Disney for SGF2011. Lots of children will be anticipated to register as guests for this meeting and I may contribute one:).

Before moving to 2012, I need to add some notes on 2011. It was my first time to present at SAS Global Forum, my first time to attend the Forum and even my first time out of China. First of all, I’d like to extend my special thanks to

Nancy Brucken of i3, section chair of Planning and Support who served as my key contact of the conference committee for all the coordination stuff

Duke Owen of Westat, my mentor of this year’s SAS Global Forum Presenter Mentoring Program. We had intensive communication via email, telephone and WebEx, see

01/25/2011 initial call.  review abstract and title
02/01/2011 final abstract submission, no call
02/08/2011 no call – Chinese New Year celebration continues
02/15/2011 call – review paper
02/22/2011 call – finalize paper for submission
02/25/2011 submission of pdf (on or before this date)
03/01/2011 call – first draft of slides, or at least the outline of your plan
03/08/2011 call – refine the slides and preview of what your script for presentation
03/15/2011 call – "live" presentation for Duke.   This can be done just on our pc’s or as a WebEx
03/22/2011 call – WebEx for a wider audience.  We can discuss who to invite and adjust time as needed.
03/29/2011 call – Finalize slides/script for you to send to Nancy Brucken
04/02/2011 Duke arrives in Las Vegas
04/03/2011 John arrives in Las Vegas (I think)
04/04/2011 Rehearsal room practice and final slides to Nancy
04/05/2011 Show Time:  Tuesday 3:00-3:20 pm    ,  drinks on me at the kickback
04/06/2011 It is all in the past
04/08/2011 Duke leaves Las Vegas

Last but not least, I also want to thank my boss and my company to support and sponsor me the trip. It is not a world-peace speech. Economy is tough worldwide and few SAS programmers in China could get the opportunity to attend the Forum. In my observation, even in US, relative fewer programmers in pharmaceutical company and other industries attended SGF2011 compared former years.

That might be also one of the reasons why in this year, SAS Institute had a stronger presence at Las Vegas. For me, it’ not bad. I had the opportunity to meet and talk with some of my former colleagues at SAS R&D Beijing and Cary (even when we’re all in Beijing, we have few chance to meet together). What’s more, SGF2011 had wonderful SAS Support and Demo Area and Hands-on Workshop fully supported by the Institute. It was definitely great opportunity to learn almost all parts of SAS system by talking with R&D staff directly. Admitted that I spent more time in the demo room instead of the user presentation room (I can read all the user papers after the Forum, but there is only one chance I can talk with R&D staff about the software itself!)

My personal presence at Las Vegas:

1.  Monday, April 4, attended the pre-conference training, Data Simulation for Evaluation Statistical Methods in SAS by Rick Wicklin. Rick is a famous blog writer on IML(The DO Loop), SAS’s matrix language. His blog is just as popular as Chris Hemedinger’s The SAS Dummy, a blog on SAS Enterprise Guide. It’s wonderful to talk with these two bloggers face to face!

2. Tuesday, April 5, I was invited to a panel discussion on SAS Business Visualization and Mobile Computing in a SAS BI Product Development Focus Group, hosted by 3 SAS directors, developer Justin Choy, product manager Pyan Schmidt and marketing Jo Fisher. Visualization and mobile computing (in iPad, iPhone, …) are two of most hottest topics in Las Vegas. This Focus Group also wanted to gather feedbacks from end users.

3. Tuesday, April 5, my presentation, Work Smarter Rather than Harder- Tools for Growing up A SAS® Programmer.

4. Wednesday, April 6, one or two seconds personal show up in Gregory Nelson and Neil Howard’s featured presentation in lunch, Revenge of the Semicolon People: Business Analytics at Work.  !!!!!

That time Duke and me were in the same table and Greg and Neil were just showing they slides. Suddenly I found a picture of Duke, Shane and me was in the presentation for SGF2011 after some historical pictures for 2010. It just flashed past but it was enough to excite around the table!

The core of CDISC Express is an Excel configuration file called Mapping File which defines all the metadata and mapping rules required by CDISC SDTM standard. Then a set of SAS macros is used to

• validate the SDTM mapping file
• generate SDTM datasets
• generate define.xml file
• validate the SDTM datasets

I found its architecture is simple, concise and powerful. When mentioned “simple”, I mean it is easy to understand and implement. It is still in its version 1.0, I wish following enhancements in the future according my personal user experiences:

• More web browsers supports for define.xml file and documentation
• More merge or set types
• Debug mechanism enhancement

look to the master,
follow the master,
walk with the master,
see through the master,
become the master.

The SAS Global Forum 2011 (Apr 4-7, Las Vegas) papers are now available on line:

http://support.sas.com/resources/papers/proceedings11/TOC.html

There are lots of Web 2.0 elements in this year’s SGF, including a Twitter hashtag #SASGF11, a Facebook account, a blog, a LinkedIn group, and of course, sasCommunity.org. I will give a talk at this conference (Tuesday, April 5, 05:30- 05:50 PM, Promenade Level, Neopolitan IV) to discuss how to get informed with little efforts from such multiple channels for SAS programmers using Gmail and Good Reader to create a personal information portal. That is part of my first SAS paper, Work Smarter Rather than Harder- Tools for Growing up A SAS® Programmer.

It will also be my first presence in SGF. Just can’t wait to walk with SAS masters there!

1. Google calculator

As Google fanatics, I first try to search the answer via Google:

Whoops, nothing interested returned! Type “170!” and get the output:

Why kinda things happened in this calculator? 171! is just equal to 171*170!.

2. Excel

Switch to Excel spreadsheet. Function fact(*) used:

Oo, interesting. The same.

3. SAS

Google and Excel may be the niche players in calculators’ family. Why not try to use some programming languages?

As a SAS programmer, my handy tool is SAS of course.

First, I use SAS data step with its build-in function fact(*):

data _null_;
    x=fact(170);
    y=fact(171);
    put x= y=;
run;

and I get

NOTE: Invalid argument to function FACT at line 49 column 7.
x=7.257416E306 y=.
x=7.257416E306 y=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.

Expected or unexpected? I don’t know how this fact(*) function is defined, and  try to define a function to calculate the factorials by myself. In SAS 9.2, you can use PROC FCMP(also available at 9.1.3 as a experimental procedure):

proc fcmp outlib = work.funcs.math ;
    function factorial(k) ;
        if k = 0 then return(1) ;
        z = k ; *preserve k ;
        x = factorial(k-1) ;
        k = z ; *recover k ;
        k = k * x ;
        return(k) ;
    endsub ;
quit ;

options cmplib=work.funcs ;

Use this self-defined function to get 170!

proc fcmp ;
    x = factorial (170) ;
    put x = ;
run ;

The FCMP procedure returns

x=7.257416E306

Try to calculate 171! ?

proc fcmp ;
    y = factorial (171) ;
    put y = ;
run ;

Just get the overflow error. The interaction stops at 170!:

ERROR: An overflow occurred during execution in function ‘factorial’ in statement number 7 at   line 10 column 1.
       The statement was:
    1      (10:1)    k = (k=171) * (x=7.257416E306)

The above function definitions use recursion. Recursion may have some limitation on efficiency. We could try the loop without recursion. SAS/IML doesn’t support recursion. Let SAS/IML to the court:

proc iml;
    start factorial (n);
        fact=1;
        do i=1 to n;
        fact=fact*i;
        end;
        return (fact);
    finish factorial;

    x= factorial (170);
    print x;

    y= factorial (171);
    print y;
quit;

Again, I get 170!

    x
7.257E306

and a overflow error for 171!

            y= factorial (171);
ERROR: Overflow error in *.

Turing, Von Neumann and Tony, what happened?

4. R

When SAS failed, lots of voices pop up: use R! OK, Rction!

> x=factorial(170);x
[1] 7.257416e+306
> y=factorial(171);y
Warning message:
In factorial(171) : value out of range in ‘gammafn’
[1] Inf

5. C++

I don’t want to lose my patience. Think C++(use both recursive and non-recursive methods):

#include <iostream>
using namespace std;

double factRecursive(double num);
double factNonRecursive(double num);

int main()
{
    cout<<endl;   
    cout<<"Recursive: the factorial of 170 is "<<factRecursive(170)<<endl;
    cout<<"Recursive: the factorial of 171 is "<<factRecursive(171)<<endl;
    cout<<endl;  

    cout<<"NonRecursive: the factorial of 170 is "<<factNonRecursive(170)<<endl;
    cout<<"NonRecursive: the factorial of 171 is "<<factNonRecursive(171)<<endl;
    cout<<endl;   

return 0;
}

double factRecursive (double num)
{
    if (num==0)
        return 1;
    else
        return num*factRecursive(num-1);
}

double factNonRecursive (double num)
{
    double fact=1;
    for (double i=2;i<=num;i++) fact *=i;
    return fact;
}

Unfortunately, same story once more:

Well. The story’s played out like this. It may be not the limitable of the language but the machine. I check which is the largest numbers my computer supports:

#include <iostream>
#include <cfloat>

using namespace std;

int main()
{
  cout<<"maxinum double value of machine: "<<DBL_MAX<<endl;
  return 0; }

maxinum double value of machine: 1.79769e+308

Now everything’s in the open. The factorial of 170 is about 7.257416e+306. 171! is too big to be supported by my PC.

(Note: I put these codes in http://codepad.org, a online complier. if you don’t have any C++ complier in your machine, you can see the codes and outputs in:http://codepad.org/xnneavsw  and http://codepad.org/3FeEC9t2)

6. WolframAlpha

Struggled for hours, I turn to WolframAlpha computing platform. It returns the factorial of 171 AT LAST:

   AT LAST we know the factorial of 171 has 310 digits.

7. Windows Calculator

I try to use Windows build-in calculator. Amazing, it is powerful:

8. Python

Return to programming language.  First, I defined a function(recursive version) in Python and then use its MATH library:

>>> def factorial(n):

    if n==0:

        return 1

    else:

        return n*factorial(n-1)

>>> factorial(170)

7257415615307998967396728211129263114716991681296451376

5435777989005618434017061578523507492426174595114909912

3783852077666602256544275302532890077320751090240043028

0058295603966612599658257104398558294257568966313439612

2625710949468067112055688804571933402126614528000000000

00000000000000000000000000000000L

>>> factorial(171)

1241018070217667823424840524103103992616605577501693185

3889518036119960752216917529927519781204875855764649595

0167038705280988985869071076733124203221848436431047357

7889968548278290754541561964852153468318044293239598173

6968996572359039476161522785581800611763651084288000000

00000000000000000000000000000000000L

>>> import math

>>> math.factorial(171)

1241018070217667823424840524103103992616605577501693185

3889518036119960752216917529927519781204875855764649595

0167038705280988985869071076733124203221848436431047357

7889968548278290754541561964852153468318044293239598173

6968996572359039476161522785581800611763651084288000000

00000000000000000000000000000000000L

Amazing, Python beats up C++!

(to be continued :

Too Big to Be Accurate(2): Approximation

)

The Arizona State University is maintaining a repository of feature selection, including original documentations, Matlab packages and user guide for the following popular algorithms so far:

BLogReg
CFS
Chi Square
FCBF
Fisher Score
Gini Index
Information Gain
Kruskal-Wallis
mRMR
Relief-F
SBMLR
T-test
SPEC
see http://featureselection.asu.edu/software.php

A R package, FSelector, is also useful for step-by-step studying. This package covers:

Filters:
*cfs
*chi-squared
*consistency
*correlation
–linear.correlation
–rank.correlation
*entropy.based
–information.gain
–gain.ratio
–symmetrical.uncertainty
*OneR
*random.forest.importance
*relif-F

Wrappers:
*best.first.search
*exhaustive.search
*greedy.search
–backward.search
–forward.search
*hill.climbing.search

Recently I read a paper on the comparisons of SAS EM, SPSS Clementine and IBM Intelligent Miner on their decision tree and cluster technology:

Decision Tree Induction & Clustering Techniques in SAS Enterprise Miner, SPSS Clementine, and IBM Intelligent Miner – A Comparative Analysis by Abdullah M. Al Ghoson, Virginia Commonwealth University

The output is not that surprising. SAS EM plays better in performance, functionality and auxiliary task support but worse in usability.

Here are few comments on decision trees implementations in SAS EM and SPSS Clementine based on my own experiences. Some advises for beginners are also supplied.

There are four nodes in SPSS Clementine to supports four trees algorithms respectively: C5.0, Classification And Regression Trees (CART),  Quick, Unbiased, Efficient Statistical Tree(QUEST) and Chi-squared Automatic Interaction Detector(CHAID),  which are most famous and popular in decision trees family.

Note that CART(R) is a registered trademark of California Statistical Software, Inc., and is licensed exclusively to Salford Systems, San Diego, California. So SPSS Clementine uses C&R Tree as name.

In SAS EM, there is only one decision tree node:

The algorithms behind this node is called SAS tree algorithms, which incorporate and extend the four mentioned before. Just change the settings in decision tree node, you can get the trees you want.

Obviously, SAS tree algorithms is superior than the separated ones in SPSS Clementine for expansibility and flexibility. But at the other hand, the complexities increase. For a newbie user of SAS EM, he/she may wonder which trees he/she is training. A SPSS Clementine users just picks up a node and says: OK, I am now training a CART or CHAID.—he/she would communicate with others more smoothly.

Regardless of the industry application, I think this is the educational benefit of SPSS Clementine. Since almost every data mining book introduces decision trees by separated algorithms(such as ID3/C4.5/C5.0, CART, QUEST, CHAID, . . .), the beginners using SPSS Clementine as instructional tool may get the clear ideas about the algorithms one by one. Once he/she get the full understanding of the differences among tree algorithms, he/she would train trees in SAS EM more comfortable.

What’s more, SPSS Clementine supplies rich supporting documentations for beginners and self learners , such as Tutorial, User Guide, Algorithms Guide, Node Reference. The official documentations of SAS EM 5.x and 6.x are relatively poor. Yes there is a good SAS Help and Documentation for SAS EM 4.3 including Getting Started with Enterprise Miner. EM4.3 is a traditional AF application but EM5.x and above are Java client incorporated in SAS analysis platform(they are totally different!). For EM5.x and above, only installation guides and a plain reference are available.

SAS Institute may have its own marketing strategies. No rich references available, the Institute DOES offer rich training programs in data mining and Enterprise Miner application. Wooo, the big-budget purchasers of SAS EM can also afford the trainings.

DATA steps also have an underlying structure, an implicit, built-in loop. You don’t tell SAS to execute this loop: SAS does it automatically. Memorize this:

DATA steps execute line by line and observation by observation.

Programmers could memorize the statement above and apply it well in their programming practices, but still find it hard to get the vivid idea about the so called implicit built-in loop. –This post would make it easy.

The following will show an explicit loop example in C++. Note that you do not need to know any about C++ to get the idea. Suppose that a data file data.dat in D driver holds three numbers

1
2
3

The question is how to (read and) print out these numbers and their sums.  Following is the C++ approach (just read the bold session):

#include <iostream>
#include <fstream>
using namespace std;
int main()
{
int x;
int sum=0;
ifstream inFile;
inFile.open(“d:data.dat”);
inFile >> x;

while (!inFile.eof( ))
{
cout<<x<<endl;
sum = sum + x;
inFile >> x;
}

inFile.close( );
cout << “Sum = ” << sum << endl;
return 0;
}

There is an explicit loop in these C++ codes: while (!inFile.eof( )) .  While it is not at the end of infile, the codes above will keep print out the numbers and do the accumulation. The final output is

1
2
3
sum=6

The following SAS codes produce the exactly same output:

data _null_;
infile “d:\data.dat” end=eof;
input x;
sum+x;
put x;
if eof then put sum=;
run;

Note that SAS codes do not need an explicit loop to reach to the end of file. There is a so called implicit built-in loop.<the end>

The mechanic, who wishes to do his work well, must first 

sharpen his tools.

 --Confucian Analects. BOOK XV.WEI LING KUNG.CHAP.IX.

My paper Work Smarter than Harder-tools for growing up a SAS programmer was accepted by SAS Global Forum 2011. It would be my first time to attend SAS user group conference worldwide. The draft version is available at

http://jiangtanghu.com/docs/en/SGF2011_JiangtangHU(draft).pdf

Welcome for any comments.

The SGF2011 will be held at Caesars Palace in Las Vegas, Nevada. The interesting thing is that, as far as I know, Caesars Palace in Las Vegas was also the host for SUGI 1978(Jan 30-Feb 1, 1978). looking forward to seeing my SAS gurus in Caesars Palace next year, a place of history.

Yesterday I finished my project, wrote a Haiku to colleagues worldwide to say happy new year, then closed my desktop, said goodbye to colleagues still in office:

In December my baby born

my project deliveries followed on

Now my computers shutdown

do until(6 Jan 2011) I will be OOO

Please take blessings from John

Happy New Year!

http://saslist.com/  in Chinese, maintained by sxlion, also the owner of a SAS information site,  http://saslist.net/

http://sas-x.com/   in English, maintained by Tal Galili, also the owner of R blogs aggregator, http://r-bloggers.com/

I try to express in a very symmetrical way. What’s more (and interesting), these two aggregators share a same WordPress template. My blogs, in Chinese and in English, are also the members of these aggregators respectively.

I view this is the first wave for SAS programmers to embrace the Web2.0 world. If interested, you could also add your SAS blogs in

http://sas-x.com/add-your-blog/

             To Iterate is Human, to Recurse, Divine. 

      –by L. Peter Deutsch, creator of Ghostscript. (I also love his saying “The best thing about a Boolean is even if you are wrong, you are only off by a bit”)

I learned this quote from Tony Barr, designer and developer of SAS (the Statistical Analysis System).  He also used the Recursive Descent method to develop SAS complier. Now Tony is working for AMOR (A Model of Reality). AMOR is the self-defining system for modeling ideas and concepts (or human knowledge).—Recurse again: in AMOR, the type is defined by the type of type, and, the type of type is also defined by the type of type of type . . .

To help to understand the DIVINE part of recursion, Tony gave two examples from the Bible:

1. The Golden Rule is recursive.

Do unto others as you would have them do unto you. (see Matthew 7:12, or Luke 6:31)

2. God indentify himself in a recursive way.

Moses asked God for his name. Then God said to Moses, “I AM THAT I AM.” (see Exodus 3.14)

I’d like add two items (from a programmer’s perspective rather than Biblical Theology):

3. Recursion at the very start

In the beginning was the Word, and the Word was with God, and the Word was God. (see John 1.1; also see here)

4. The Trinity recursive way

First, there is only one God in Christian doctrine. God the Son duplicates the nature of God the Father in every way exactly, and so God the Spirit. And what’s about God the Father? see above.

ID	Age	agegrp
001	1	1
002	4	2
003	5	2
004	5	2
005	2	1
006	4	2
007	5	2
008	2	1
009	9	3
010	8	3

and the rules:

age<4,           group 1

4<=age<6,     group 2

6<=age<10,  group 3

It is a very simple question and you could use the if/else statement without thinking:

data age;
input ID $ age;
datalines;
001 1
002 4
003 5
004 5
005 2
006 4
007 5
008 2
009 9
010 8
;

data age1;
set age;
if age<4   then agegrp=1;
else if age<6   then agegrp=2;
else agegrp=3;
run;
proc print;run;

or, you could use proc format to map data:

proc format;
value agegrp
low-<4=”1″
4-<7  =”2″
7-<10 =”3″
other =”"
;
run;

data age2;
set age;
agegrp2=put(age,agegrp1.);
run;
proc print;run;

And try to use logic operators. It is a ONE-LINE-OF-CODE approach:

data age3;
set age;
agegrp3=1+(age>3)+(age>5);
run;
proc print;run;

In SAS, a logic express returns to 1 or 0 according the true of false of the express. The logic behind the above statement is:

• age group is set to 1 at the beginning;
• if age>3, age group adds 1;
• if age>5, add 1.

This easy-to-follow logic can apply to other scenarios(flags, for example):

flag=value>50;

select value>50 as flag    (in proc sql)

• Two-sided confidence intervals for the single proportion: comparison of seven methods.
  Newcombe RG, Stat Med , Volume 17 , 8 (April 1998) pp.857-872
• Interval estimation for the difference between independent proportions: comparison of eleven methods.
  Newcombe RG, Stat Med , Volume 17 , 8 (April 1998) pp.873-890

These two widely cited papers evaluate 7 and 11 methods to calculate single proportion (paper A) and the difference between proportions (paper B), respectively. Further more, they were in the same issue of Statistics in Medicine(Volume 17, 1998), and, they were also cross referenced! So here is a live story about recursive referencing(thanks to Prof. Newcombe):

• An author writes two papers, A and B;
• Paper B is in the  bibliographic reference session of paper A;
• Paper A is also in the  bibliographic reference session of paper B;
• Paper A and paper B are in the same issue of a journal.

Note that for Prof. Newcombe’s two linked papers, it is common and acceptable in publication practices. Recently I used these two wonderful papers to learn CI calculation and this post just want to lead to the concept of “recursion”  (reference in reference in reference).

• SAS data step: the basic SAS; a generation IV programming language, similar with other procedural languages such as C.
• SAS Proc SQL: SAS’s implementation of standard SQL(SQL-92).
• SAS IML(Interactive Matrix Language): SAS’s matrix manipulation language(like R and Matlab).  SAS IML Studio also supply IMLPlus programming language(IML+), an enhanced version of IML.
• SAS SCL(SAS Component Language): build in SAS/AF software, an object oriented programming(OOP) language for applications development.

I am a heavy user of data steps and SQL and want to invest some bit on matrix manipulation. Although other wonderful languages available(such as R and Matlab), I found IML is a good choice for SAS programmers like me. It is well integrated within SAS system, and very important, almost all of the SAS Base functions and call routines are also supported by IML. Here some notes of IML 101(codes are self explanatory from a SAS Base point of view):

1. IML style of ‘hello world’

proc iml;
    text="Hello World!";
    print "IML saying" text;
quit;

and you got in output window:

IML saying Hello World!

Like Proc SQL, IML begins with “proc iml” , end with ”quit”, and every statements end with a semicolon. The key word “print” (an IML statement), just like “put” statement in data steps.

An enhanced version of Hello World:

options nocenter nodate nonumber;

proc iml;
    reset printall;

    text="Hello World!";
    print "in &sysdate. IML saying" text;
quit;

Some SAS global options added(“nocenter nodate nonumber”). The IML statement “reset", works like “options” statement to set some processing options within the IML(and you can guess the meaning of the options “printall”, just print all. . . it is your turn to check the output window).

A SAS system macro variable “&sysdate” is presented to encourage you to add any programming elements in SAS Base to IML.

2. How to create a matrix manually

Actually, we have already create a matrix named “text” in the previous hello-world codes. It is a character scalar(matrix with only one element). If we want to avoid the SAS data steps’ style of assignment,  we can use {} to enclose matrix elements:

a={“a”};  /*a _char_ scalar */
b={1};     /*a _num_ scalar*/

and a 2*3 matrix:

c={1 2 3,
      2 3 4}; /*2 rows, 3 cols*/

Commas(,) are used to separate rows.

3. How to create a matrix by functions

Some matrix reshaping functions:

a=I(3);     /*creates a 3*3 identity matrix*/
b=J(2,3,5); /*creates a 2*3 matrix of identical values*/
e=do(1,9,2); /*produces series, from 1 to 9, by increment 2*/
c=block(a,b);/*forms a block-diagonal matrice*/
d=diag(a);   /*creates a diagonal matrix*/
m=repeat(a,4,3); /*create a (3*4)*(3*3) matrix by repeating*/
n=T(b);   /*transpose*/

4. How to create a matrix by reading a SAS data set

proc iml;
    use sashelp.class;
    read all var _char_                    into class_char;
    read all var _num_                     into class_num;
    read all var {"Age" "Height" "Weight"} into class_num2;
    close sashelp.class;

    print class_char class_num class_num2;
quit;

Note that it is a good habit to close the data file after reading or using it(see Rick Wicklin’s Five Reasons to CLOSE Your Data Sets).

5. How to output a matrix to SAS dataset

proc iml;
    use sashelp.class;
    read all var _num_ into class_num;
    close sashelp.class;

    create work.class_num from class_num;
    append from class_num;
    show datasets;
quit;

6. How to format a matrix

/*version I: use matrix options*/

proc iml;
        use sashelp.class;
        col={"Age" "Height" "Weight"};
        read all var col into class;
        read all var{name} into row;
        close sashelp.class;

        print class[rowname=row
                    colname=col
                    format=5.2
                    label="test, label"];
quit;

/*version II: use mattib statement*/

proc iml;
        use sashelp.class;
        col={"Age" "Height" "Weight"};
        read all var col into class;
        read all var{name} into row;
        close sashelp.class;

        mattrib class rowname=row
                      colname=col
                      label="test, label"
                     format=5.2;
        print class;
quit;

/*version III: avoid hardcoding—use IML function and operations*/

proc iml;
        use sashelp.class;
        col=T(contents(sashelp,class)[3:5]);
        read all var col into class;
        read all var{name} into row;
        close sashelp.class;

        mattrib class rowname=row
                      colname=col
                      label="test, label"
                      format=5.2;
        print class;
quit;

(IML matrix operations: to be continued)

http://mathforum.org/library/drmath/view/52644.html

And the SAS codes(self-explanatory):

data root;
        /*question: find the square root of 4*/
        x=4; 

        /*first choose a rough approximation of sqrt(4);
        actually, you can start with any numbers*/
        y0=1;        

        count=0;/*init count number*/

        do until (w<1e-8); /*set a small tolerance error*/
                count=count+1;   /*accumulate count number*/
                y=(y0+x/y0)/2;   /*Newton’s formula*/
                w=abs(y-y0); /*if close, exit;*/
                y0=y;        /* otherwise, keep the new one*/
        end;

        output;
run;

The outputs:          

x    y0    count         w           y

4     2      6      2.2204E-15    2

After 6 iterations, Newton-Raphson(also called divide-and-average) gets an approximated square root. See what happed during each iteration compared the output generated by SAS function,sqrt():

data root;
        x=4; 
        y0=1;       
        count=0;
        do until (w<1e-8);  
                count=count+1;  
                y=(y0+x/y0)/2; 
                w=abs(y-y0);
                y0=y;
                if y =sqrt(x) then is_eq_sqrt="YES";
                else is_eq_sqrt="NO";
                output;
        end;
run;

Outputs:

x       y0         count        w               y     is_eq_sqrt

4    2.50000      1      1.50000    2.50000     NO
4    2.05000      2      0.45000    2.05000     NO
4    2.00061      3      0.04939    2.00061     NO
4    2.00000      4      0.00061    2.00000     NO
4    2.00000      5      0.00000    2.00000     NO
4    2.00000      6      0.00000   2.00000     YES     

What’s the difference between count 5 and 6 since their y values look the same? We reset the tolerance value to 1e-3 rather than 1e-8, and get the outputs:

x       y0      count       w          y              is_eq_sqrt

4    2.50000      1      1.50000    2.50000      NO
4    2.05000      2      0.45000    2.05000      NO
4    2.00061      3      0.04939    2.00061      NO
4    2.00000      4      0.00061    2.00000      NO      

The system get a faster convergence at an higher error rate, with an approximated  value little away from sqrt(4).

We should have a deep understanding of how SAS stores numeric values, which deserves a full session to discuss, to unearth the mystery. Some basic references:

• Numeric Precision in SAS Software
• Numeric Precision 101
• Dealing with Numeric Representation Error in SAS Applications

http://addictedtor.free.fr/graphiques/thumbs.php

As a SAS programmer, I also love Robert Allison‘s SAS/Graph Examples and Michael Friendly‘s SAS Graphic Programs and Macros (the only two SAS graph galleries available on website maintained by users):

http://robslink.com/SAS/Home.htm

http://www.datavis.ca/sasmac/

I used to be a casual user of SAS Graph. I’d like to add Graph into my toolbox in the following month: half of it due to the encouragement of the two SAS graph gurus, the other half SAS 9.2’s exciting enhancement on graphics.

p.s: the official SAS graph  gallery:

http://support.sas.com/sassamples/graphgallery/index.html

implication:       if       P then       Q (P—>Q)

inverse:            if not P then not Q (-P—>-Q)

converse:         if       Q then       P (Q—>P)

contrapositive: if not Q then not P (-Q—>-P)

contradition:    if       P then not Q (P—>-Q)

Mathematically or logically speaking, if the implication statement holds, then the contrapositive holds, but the inverse does not hold, i.e., if P then Q, then we can get if not Q then not P, but we can not get if not P then not Q.

That’s all logics needed here and Let’s turn to the ambiguous English in daily life. James R. Munkres of MIT gave an example in Topology (2nd edition, 2000, P.7):

Mr. Jones, if  you get a grade below  70 on  the final, you are going to flunk  this course.

We adapt it in a logical implication form:

Mr. Jones, if P then Q, where

P: you get a grade below  70 on  the final

Q: you are going to flunk  this course

Considering the context, we can also get that the inverse holds: if you get a grade above er or equal to 70, then you are going to pass this course(if not P then not Q ).

Question: when do statistical programming, what types of logics you use?

Answer: Not all mathematically. see

if score<70 then grade="flunk";  *if P then Q;
else                    grade="pass";  *if not P then not Q;

• SAS data steps approach:                        import and export
• SAS XML Libname engine:                         import and export
• SAS ODS XML statement(ODS MARKUP):   export
• PROC CDISC:                                            import and export
• SAS XML Mapper:                                      import
• SAS CDISC Viewer:                           as if  import

The SAS CDISC Viewer and PROC CDISC procedure are some bit toys, and the rest really work. The Perl Regular Expression(PRX) approach is also presented to export and import XML data.

A simple demo. First, use FILE and PUT statements to generate an XML file:

data _null_;
    file "export.xml";
    put ‘<?xml version="1.0" encoding="windows-1252" ?>’;
    put ‘<ROWSET>’;
    put ‘<ROW>’;
    put ‘<text> Welcome to CDISC Interchange 2010 China </text>’;
    put ‘</ROW>’;
    put ‘<ROW>’;
    put ‘<text> We are in Shanghai! </text>’;
    put ‘</ROW>’;
    put ‘</ROWSET>’; 
run;

Then read the whole XML file to SAS dataset:

data import0 ;
    infile "export.xml" dsd missover truncover lrecl = 1024;
    input line $1024.;
    if line = ” then delete;
run;

Third step, extract the information you want(the text between <text> and </text> tags) using  Perl Regular Expression:

data import (keep = line );
     retain queName ;
     retain line ;
     set import0;     

     /*use PRX to capture the structure of XML data;*/
     If _n_=1 then do;
            queName=prxparse(‘/^\<text\> /’);
     end;
     queNameN=prxmatch(queName,line);

    /*use PRX to remove the XXML tags;*/
     if queNameN>0 then do;
        rx1=prxparse("s/<.*?>//");
        call prxchange(rx1,99,line);
        output;
     end;     
run;

The logic of PRX approach to process XML data is very simple and can be easily modified according to your needs:

• complicate and utilize the PRX codes to capture the hierarchical structure of XML data.
• remove XML tags and output the information to SAS dataset.

• Level I:   focus on techniques
• Level II:  focus on using data
• Level III: focus on meaning

I found the three conceptions could be easily interpreted as the three kinds of state of learning and using statistics based on my personal experience:

• State I: focus on techniques—As a student of Economics and (then) Software Engineering, I needed some statistics techniques to support my study on data mining and machine learning. So I invested a lot on some fancy skills such as logistic regression, decision tree,  neural network and even support vector machine in graduate school and SAS R&D(as an intern). In most time, I just thrown data to the models and checked their functionality and feasibility(Wula-IT-WORKS! or Oops-crash-again). When looking back, I’d just have to say these techniques were toys played in labs.
• State II:  focus on using data—Now I worked as a SAS programmer(also titled as statistical analyst) in pharma. All data are not just the rows and columns in the tables. They are SUBJECTS! Statistical techniques are used carefully to display and interpret the story of real world. Why the denominator is 999 while 1000 subjects were recruited in this trial? Because subject 001-127, male, 23 months of age,  discontinued due to his father’s wish and opinion!
• State III: focus on meaning—Peter Petocz and Anna Reid concluded that, regarding the MEANING conception of statistics, “statistics is an inclusive tool used to make sense of the world and develop personal meanings.” The last state of any realms ideal, is always sounded like philosophy or religion. That may be a life in a statistical way or style(If got it, I would change my blog’s title as From a Statistical Point of View^).

—————-some notes on non-statistics—————————-

1. three kinds of state of Chan

• just mountain
• isn’t mountain
• still mountain

2. three realm ideal of Wang Guowei

• heaven is integrated with man:

Last night the west wind shriveled the green-clad trees,

Alone I climb the high tower

To gaze my fill along the road to the horizon.

• knowledge is integrated with practice

My clothes grow daily more loose, yet care I not.

For you am I thus wasting away in sorrow and pain.

• feeling is integrated with scenery

I sought her in the crowd a hundred, a thousand times.

Suddenly with a turn of the head [I saw her],

That one there where the lamplight was fading.

Reference:

Peter Petocz and Anna Reid. On Becoming a Statistician—A Qualitative View. International Statistical Review(2010), 78,2,271

WANG Guowei. Ren jian ci hua. translated by Adele Austin Rickett.

This blog, “From A Logical Point of View”, is not supposed to be owned by a logician. Actually, the book, From A Logical Point of View, is a collection of logical and philosophical essays by W.V.Quine(1908-2000), an American philosopher and mathematician.

A story about this book. From A Logical Point of View, was a calypso song by Robert Mitchum, an US actor, composer and singer. Quine enjoyed this music and used it as his new book, which is Quine’s best seller. Now I love this book and give the name to my blog from a logical point of view^.

0. Install Oracle Database 10g Express Edition

Fast (and free) to download, easy to deploy and simple to admin–for learning and testing purpose, Oracle Database 10g Express Edition (Oracle Database XE, a mini version of Oracle Database 10g Release 2) are strongly recommended:

0.1 Download it at its homepage(206MB);

0.2 Install it following default settings;

0.3 Unlock accounts for HR and SCOTT. In a windows DOS prompt, using the following scripts:

sqlplus sys/YourSysPassword as sysdba

alter user HR account unlock

alter user HR identified by YourHRPassword

alter user SCOTT account unlock

alter user SCOTT identified by TIGER

Or you can accomplish these tasks within the Oracle web application:

http://localhost:8081/apex

1. Connect Oracle using SAS libname engine

libname SCOTT oracle user=”SCOTT” password=”TIGER” path=’xe’ ;

Note: xe is the default path for Oracle Database XE.

2. Connect Oracle using SQL Procedure Pass-Through Facility

proc sql ;
connect to oracle as orcl
(user=”SCOTT” password=”TIGER” path=’xe’);

select *
from connection to orcl

(

SELECT …

) ;

disconnect from orcl;

quit;

Note: 1) In this approach, Oracle, instead of SAS, processes the SQL statement (i.e., you use the more powerful and flexible Oracle SQL syntax instead of SAS Proc SQL procedure for querying. For more, see SAS Doc).

2) Your Oracle SQL codes should be placed in the RED blocks, and end without a semicolon(;):

select *

from emp

3) Only basic Oracle SQL statements (select, create table, . . .)can pass through this facility.

3. Load SAS datasets to Oracle database

proc copy in=sashelp
out=scott;
select class;
run;

4. Misc: Some differences between Oracle SQL and SAS Proc SQL

4.1 Table aliases

Oracle: from employees a;

SAS: from employees a; or

from employees as a;

4.2 Column aliases

Oracle: don’t use single quotation marks(‘’).

select job_id as job, job_id job, job_id as “job” , job_id as ‘job’

SAS:

select job_id as job, job_id “job” , job_id ‘job’, job_id job

4.3 Literal Character Strings

Oracle: Date and character literal values must be enclosed within single quotation marks(‘’).

SAS: Use both single and double quotation marks.

4.4 Order the null value

Oracle: Null values are displayed last for ascending sequences and first for descending sequences.

SAS: Null values are displayed first for ascending sequences and last for descending sequences.

4.5 Nesting Group Functions

select max(avg(salary))
from employees
group by department_id

SAS log:

ERROR: Summary functions nested in this way are not supported.

Optional approach for SAS:

select max(avg) as max
from(
select avg(salary) as avg
from employees
group by department_id )

4.6 row number(_n_)

Oracle:

select rownum
from employees

SAS:

select monotonic()
from employees

Note: monotonic() is an undocumented function of SAS, seehttp://www2.sas.com/proceedings/sugi29/040-29.pdf

see, http://support.sas.com/kb/26/134.html

• Slider chart indicator:
  • SAS program version
  • stored process version
  • portlet version
• Slider chart dashboard:
  • SAS program version
  • stored process version
  • portlet version
• Bullet graph indicator:
  • SAS program version
  • stored process version
  • portlet version
• Bullet graph dashboard:
  • SAS program version
  • stored process version
  • portlet version
• Dial meter indicator:
  • SAS program version
  • stored process version
  • portlet version
• Dial meter dashboard:
  • SAS program version
  • stored process version
  • portlet version
• Bar chart indicator:
  • SAS program version
  • stored process version
  • portlet version
• Bar chart dashboard:
  • SAS program version
  • stored process version
  • portlet version
• Telesales dashboard:
  • SAS program version
  • stored process version
  • portlet version
• Web marketing analysis dashboard:
  • SAS program version
  • stored process version
  • portlet version

You would also like, Santa’s Dashboard:
http://jiangtanghu.blogspot.com/2008/12/santa-and-sas-again-santa-dashboard.html

• Black-Scholes model，the traditional stock options pricing model, see Fischer Black and Myron Scholes (1973) ;

• Black model，extension of Black-Scholes model on futures option (also called Black-76 model)，see Fischer Black(1976);

• Garman-Kohlhagen model，pricing model on foreign currency options，see Mark Garman and Steven Kohlhagen(1983) ;

• Margrabe model，on exchange options，see William Margrabe(1978).

• BLACKCLPRC: calculates the call price for European options on futures, based on the Black model.

• BLACKPTPRC: calculates the put price for European options on futures, based on the Black model.

• BLKSHCLPRT: calculates the call price for European options, based on the Black-Scholes model.

• BLKSHPTPRT: calculates the put price for European options, based on the Black-Scholes model.

• GARKHCLPRC: calculates the call price for European options on currencies, based on the Garman-Kohlhagen model.

• GARKHPTPRC: calculates the put price for European options on currencies, based on the Garman-Kohlhagen model.

• MARGRCLPRC: calculates the call price for European options on exchange assets, based on the Margrabe model.

• MARGRPTPRC: calculates the put price for European options on exchange assets, based on the Margrabe model.

For more，see SAS9.2 online help，Functions and CALL Routines by Category: Financial：
http://support.sas.com/documentation/cdl/en/lrdict/59540/HTML/default/a000245860.htm

Note: A good web site for options pricing with different models, http://www.montegodata.co.uk/

A look back at 2008 and some crystal ball predictions…, byTammi Kay Geroge, from SASBlog*/

Major Data Warehousing Events of 2008 (and Predictions for 2009), by Michael Schiff, from TDWI,

Major Data Warehousing Events of 2008:

• Everyone had an appliance story
• Industry consolidations continued
• The recessionary environment encourage further BI developments
• Open source grew

Predictions for 2009:

• Further industry consolidation(Informatica by HP, SPSS by SAP)
• Cloud computing will come down to earth
• Open source growth will accelerate
• The IT world will become greener
• Major emphasis on solutions rather than tools and technology

BusinessIntelligence Tools: Year in Review，by Cindi Howson, from BeyeNetwork

Top Virtualization Trends for 2009, by John Suit, from ZDNet

Surround the Warehouse: Prediction for 2009 , by Neil Raden, from IntelligentEnterprise

1. Leading Companies See Value in SAS and Teradata Partnership
2. SAS and Teradata Unveil Advantage Program to Bring Powerful In-Database Solutions and Services to Customers
3. SAS and Teradata Enter into Strategic Partnership
   

In BI industry, the pure players such as SAS, Teradata and Microstrategy, need to demonstrate their indispensable values against the megavendors, IBM (acquired Cognos), SAP (acquired Business Object), Oracle (acquired Hyperion) and Microsoft. Teradata is solely focused on enterprise data warehouse. SAS, dominating in business analytics (e.g. advanced statistics and data mining), will check and balance the BI industry due to the private-hold structure. SAS and Teradata Advantage Program partnership, includes wide business lines, such as Analytics, AML (Anti-Money Laundering), Credit Risk, Enterprise Intelligence and Optimization Services. I think It’s a effective way to learn from each other in mutual emulation and counterbalance the concentration market.

(santaDashBoard.png, With permission by Mr. Robert Allison)

Merry Christmas again. SAS marketing staff started up an interesting Christmas campaign on how Santa operates his workshop. Here is another wonderful work about Santa’s Dashboard, created by SAS senior R&D staff, Robert Allison.

Robert is a master of graphics and visualization. You can view his SAS/Graph examples in the following link:

http://robslink.com/SAS/Home.htm

How to purchase toys and goodies with a balance of costs and profits? and how to deliver them more efficiently?

There are lots of questions in the list of Santa, CEO of Santa’s Workshop. SAS’s marketing staff held a very creative champion for the coming Christmas. You can watch the interview with Santa in youtube.com, or read the success story about Santa, Santa’s Secret: Magic? No. It’s SAS(R) Business Analytics.

Merry Christmas.

• Ability to think abstractly, identify patterns, and generate ideas and solutions
• Understanding of when and how to escalate issues or needs
• Understanding of and ability to delivery the appropriate level of detail needed for each task
• Interest in exploring and understanding new concepts and topic areas
• Emotionally invested in the work
• Ability to learn by shadowing stakeholders
• Ability to clearly articulate technology in terms stakeholders can understand
• Understanding of the organizational culture and its impact on processes and projects (this one seems obvious, but the latter phrase is more profound than might seem at first glance)
• Ability to drive a decision analysis and selection process
• Ability to recognize patterns in requirements and categorize them appropriately

What’s more, there are some suggestions by Rajan Chandras himself:

• Know the organization’s external environment: its competitive position, current state of the industry, geographical & social factors, etc.
• Know the organization’s internal environment: its financial position, organization culture, IT maturity, etc.
• Adapt to the needs (your language, dress etc.), but be yourself. Imperfect, yet genuine, is fine; falsity comes through easily, and will destroy your credibility in no time.

No doubt, no boss can reject such a perfect analyst. But I’m afraid these standards are suitable for every professionals. That is to say, they create a model to explain everything. It is too universal to be served as a good filter to select the most proper analysts. She or he may more marketable in any other business line.

I know I might hold some academic doctrines, so I have interest to monitor any effort to try to forecast stock prices using data mining techniques. Some links from a popular data mining blog , Data Mining Research, are listed as follows:

• Stock Prediction using Decision Tree
• Stock Picking using Data Mining: Parameter Tuning

First prompts are silent.
Subsequent prompts loud and clear.
Now all prompts are heard.

Poem from R&D staff?
Yes. Rhyming sonnets were shakespeare-like complex;
they wrote Japanese haiku, showed as above.

The SAS R&D staff should complete some paper work in defects system before changing a code. They use informal descriptive language(HAIKUUU!) in the early stage. Chris Hemedinger, a senior software engineer at SAS, collected some haikus in his blog to show the humor side of SAS R&D staff. It’s interesting to cite one of the most famous haikus by Matsuo Bashō for comparison:

Old pond
a frog jumps
the sound of water