Vilno Table Programming Language White Paper

Here is a link to the
"Vilno Table Programming Language White Paper"

It is a .PDF file stored in a Google Documents area :

https://docs.google.com/open?id=0B2oCqHJ9cxhCNkp2T0dnMFFBdlE

Rather than viewing it in the Google Documents area, download it and open it after downloaded, the viewing within Google Docs of a .PDF file might show a couple of pages as blurry.

The first 8 table examples in the appendix are the most intuitive way to get a feel for how this language works.

Vilno Table, Real Examples of How It Works

These are actual examples actually produced by the software product (not yet version 1.0, but very functional, as you can see, the technology works).

Because the tables produced by the software are not so easy to put into a blog post, I am using a link to a document in "Google Docs":

https://docs.google.com/document/d/1O21VX4MCwcs2u_1eCwx5An-Di9NDld0-GCp0yHUYZLA/edit

So click on it, and you will see the examples from the appendix of the white paper.

Each example is 2 pages: the code (usually less than 15 lines), and the table that it produces.

I am having difficult choosing landscape/portrait for each page, so every page, unfortunately, is landscape. When you upload to Google Docs from a word document on the hard drive, it throws some stuff out, like page-by-page choices for landscape/portrait

More Examples of Vilno Table

Here I show a few examples of rather simple statistical tables. Vilno Table has an enormous worker productivity advantage over SAS for complex, customized, picky statistical table requests. But it's also easy to use for simple tables. The more complex and picky the statistical table request is, the greater the advantage that Vilno Table has over SAS (and Excel) in terms of worker productivity. (Therefore, for very simple requests, the difference in productivity is less).

Most of these examples use only summary statistics (the last example will be a one-way ANOVA). Each example has only one available dataset and essentially one analysis (Vilno Table can produce a table using multiple data sources and different analyses, but these examples are simple).

This is code that describes the available datasets. Here, there is only one dataset, the PATINFO dataset :

inputdset asc a/PATINFO site patid trtgrp gender race happy weight age ;

( trtgrp means "Treatment Group", happy is a categorical outcome variable).

This is just a two-way frequency table:

denom trtgrp ;

col trtgrp*( N % ) ;

row happy ;

This is the same thing, but add a chi-square p-value in the upper right corner:

( I add a model statement, and add the word "pvalue" to the column statement )

denom trtgrp ;

model chisq(trtgrp*happy) ;

col trtgrp*( N % ) pvalue ;

row happy ;

This is assorted summary statistics , for certain demographic subgroups:

col all gender*race [age<65] ;

row N mean(weight) std(weight) median(weight) ;

Okey-dokey, let's try one slightly more advanced example, a one-way ANOVA (well, technically "age" is a continuous covariate). Lm stands for "Linear model".

model lm( weight ~ trtgrp -1 + age ) ;

col trtgrp*est all*( est_pw("60mg"-"Placebo") pval_pw("60mg"-"Placebo") ) ;

row all ;

What you get is least-square mean for every treatment group, and just one pairwise comparison.

Let's make the row and column headers easier to read with:

label trtgrp "Treatment Group" est "Least Square Mean"

all "60mg vs Placebo" ""

est_pw "Difference" pval_pw "P-value" ;

To the dataset description code at the top, for the linear model, I'll need to add:

categorical trtgrp ;

continuous weight age ; (this extra description code was not needed for the summary statistics tables)

The above 4 examples are a lot simpler than most of the examples in the appendix of the Vilno Table Programming Language. The above examples do not show the full flexibility of Vilno. They show it's easy to produce tables that should be easy.

Just one more example, before I go, the next table is several frequency cross-tabulations, each one with a chi-square p-value on the right side, stacked vertically (each row section has a different row category, but the column category is always treatment group.

Several categorical tests, with N and % , and the p-value in the right column, like a baseline characteristic table. Again, most of the important stuff is in the model statement, the column statement, and the row statement.



model chisq(thisrowcat*trtgroup*N) ;


col (trtgroup all)*(N %) pvalue ;


row gender race age_group ;



This is fairly similar to table A1 in the Vilno Table white paper. When the current beta version has cr-modifier statements added to the parser, a later version will be able to put continuous and categorical statistics into the same column, but not yet.
(A baseline characteristic table crams a lot of stuff into the same page, so N and % must share the same column with mean and std. deviation).

AE table in 11 lines of code: Why it is possible.

Here I explain the 11 lines of code that produce the AE table in more detail, to clear up some misunderstandings:

Here is code for table A3 in the Vilno Table Programming Language white paper, an AE table with the typical bells and whistles:

title "Table A3: AE table, with chi-square (75-patient dbase)" ;

directoryref a="/home/robert/test" ;

inputdset asc a/patinfo3 patid trt 1*(patid) ;

inputdset asc a/advevt3a patid bodysys prefterm ;

thing pat uniqval(patid) a/patinfo3 ;

n~n(pat) ;

printto "/home/robert/test/outp01" ;

denom trt ;

model chisq(thisrow?*trt*n) ;

col (trt all)*(n %) pvalue ;

row all have(a/advevt3a) bodysys*(all nothave prefterm) ;

(Please note: patinfo3 and advevt3a are names of datasets, the patient_info dataset and the adverse events dataset.)

(Please note: trt is the variable name for treatment group (here 3 groups), bodysys and prefterm are, obviously variable names for body system and preferred term.)

As I've already said, the best way to explain and learn this language is to focus attention on 3 lines of code: the model statement, the column statement, and the row statement (which are the last 3 lines of code in the above example). It is by reading these 3 lines of code that you can see what the statistician is asking for. Most of the analysis logic is in these 3 lines. If you do many statistical tables off of the same database, the other lines of code require little modification.

But to dispel some confusion, I will go through all 11 lines. Naturally, I begin with the model, column, and row statements:

Look at the column statement:

col (trt all)*(n %) pvalue ;

You have an N and % column for each treatment group (there are 3 treatment groups in table A3). You have an N and % column for all patients together. In the right-most column, you have a p-value from a simple categorical test.

Look at the row statement :

row all have(a/ae_dataset) bodysys*(all nothave prefterm) ;

Going through each piece of the row statement in order, what it does:

row

all -> row with grand total N for each group

have(a/ae_dataset) ->

row with number of patients with any AE at all (no matter what body system or preferred term)

bodysys*( -> for each body system, a set of rows that include the following:

all -> row for number of patients with AE for this body system

nothave -> row for number of patients who do NOT have an AE (for this body system)

prefterm -> for each preferred term, number of patients with AE

This AE table is a famous example. It is not a beginner's example for two reasons: for each body system there is a row for number and % of patients who do NOT have an event ; and the request for a categorical p-value for EVERY row in the table (for every preferred term). For these two advanced issues, two advanced keywords in the programming language are used: NOTHAVE and THISROW? respectively.

In a very simple example, the model statement would look like this:

model chisq(gender*race*N) ;

The column and row statements, which are at the center of this programming language, use a "visual tree" method: you write a syntax that becomes a tree data structure, this tree data structure becomes a visual display at the top of (and left side of) the statistical table. The terms in the column and row statement are called TABLE FACTORS, which become nodes in the visual tree.

THE TREE DATA STRUCTURE IS A COMBINATION OF DETAILS THAT SHOW WHAT THE STATISTICIAN IS ASKING FOR.

With elementary tables, the table factors are: categorical variable names, boolean expressions (such as [age<65]), names of statistics( such as N % mean std(std.deviation) pvalue est(estimate for least square mean) fvalue(F-statistic) and so on), plus the ALL keyword.

With more advanced tables, more advanced keywords in the programming language might be used: HAVE NOTHAVE THISROW? THISROWCAT .

Lines 1,2, and 7 are simple, obvious, and nothing new.

Lines 3,4,5,6 are input dataset description code. That includes the thing statement, which is a new innovative feature: you have to define WHAT you are counting, and later specify WHERE you are counting. If you want to count people AND count event records (using ae_record_idnum if in the dataset), you could have TWO thing statements for the same table (I've seen it asked for(tracking before database lock), but it's not that common a request).

The keyword "inputdset" means: here is an input dataset which I describe here (obviously there is a PATINFO dataset and an AE dataset, as you expect). I'll get into data source identification later, but if you have two datasets, it's pretty obvious to the compiler.

The denominator statement is "denom trt ;" . Is that fairly obvious? It actually is part of the table paragraph, with the last three lines.

This is not a macro library, it is a new language, and it can produce a huge variety of different tables. (I still haven't shown you the linear model example, table A2, that is going to blow your mind!).

Robert Wilkins

Produce Statistical Tables Really Fast

So what is this new product?

It's an implementation of a new statistical programming language, now up and running in beta mode, that produces statistical tables really fast , with a really small effort in coding.

Since the software is in beta mode, I've already run some examples:

Typical AE table (N % p-value(chisquare) , each body-system, patients who do not have (each bodysys), and of course body-system*preferred-term) ->

That's 12 lines of code, baby! (SAS, you need around 600!)

Typical baseline characteristic ( but no continuous ), 3 categorical variables going down, one category across(treatment group) , N % p-value(chisq) ->

That's 9 lines of code , man! (SAS, hard pressed to get it under 400)

Linear model example: pairwise (t-statistic and p-value, ends up being 4x4, in the leftmost section of paper), model coefficients( t-statistic and p-value, one column 4x1 ), and F-statistic (with p-value, in the rightmost part of page) ->

That's 11 lines of code , man (SAS, hard to say, probably 400 lines of code, or more)

This is not a macro library. This makes use of tree data structures in a much more sophisticated way than SPSS/SAS/SQL.

The current version is on Linux and outsources(gives work to do) to vilno data transformation and R. This DOES NOT mean that the product forces the end user to look at vilno code or R code. A version that outsources to SAS/BASE and SAS/STAT is not yet in beta mode

Five times faster than what???

Develop complex statistical tables, upon request, five times faster than the SAS programming language.

Also, develop these tables much faster, with less chance for error , than with a spreadsheet, with a better record of what you did if you come back to it three months later.

Also, much more expressive and flexible for sudden requests and changing requirements than any language macro system can be.

That's what five times faster means.