Thoughts On Multi-Site QA: Best Practices & Pet Peeves

 There are 2 primary audiences for QA. The first is local site implementers (SDMs) who want confirmation/proof that their implementations pass at least minimal muster, or else know exactly where their implementation has gone wrong.  The second audience is potential users of the data, who are similarly looking for assurance that the data at the sites will meet minimal expectations and generally be usable for a given study.  In addition, the second audience will often want to see basic descriptives on the various fields, in order to see e.g., just how much gender identity data you have.  These statistics are also good for forming opinions on how homogenous/harmonious the implementations seem to be across sites. If one site shows an average of 7 ambulatory visits per member per month, and everybody else is around .4, then that’s maybe worth looking into.

Generally, you will want to create 2 separate reports in a QA process, one for each audience.

The presumption is that QA failures will either be fixed or documented as not fixable. So new fails should be actionable by the implementing site (unless they are known issues that have been explained on the issue tracker. If an issue isn’t worth following up (e.g., 3 stray records out of 8 million) don’t put a fail in the report.

Best Practices/Pet Peeves

1. QA Programs go hand-in-hand with the specs to define a given data area. Expect to hear from SDMs running your program about differences of opinion/interpretation of the specs. It may take some dialogue & iteration to settle on checks and tolerances. You should list the objective checks & those tolerances on your collated report along with (at least) a summary of the results of those checks at the sites.
2. Don't make me hunt for the table listing checks & pass/fail results--that should be the first thing I see, right at the top of the output.
3. I should be able to ctrl-f for the word 'fail' and instantly know if I have any fails & if so what they are.
4. Be thoughtful about what reporting output you create for the local implementer/user. In particular, thou shalt not spit proc contents output into a report.  If you want a full CONTENTS on my dset, run that out to an output dataset and consume that in your collated report. Don’t forget that you can produced most anything you want once you get results back from the sites.
5. Make your collation process and collated report creation process as streamlined as possible, and favor publishing the results immediately after you get updated results.
1. You should be able to receive updated results at any time, and have only to unzip contents into a folder, run a single program, and then upload the resulting collated report.
2. Give Sites immediate credit for having fixed fails
3. While it would be wonderful to always have the time to pore over results and compare site implementations & follow-up with individual sites, it is also not crazy to basically crowd-source this work.
6. Produce the smallest number of output datasets as will hold the data you need.  Each one has to be checked for PHI before it can be returned.  Consider concatenating multiple similar datasets together.
7. Save off close-to-raw records that violate a check and point the user to that dset in the part of the report that lists the fail, so they don't have to write their own code to (dis)confirm the issue & see some samples data to start figuring out how it got into the dset.
8. Graphics are way better than tables.
9. Format values to make them interpretable to ppl who have not memorized the spec.

Provider specialty data--wrangling and using

In medical research data harvested from insurance/care data it's often important to know something about the providers that appear in the data. In particular we frequently want to know what the type of provider is--were they a:

• Nurse Practitioner
• Physician's Assistant
• Social Worker
• Technician

and so forth.  It's also frequently important to know what that person's specialty is--for example:

• Transplant surgery
• Pediatrics
• Oncology

Trying to make this data available for research--particularly in hybrid organizations like HMOs, can be a serious challenge.  In general, we'd like to have a lookup table that lists every provider's type and specialty as it existed at the time of an encounter, say. Unfortunately, our sources don't often provide that information, and so we wind up with a pile of types and specialties for each provider that can be confusing to use.

Reasons a single provider might have more than one record in a provider type/specialty table

1. They are (or were) truly qualified in multiple specialties
1. simultaneously (say, internal medicine and oncology), or
2. they re-trained over the course of their career (e.g., started out as a registered nurse, became a nurse-practitioner)
2. We drew data for the same individual from multiple systems, and the information is inconsistent because:
1. one or all of the systems are wrong about the type/specialty
2. one system has a bad identifier & they're not really the same individual
3. Some of the data is just bad (e.g., indicates additional specialties not actually merited).

Unfortunately our sources (National Provider Identifier Registry, our various Electronic Medical Record systems, our claims processing systems) don't often (ever?) give guidance as to the time progression of various types and specialties. Lacking that information, we are powerless to convey it to users.  So what can we do?

Document the scope of the problem

Looking just at our EMR data, a little more than 94% of providers have just a single listed specialty.  If that's typical, while probably not negligible, it's not a show-stopper either.

Provide Users All The Data We Have, warn them of the issue, and give whatever guidance we can

So if you happen to know from your analysis that the NPI registry data is particularly dodgy, say, let them know that in cases of ambiguities, that they should prefer records coming from sources other than NPI.

Similarly, it's often useful to look at other contextual clues on particular encounters--what procedures/diagnoses bear that provider's identifier? Social workers don't perform Radial Keratotamy surgery, right--so if that's what looks to be happening, one of those fields is lying.

Department (when it's available and not imputed from provider specialty) can also be an important clue.

For Heavy-Hitters, Analyze and Special-Case Particular Providers' Data

In our provider table I like to include off-spec fields that give the number of encounter & rx fill records that bear that provider identifier. That lets me optimize my bang-for-buck in terms of my precious time inspecting/analyzing/researching seeming ambiguities.  Providers that only appear on a handful of records do not merit the same attention that the ones with higher numbers do.

When we do find bad data, I am absolutely not above including lines like:

if provider = 'some_dude' and specialty = 'wrong specialty' then delete ;

in my code.

Maybe Someday--Do something AI-ish to flag inconsistencies or (dare I hope) try to construct time-periods

This should be possible to do in a centrally created Quality Assurance type program, but it would probably be a ton of work and is not feasible at the moment.

"Semantic" Version numbering for VDW Specifications

Software Engineers Have A Good Version Numbering System

Software projects large and small will often adopt what's known as "semantic versioning", which you can read about in-depth here, but let me reproduce the summary real quick so you don't have to stop reading this: 

Given a version number MAJOR.MINOR.PATCH, increment the: 

1. MAJOR version when you make incompatible [interface] changes, 
2. MINOR version when you add functionality in a backwards compatible manner, and 
3. PATCH version when you make backwards compatible bug fixes. 

While the VDW specs are not in fact a software product they do share a lot of similarities.  Specifically,

• they change over time, and
• programmers rely on them to ensure compatibility of the code they write.

VDW Should Adapt This System

It seems to me this is a model we should adapt for our own uses. Specifically, and in the light of yesterday's Implementation Group meeting decisions:

The MAJOR version applies only to the set of specs in the aggregate.  Individual table specs inherit the MAJOR version (which means they will sometimes change even when particular table specs have not changed).

A Proposed Adaptation

Reasons to increment the MAJOR version include:

• We have a large number of individual spec changes come through in a very short period of time + a concerted effort to implement them (a la the version 2 -> 3 changes).
• The number of un-coordinated spec changes have accumulated sufficiently in the judgment of the VDW Operations Committee Lead's opinion that the new major version is warranted.

Since MINOR and PATCH levels only apply at the individual table level, I would propose we increment the MINOR level for changes that will break existing code, or are otherwise important (again in the judgment of the VOC lead).  For example:

• Change in the primary key.
• Addition or removal of one or more fields, or changing the name of a field.
• Significant expansion of the permissible values allowed for a field (for example the Virtual Care additions to the utilization specs)
• Change in a reference spec to which a field is tied (for example if we were to decide to update the language table to tie values from ISO 639-2 to  ISO 639-3)

By contrast PATCH levels are incremented for less-important changes like:

• Edits to descriptions, either at the field or whole-table level
• Correcting obvious typos.

This will of course entail adding PATCH numbers to the existing table specs, but doing so will give us the freedom to note changes while at the same time distinguishing between more-important and less-important ones.

VOC Lead Makes The Call

Change proposal authors should feel free to suggest incrementing/not incrementing MINOR and PATCH levels if the proposal is adopted, but the ultimate decision should be made by the VOC lead.

What do you think--can this work? How would you decide to handle particular types of changes?

Elements of a Distributed Research Data Network

As preparation for a panel discussion at Health Datapalooza 2018 on distributed data models, I wanted to jot down some thoughts on what I think you need to have a successful distributed research network. (Possibly these things are beside the point of that panel, but I've been wanting to write this for a little while. So here it is.)

Research Networks

What I mean by a 'research network' is a group of data-having organizations who intend to offer data access to remote users via some interface, in order to answer research questions. For me the paradigmatic example is of course the HCSRN. That's the organization I'm most familiar with, having been in on its VDW effort from pretty much the beginning in 2002.

So what do you need for a distributed research data network?

A Data Model

A data model is generally a collection of specifications for the tables that are available in the interface you're offering to remote users. It communicates to your users:

• What data they will find in your instance (e.g., lab results; insurance periods, pharmacy fills)
• How to refer to the data—e.g., table and field names.
• How the data is structured—what relationships the tables bear to one another.

At least as important, the data model should communicate to your implementers just what data has a place in the data model, what it needs to look like, and where in the scheme it should go.

To that end, good specifications are more than just those table and field names. They should also feature some verbiage that sets out what data does and does not belong in each table (e.g., the Race fields should hold the patient's self-reported race values only—do not use non-self-report sources for these fields). Optimally, they will also specify some natural key for the table—some combination of substantive fields that should uniquely identify a record (e.g., there should be one record per (MRN, RXDATE, NDC, and RXPROVIDER)). At some point this meta-description verges into full-on implementation guidelines, discussed below, but there should certainly be some general description to help users and implementers alike know what should be in the tables.

Important Adjuncts

Table specifications are the bare minimum you need for a successful DRDN. In addition to these, it is optimal to have:

1. Objective QA tests
2. Implementation guidelines
3. An authority to construe the specifications and answer user and implementer questions.

That last element is potentially very expensive of course—it may involve paying knowlegable humans to respond to questions and make recommendations/answer questions. If your network is very successful and can gain many implementers, then it's possible that an active community will spring up around it sufficient to keep it going without money changing hands (in much the same way that happens with some open-source software). But it would be unwise to count on that happening very early—much better to seed the ground by starting out with a paid group.

As the VDW effort matured I came to really appreciate the existence of objective QA tests as a supplemental source of data specification. Just the exercise of agreeing on what checks you should have (apart from 'all fields should exist & be the proper type') is incredibly valuable for surfacing the frequently diverse understandings and assumptions of your implementers.

Extra Credit

In addition to the above, it would be lovely to also include a sample implementation from widely available reference data (e.g., Optum, Medicare Claims). The OMOP folks have put together this implementation of an ETL process that creates OMOP data from CMS' synthetic public use file, for example.

Likewise, since the healthcare and health research fields are both ever-changing, it's good to have a process by which the DM can be amended.

Finally, in addition to a human-readable specification of your data model, it's always nice to include one or more flavors of SQL DDL scripts that create an empty set of the tables that constitute your data model.

A Query Processing Engine

This is what your users will use to manipulate the data from afar. Because they are doing so "from afar" it is crucial to be as specific as possible as to what that environment is like. Examples include:

• python 3.6 or later, with pyodbc 4+ and pandas v02+
• R 3.4 or later
• Oracle PL/SQL version 9i or later
• SAS 9.3 or later
• ::centrally created, possibly open-sourced, web app query tool with corresponding execution nodes at implementing sites::

For actual programming environments, where queries consist of human-generated, small batch free range artisinal code, you want to specify that environment in a lot of detail. Generally, you want to enable your users to create a similar environment locally (either with their local implementation of your data model, or from synthetic data) so they can formulate/test their queries before inflicting them on the network.

Opting for a custom web-based GUI application for specifying queries takes out a lot of the guesswork in terms of "will it run at the sites?" but of course that's at the cost of having to create said GUI application, and the sophistication of what users will be able to do.

A Registry of Implementations

Having some central clearinghouse where implementers can register that they have implemented your data model, are open for business, and set expectations in terms of what queries they're willing to entertain is crucial. The popularity of a data model comes from how many organizations have implemented it. This is also a great place for implementers to set expectations in terms of:

• The extents-in-time of their data holdings.
• The date they last ran the QA tests, and their results.
• What QA issues are currently known, and whether they are fixable/in process of being fixed.
• What specific versions of the query engine software they have available.
• What hoops there are to jump through in order to get a response—e.g., data use agreements, fees, approvals, applications, etc.

On the HCSRN we have a portal website that we use to document these things.

An Implementing Community

If nobody implements your data model, you don't have a network. And as soon as more than one org implements, you have the problem of ensuring that different implementers are interpreting those specifications in something like the same way. In a lot of ways, specifications are like legislated laws—they usually sound logical, coherent, and reasonable when they are formulated, in the abstract, with particular use-cases in mind. But just like we need judges to construe those laws in order to apply them to specific situations (often disturbingly different from those anticipated when the law/specification was first written) we need at a minimum, a way to arrive at consensus regarding how specific decisions should be made.

Arriving at consensus means having a conversation, be it live at meetings, or on conference calls, or asynchronously in e-mail for on forum threads. The means for having those conversations are what I mean by Community.

Users!

No need to belabor this one. Without people interested in using your data model, again, you don't have a network.

What Else?

What have I forgotten? Tell me in the comments.

SAS Programming package for Sublime Text is now available via Package Control

Just a quick note to convey the information in the title there.  Thanks to some lovely help from the community (especially @friedegg, @seemack and @bobfourie) the SAS package for Sublime is now more functional than ever, and is finally installable via the wonderful Package Control.

So if the previous, janky installation procedure has thus far kept you from trying out Sublime for SAS programming, do please give it a whirl.

Sublime Text, Intel Graphics and CTRL-ALT-ARROW hotkeys

Just in case this saves someone else (or future me) some head-scratching.

Work issued me a new laptop, onto which I promptly installed ST3.  When I first hit ctrl-alt-down arrow to go into column select mode my display turned upside down.  I've seen this dozens of timesby now--this is the graphics card add-on program making it easy to rotate my display (though why 180 degrees is a useful rotation that they want to serve is unclear).  I hit ctrl-alt-up arrow to put things back to rights, and then go into the system area to look for the (in my case Intel) icon to turn off its hot-keys.

But now I go back into ST3 and those key combos don't work at all.  Display stays properly oriented, but my sublime cursor moves noplace.

Is ST3 even receiving those keypresses?  I open its console with ctrl-` and type "sublime.log_input(True)" to turn on key event logging.  Nada, zip, nothing--something is completely eating those keypreses.  I google for a bit (mostly finding posts from people who have inadvertently rotated their displays and don't know how to un-rotate them) and didn't find anything useful.

So I pull up the full Intel HD Graphics Control Panel app and go into the hotkeys section:

For a goof, I tried re-defining the rotate-y hotkey combinations from ctrl-alt-::something:: to shift-alt-::something::.

I enabled hot-keys & tried sublime again.  That worked.  Then I disabled hot-keys and it still worked.  So I'm calling it a fix.  This seems like a bug in Intel's utility though--it seems to be eating keystrokes that it's been told to ignore.

How we do Quality Assurance for the VDW

Last weekend I attended the most excellent PCORI Data Quality Code-a-thon, hosted by Michael Kahn and his colleagues over at University of Colorado, at which I met some really interesting and smart people doing really interesting work.  A couple of them evinced an interest in VDW QA work and I said I'd share the substantive checks that we are doing.

Some Context

This is volunteer work

Like most everything VDW, QA work is largely unfunded and distributed across implementing sites.  Volunteers from the data area workgroups (e.g., Utilization, Pharmacy, Enrollment) put together lists of checks pertaining mostly to their data areas, write VDW programs that implement the checks & periodically (generally annually, but sometimes more frequently) make a formal request that implementing sites run the code & submit their results to the program author(s) for collation & reporting out to the VDW Implementation Group.

One big implication of this is that our approach is not nearly as coordinated as an outside user might expect.  I'd like to say that we are evolving toward a common approach, and we do have a new(ish) "Cross-File QA" group that's taking on meta-standards for QA work, but there is definitely a long way to go before this is uniform enough to be coherent to anyone not familiar with the history.

QA Is Multi-purpose

We generally try to kill 2 birds with our QA stones.  Primarily we want to characterize the quality of our implementations for ourselves, each other, and our user community.  But we also love it when our reports are useful to Investigators writing grant applications, who sometimes need to brag about  e.g., how many person/years worth of data we have across the network for people with a drug benefit.

This can be a slippery slope, on occasion leading individual sites to declare that a given measure has strayed from QA (which is generally exempt from IRB approval) into substantive research territory, or else exposes what should be proprietary information.  One example that comes to mind on Enrollment was a measure of churn--e.g., in a typical month, how many enrollees does a site tend to lose to disenrollment, and how many do they pick up?  It's a constant dance/negotiation.

Roy's QA Prejudices

To my way of thinking the best QA:

1. Enables implementers to find (and fix) their own errors first, before exposing them to any larger audience.  This is a matter of professional courtesy.
2. Includes as many objective checks as are practical to implement in code, and presents the running user with:
1. A clear list of what the checks are
2. What the tolerance is for those checks (e.g., 3% of your patient language records can have nonstandard values, but any more than 5% and we're going to say you failed the check).
3. Whether the file passed or failed each check.
3. More general descriptives characterizing the amount and predominant values in the data.  These are often most useful when viewed as part of collated output so you can compare sites.
4. Collated quality/descriptive reports 
1. should be readily available to the user community (we have them up on the HCSRN web portal, behind the password-protected area).
2. should be easily updated (completely automatically if possible) so that implementers are incentivized to fix whatever issues they can as soon as possible (and get credit for doing so).

Following the lead of the Utilization (encounters) workgroup, we generally refer to the objective checks as "Tier 1" checks and the descriptives as "Tier 2".  Like most things, the checks are a matter of negotiation within the workgroup.  I've come to think of them as crucial adjuncts to the specs themselves because they sometimes reveal reasonable disagreements on how to interpret the specs.

The Checks

Demographics

Tier 1

1. All variables listed in the spec exist and are the proper type (character or numeric).  I don't personally like to check length and so don't, though there is diversity of opinion on that & so some QA programs do.  There is no tolerance on these checks--any missing variable is a fail.
2. For those variables that have an enumerated domain of permissible values (pretty much everything but MRN and birth_date) that those are the only values found.  If > 2% of the values found are off-spec we issue a warning.  At 5% or greater we fail the check.
3. MRN is unique.  Zero tolerance here.

Tier 2

1. Counts of records (not just current enrollees) by
1. Gender
2. Race
3. Ethnicity
4. Age group
5. Need for an interpreter
2. Counts of enrollees over time by those same variables.

Enrollment

Tier 1

1. All variables listed in the spec exist and are the proper type (character or numeric). Here again zero tolerance.
2. For those variables that have an enumerated domain of permissible values (all but MRN, the start/stop dates, PCP and PCC) that those are the only values found.  Same tolerance here as with demog-- > 2% is a warn, > 5% a fail.
3. Enr_end must fall after enr_start.  Zero tolerance.
4. Enr_end must not be in the future.  Warn at 1%, fail at 3%.
5. At least one of the plan type flags (which say whether the person/period was enrolled in a PPO, HMO, etc.) must be set.  Warn at 2% and fail at 4%.
6. Ditto for the insurance type flags (e.g., commercial, medicare/caid, etc.).
7. If any of the Medicare part insurance flags are set, the general Medicare insurance flag must be set. Zero tolerance.
8. No period prior to 2006 should have the Medicare Part D flag set. 1% warn, 2% fail.
9. If the Part D flag is set, the drugcov flag must also be set.
10. If the high-deductible health plan flag is set, either the commercial or the private-pay flag must also be set.
11. If any of the incomplete_* variable values is 'X' (not implemented) then all values must be 'X'.

(That last check refers to six variables too new to be listed in the publicly available specs.  They let implementers surface known problems with data capture, if there are any (for example at Group Health we only have tumor registry information on people who live in one of the seventeen WA State SEER counties).

Tier 2

1. Counts and percents of enrollees over time by all substantive enrollment variables--plan types, insurance types, drug coverage, etc.
2. Counts & percents over time by several demographic variables (listed above under demographics).
3. Counts & percents of enrollees over time by whether the values in primary care clinic (PCC) and primary care physician (PCP) appear to be valid (that is, contain at least one non-space and non-zero character).

More Later

As I'm the primary author of the E/D checks, these were the easiest to hand.  I'll be back with at least some of the other checks the other groups have implemented.

Axis Problem

How can I get the 0 value on this graph's x-axis to stay left-justified, without using a VALUES = statement on the XAXIS?

The code for this is:

data gnu ;
  input
    @1   clinic        $char5.
    @7   report_date   date9.
    @19  readmit_rate  3.1
  ;
  format report_date monyy7. ;
datalines ;
north 31may2012   0.8
north 30jun2012   0.2
north 31jul2012   0.3
west  31may2012   0.0
west  30jun2012   0.0
west  31jul2012   0.0
run ;

options orientation = landscape ;

ods html path = "c:\temp" (URL=NONE)
         body   = "deleteme.html"
         (title = "Axis Problems")
          ;

    proc sgplot data = gnu ;
      hbar report_date / response = readmit_rate ;
      by clinic ;
      xaxis grid /* values = (0 to .1 by .1) */ ;
      yaxis grid ;
    run ;

ods _all_ close ;

Why aren't these colors the same?

When I run:
proc format ;
  value $sub
    "bibbity" = "5"
    "bobbity" = "30"
    "boo" = "80"
    "baz" = "120"
    "foo" = "150"
    "zoob" = "180"
  ;
quit ;

data test ;
  do subject = 'bibbity', 'bobbity', 'boo', 'baz', 'foo' ;
    do obs_date = '01-jan-2010'd to '31-dec-2013'd by 30 ;
      num_widgets = input(put(subject, $sub.), best.) + floor(uniform(4) * 30) ;
      proportion_blue = uniform(4) ;
      output ;
    end ;
  end ;
  format obs_date mmddyy10. proportion_blue percent9.2 ;
run ;

%let out_folder = c:/temp/ ;

ods graphics / height = 6in width = 10in ;
ods html path = "&out_folder" (URL=NONE)
         body   = "deleteme.html"
         (title = "Why are bubble color and line color not coordinated?")
          ;

  proc sgplot data = test ;
    loess  x = obs_date y = num_widgets / group = subject ;
    bubble x = obs_date y = num_widgets size = proportion_blue / group = subject transparency=0.5 ;
    xaxis grid ;
    yaxis grid ;
  run ;

ods _all_ close ;

I get:

I think it's confusing that the bubble colors and the loess line colors don't match.

If I change from loess to a series plot, the colors match.

Anybody know how I can get the colors to match?

Thanks!

Loving R's ggplot2!

So I'm messing around with R for a coursera course I'm taking, and totally loving the ggplot2 library.  Check out this lovely plot of some loan data we got from the course instructor:

 That's a scatterplot with loess smoother + confidence intervals, all done with this simple call:

qplot(x     = FICO
    , y     = ir
    , data  = loansData
    , color = Loan.Length
    , geom  = c('point', 'smooth')
    , ylab  = "Interest Rate"
    , xlab  = "FICO Score"
    )
How awesome is that?

Launchy + PowerShell = easy navigation between project folders

Like most programmers at GHRI, I do work in a multitude of different directories.  Different projects store their programs & data in different folders, and there are numerous different folders that that are important to my data infrastructure work.

When I'm called upon to navigate to these different folders I typically have to remember where they are & then 'cd' over to them (if I'm at a command line) or type into explorer's address bar one component at a time, waiting for the auto-complete (or attempting tab completion at the command line).  This can be cumbersome--especially when I'm not physically connected to the network.

At some point I decided to set some environment variables for myself so I could just type, e.g., %myproj% into the Run window or Explorer's address bar or an Open File dialog & be taken there instantly.  I found this very helpful--no more having to remember where things lived, just my nicknames for them.

Then after adopting powershell as my preferred command-line & discovering functions, I created a parallel set of functions that just did a 'cd' into the proper directory.

Then my machine was repaved & upgraded to Windows 7, and I lost my environment variables.  Around the same time I read a lifehacker article on the Launchy utility & decided to try that.  So rather than set up the environment vars I just created a special folder called Shortcuts into which I put shortcut files pointing to the various folders, named after my nicknames for the projects.  I like Launchy quite a bit, but I did miss my environment variables for the odd Open File dialog.

So today I decided to delve into powershell scripting a little so that I could put all the information in a script, and have it generate:

• The environment variables I missed,
• the ps functions I wanted, and
• the Launchy shortcuts I wanted.

Here's what I came up with--it seems to work pretty well.

$WinShell = New-Object -comObject WScript.Shell
$shrt_dir = "C:\Users\Roy\Desktop\shortcuts"
# nicknames and locations of my projects
$projects = @{"grif"  = "\\some_server\griffin\stupid name" ;
              "cupid" = "\\other_server\projects\cupid" ;
              "prod"  = "\\data_server\management\programs\"
}

foreach($prj in $projects.GetEnumerator()) {
  # Create a shortcut named for the nickname that points to the dir in the value.
  $shrtfile = $shrt_dir + '\' + $prj.key + '.lnk'
  $shrt = $WinShell.CreateShortcut($shrtfile)
  $shrt.TargetPath        = $prj.value
  $shrt.WorkingDirectory  = $prj.value
  $shrt.Save()

  # Create an environment variable for each.
  [Environment]::SetEnvironmentVariable($prj.key, $prj.value, "User")

  # Create a function for each nickname
  $this_func = "function " + $prj.key + "() {Set-Location '" + $prj.value + "'}"
  $this_func
  Invoke-Expression $this_func
}

Next up I want to change my prompt function so that those project folders show up as e.g., ::cupid:: in the prompt rather than the whole long thing.  I'm already replacing $env:home with a '~', so I should just be able to loop through that $projects dictionary to make similar substitutions.

Principles of VDW development

Spitballing for a paper I'm (hopefully) helping to write.

The principles informing development of the VDW are:

• The VDW exists to facilitate substantive health services, epidemiological, and health economics research, rather than advance the fields of medical informatics or computer science.  Thus we take a very pragmatic (and often not very sexy) approach to data sharing.  Results are always favored over methods.
• Participation must not impair organizations' ability to protect patients/insureds as the human subjects of research, or abide by applicable laws and regulations (HIPAA, etc.).  Participation must also not impair organizations' ability to protect the interests of both local researchers and their parent organizations (i.e., the health care/insurance providers).
• The data standards must be open and publicly available so that any interested organization with relevant data can implement and potentially collaborate with other implementing organizations.
• The data standards represent a "floor" rather than a "ceiling"—that is, implementers are free to embellish or enhance data structures in ways that do not break compatibility with the base VDW specifications, in order to increase the value to local researchers.
• No "least common denominator" specifications.  Participating organizations have varying levels of detail in their local data.  Rather than discard all but the lowest level of detail available across all implementing sites, the best data specifications accommodate detail and make it optional. For example, if some organizations record the precise day that an insured disenrolls, but others always pad this out to last day of the month, the best specification will allow both organizations to put their data in VDW form.
• Participation can be partial—an organization can choose to implement 5 out of the 11 data areas for example.  Their attractiveness as a collaborator is thus diminished of course, but they are still participants in the VDW process.
• Central coordination, but not authority.  Participation is voluntary by all organizations.  There is no central source of funding, and therefore no central authority directing the development of the VDW.  Decisionmaking is by rough consensus with the goal of serving the greatest number of existing and foreseeable projects, while not overtaxing resources available at the sites.

The problem w/the 3rd bullet there is that there's no central clearinghouse where a non-HMORN member org can advertise their implementation & hold themselves out as potential collaborators.

I might add:

• The data is never going to be perfect.  Like all data collected for any reason, VDW data has problems, not all of which we know about.  Problems can be introduced both in the local operational business systems from which it originates, and in the work implementers do to transform it into VDW data.  We are committed to a transparent process by which problems are reported, prioritized, and fixed.  But users should not assume that the data is pristine and suitable for their intended use--caveat user, and do please report issues.
• VDW is not the end-all/be-all of research data warehouses.  We are  eager to (strategically) expand the domain of projects for which VDW data alone are sufficient--where current or future projects stand to benefit.  But potential users should not assume that a given study can be carried out using nothing but VDW data.  Custom programming is sometimes necessary, and some projects can't be carried out at all.  Also, "my project needs new variable X" is not a reason for us to drop everything we're doing and implement variable X.

What am I forgetting?  What could be said better?

Managing the standard macros

Current State

GHRI maintains an FTP server at vdw.hmoresearchnetwork.org, which we use to keep/maintain the VDW standard macros, and several associated data files (e.g., the format data for %vdw_formats).

There are 2 main accounts for this server, one with just read access (which is what we use in the FTP FILENAME statement in stdvars.sas) and one with write and read (which is what I use for updating the macros).

Advantages

• SAS users at every site can %include the FTP-hosted file directly into their programs, ensuring they have the most recent version of the code at all times.
• Having the code in a single place ensures that all users benefit from additions and bugfixes.
• FTP is easy to script & we have an easy method of pushing out updates (e.g., a ruby script moves the file).

Disadvantages

• FTP is rightly viewed as an outdated and insecure protocol.
• One VDW site disables FTP on one of their servers, requiring local staff to manually download (and then selectively alter) the standard macros program file.
• FTP has made it a pain for end-users to just browse the code, which has probably hindered use.

We have on at least two different occasions over the last 5 or so years tested out http access at the various sites.  That can work every place that tested it (don't recall who all reported in the last time, though it was a bunch of sites)--including Group Health.  In that last round we also tested https access.  That everybody could do except for Group Health.  (Our problem is our proxy server, which requires NTLM authentication, which not many non-browser clients know how to pass.)

Impending Change

KP Northern California is to take over the hmoresearchnetwork.org domain, and will implement a new web presence for the HMORN based on its Alfresco system.

This raises the questions:

1. Will KPNC be amenable to maintaining an FTP server as GHRI has lo these many years so that we can keep the status quo?
2. Do we want to keep the status quo, or is it time to either move to http(s), or to another model of disseminating this code altogether?
3. If http(s) do we want to keep the code on an hmorn.org domain or do we want to move things over to say, github?

The main candidate for "another model of disseminating this code altogether" is I think, the CESR convention of making static copies of macros which are included in the .zip file packages that represent CESR VDW application jobs.  Even in that case though I think we'll want to have some 'canonical' repository of the code, to which updates should be directed & from which new copies should be had.  I don't know how CESR is dealing with that currently.

If the answer to question 1 is 'no' and 3 is 'hmorn.org', then I think either KPNC staff will have to take charge of the macros, or else provide Roy with a means of pushing updates to the file (preferably easily automated--something git or ssh-based would be my preference).

The case for github

I for one, would like to see us move the whole schmeer over to a public github repository, for the following reasons.

1. It makes our macros capital-P Public, and I think many SAS programmers could benefit from them.
2. It will raise the profile of the HMORN on what has become the de-facto standard for open source projects.  Note that we already have an 'organization' on github for the HMORN, which Christine Bredfeldt and I put together in order to share the PHI macros we wrote, and which Christine has published a paper on.
3. It makes not just the macros as they exist now available, but the complete history of edits back to 2009 available.  All versions will be easily browsable.
4. Github has a lovely collaboration model, and should make it easy for any minimally competant git user to update a macro and share it back with the group, either via pull request, or by direct commit (for people given commit permission).
5. It is incredibly secure.  In particular:

All private data exchanged with GitHub is always transmitted over SSL (which is why your dashboard is served over HTTPS, for instance). All pushing and pulling of private data is done over SSH authenticated with keys, or over HTTPS using your GitHub username and password. The SSH login credentials used to push and pull can not be used to access a shell or the filesystem. All users are virtual (meaning they have no user account on our machines) and are access controlled through the peer reviewed, open source git-shell.

The one down-side is that would-be collaborators would  need to get comfortable with git before they could easily contribute.  But that's not much of a problem relative to the status quo--people would still be able to e-mail me (or Christine, or any other person who creates an account & is given commit permission) changes that we could commit and push.

Public Key Cryptography: Encryption, Signatures and Certificates

Public Key Cryptography is a technology that forms the basis of a strong form of data encryption, digital signatures, and digital certificates. This post attempts to explain—in broad strokes—how these technologies work.

Key Pairs

The engine that drives Public Key Cryptography is the "key pair". A key pair is basically a set of two numbers, each of which can be used to encrypt data. These keys are used in the same way that passwords are used by the more common type of encryption—secret key encryption.
Key pairs have 2 properties that make them very useful.

• Things encrypted with one key of the pair can only be decrypted by using the other key in the pair.
• It is exceedingly difficult to derive one key from the other. Doing so requires solving a problem known to be difficult in computer science (like for instance factoring large prime numbers).

One naive encryption scenario involves splitting a key pair between two people who want to communicate privately. When Alice wants to send a private message to Bob, she encrypts it with her key, and Bob uses his to decrypt. When Bob wants to reply, he encrypts with his key and Alice uses hers to decrypt.

Figure 1: Naive Encryption Scenario

Public Key Encryption

Public Key Encryption takes this scenario one step further. In Public Key Encryption, the idea is that every participant has their own pair of keys. But rather than treat both keys as secret (as in the naive scenario above), only one of the keys is secret. The other is considered to be Public—its owner disseminates it far and wide. This is safe to do because of the second property of key pairs—just because someone knows your public key does not give them an advantage in guessing what your secret key is.

Figure 2: Key Generation & Exchange

There are special kinds of internet servers that do nothing but serve as open clearinghouses of public keys.

If someone wants to send you a private message, they get your public key (either from a keyserver, or directly from you in an e-mail) and use it to encrypt the message. You then use your secret key to decrypt the message. For a message back to them, you grab their public key and encrypt with that. So all the encrypting is done with the public keys, and all the decrypting is done with the corresponding private keys.

Figure 3: Secure Data Transfer with PKC

There's one major benefit to doing encryption this way—you never need to send anything secret (like a password) over an insecure channel. Your public key goes out to the world—it's not secret and it doesn't need to be. Your private key can stay snug and cozy on your personal computer, where you generated it—it never has to be e-mailed anywhere, or read over the phone, snailed on a floppy, etc.

Compare old-style, Secret Key encryption, where both people communicating need to know the same password—in that case you've got to come up with some way of getting that password to your friend in such a way that it can't be intercepted. That is often problematic.

But wait—there's more. Since things encrypted with one key of a pair can only be decrypted by the other key, the owner of a key pair can also encrypt a message with their private key. Given that in theory at least, everyone in the world has your public key, and that any message encrypted with your private key can be decrypted by your public key, this is not a recipe for private communication—anyone at all can decrypt the message. So why would you do that?

Digital Signatures

You would do that to authenticate the message—to prove that it came from you. Any message decryptable with a given public key is sure to have come from the person holding the corresponding private key. So to the extent you can trust that a given public key really belongs to a particular person, you can be sure that such a message really did come from that person. This is the basis of Digital Signatures.

Typically, since you're not going for privacy when you digitally sign a message, there will be a plain text version of the message, followed immediately by an encrypted version. To verify the signature the encrypted version is decrypted, and that decrypted text is compared to the plain text version. If they match, you can be sure the message is authentic, otherwise the message is probably bogus—it either came from some other person entirely, or it was altered after being signed.

This is a huge benefit. All of a sudden, the formerly anonymous Internet can now support verifiable digital identities—so people and organizations who 'meet' on the internet can have a basis for trusting one another—they at least know who the other party is.

But consider that phrase above "to the extent you can trust that a given public key belongs to a particular person" . That's potentially a big problem for digital signatures. Key pairs are very easy to generate—free software for doing so is available on the internet. Just because a given public key has my name on it does not necessarily mean that I generated it. Furthermore, public keys are usually transmitted over insecure channels—like the internet—which excepting maybe public cell phone yelling is probably the least secure channel known to humankind. Thus, they are subject to hacking.

So how can you be assured that a given public key really truly belongs to Bob Friend, and not Emily Enemy?

There are 2 methods.

1. Give Bob a call and ask him to read his public key to you over the phone. This is tedious and annoying. More importantly, it assumes that you know Bob personally, know his phone number, and will recognize his voice. In many situations (described below) this is unrealistic.
2. If you have a friend in common with Bob (let's call him Tommy Trustworthy), whose public key you have and trust, and Tommy has a copy of Bob's public key, then you could get Tommy to digitally sign his copy of Bob's public key and send the signed copy to you. When it arrives, you verify Tommy's signature, and if it checks out, you're all set. In a sense, Tommy is vouching for Bob's identity.

The Web of Trust

This second method is the basis of something called "the web of trust". The idea amounts to a network of people vouching for each other. If everybody endorses the the public keys of everybody they know personally (by digitally signing those keys & registering their signature with an internet keyserver) then eventually we will build up a web of trust associations, from which I should be able to trust your key. I may not know you directly, but I know Fred, who knows Mary, who knows Chris, who knows you. The more people you can get to sign your key, the larger the group of people who will be able to use this system to verify your public key.
It's sort of like a digital security version of Six Degrees of Kevin Bacon.


Figure 4: Kevin's worked with everyone

This is also tedious, but it's usually less tedious than the first option—especially for people you don't know personally. Technical people understand this, and will go to the trouble. At computer conferences you will find geeks congregating in "key signing parties" where everybody gets together, proves their identities to one another (driver's licenses, passports, etc.) and signs one another’s keys.

But non-technical people generally won't do this. Being able to digitally sign things is just not that important to them. Nobody expects them to be able to do this, and nobody they know has a public key to sign.

Enter the Certificate Authority.

Imagine that, instead of a network of lots of little trusting geeks, we have one, big, super Tommy Trustworthy, who is willing to verify the identities of anyone and everyone, and after doing so, sign their keys for them.

Imagine that this Super Tommy has incredibly rigorous methods of identification. Super Tommy flies out to your location, talks to your friends and associates, and only if everything smells right does he sign your key. It's worse than applying for a mortgage. But Super Tommy and his identification methods are so well known that no matter who you are—if Tommy trusts that you are who you say you are, then the rest of the world will too.

Now, Super Tommy doesn't do all this out of the goodness of his heart—you've got to pay him to go to all this trouble. But it's worth it in order to get the enormous numbers of people who trust Super Tommy to trust you too (and to avoid having to hang out with all those geeks at the key signing party).

Super Tommy is trading on his reputation.

Digital Certificates

The role Super Tommy is playing is called a Certificate Authority—an entity that is in the business of manually authenticating and certifying people's identities, and providing them with the digital credentials to signify it. Your signed public key is now a Certificate—it's a public key that's been endorsed by a Certificate Authority. You can present this certificate to anyone, and they can verify Super Tommy's signature on it, and trust that you are who the cert says you are. "If you're good with Super Tommy, you're good with me."

Why would anyone go to this trouble?

If you are a company wanting to do business on the internet, you can take your company's certificate and install it on a web server, and thereby identify the web server as belonging to you. That way when someone goes to your web site to buy something, they can be sure that the site they are interacting with is actually yours—not some hackers' in Belarus.

That can go a long way toward making people comfortable entering their credit card numbers into your web site. And furthermore, it's a key requirement (pun intended) of the Secure Sockets Layer (SSL) technology. When you visit a website that lives on a server that has a Certificate installed (and that's using secured HTTPS) you get that reassuring little padlock icon at the bottom of your browser screen.

Figure 5: SSL-signifying padlock icon

That padlock tells you 3 things.

1. The site has presented your browser with a certificate.
2. The certificate has been signed by someone (some Certificate Authority) your machine is set up to trust.
3. The internet traffic in between your browser and the web server is being encrypted. (Both to and from the server).

To see the evidence of this, double-click the little padlock icon. You'll see a dialog box that lists the person or entity to whom the certificate was issued. That's who you sue if they do you wrong. The Certificate Authority who issued the cert can tell you where that person or entity can be found.

Figure 6: The Identity established by the Certificate

Figure 7: Certificate Authorities Short-circuit the Web of Trust; VeriSign = Super Tommy.

Notice how one-sided this is. You know that you're really dealing with Amazon.com, but how does Amazon.com know that they are really dealing with you? You don't have one of these fancy certificates to present. They don't! And in fact—they don't care. If you can produce a valid credit card number, expiration date, and that new code from the signature bar on the back of the card, they can bill the account. From there, any identity disputes are between you and Visa.

But what if a site did care about the identity of the people who used it? If you went to the trouble of obtaining a certificate of your own (these are generally referred to as "client-side" certificates) then there is a process for installing that cert on a particular machine, and associating it with a particular user account. If you do that, your browser would present the certificate to the web server on request, and the web site would 'know' who it was dealing with.

For instance, CRN's Secure File Transfer website requires that users have a client-side certificate, that Kaiser Northwest issues, so they can be sure that users aren't just sharing their username/passwords. You can share your username & password with someone else fairly easily, but it is much more difficult to share a certificate.

Conclusion

Public Key Cryptography is an important technology that forms the basis of several other important technologies. The engine that powers PKC is the "key pair"—a mathematically derived pair of numbers, each of which can decrypt messages encrypted with the other. Private communication can be ensured by using public keys to encrypt messages. Messages can also be digitally signed by including a private-key-encrypted version of the message along with the plain text version.

By linking public keys to real-world trust associations we can establish a "web of trust" through which digital messages can be tied to particular legal entities. Because of their good reputations for carefully authenticating PKC users, Certificate Authorities serve as large portions of the web of trust.

The Gnu Privacy Guard (GPG)

The Gnu Privacy Guard is free, open source software that implements the PGP algorithm for Public Key Cryptography (among many others).  Note that PGP here is not meant to signify the proprietary, license-limited software package called PGP (published by the PGP corporation) but instead the “Pretty Good Privacy” algorithm, which is in the public domain.  GPG is available for windows, linux and many flavors of unix.  You can download the latest version at http://gnupg.org/.

GPG provides a command-line interface—meaning that on windows, you use it at the c:\> prompt.  Here is a mini-tutorial on how to use gpg.  User-supplied information is printed in bold red text .

Creating a key pair

To create a key pair you call gpg with the “--gen-key” option, and respond to the prompts.

C:\> gpg --gen-key

gpg (GnuPG) 1.2.1; Copyright (C) 2002 Free Software Foundation, Inc.

Please select what kind of key you want:

(1) DSA and ElGamal (default)

(2) DSA (sign only)

(5) RSA (sign only)

Your selection? 1

DSA keypair will have 1024 bits.

What keysize do you want? (1024) 1024

 

Requested keysize is 1024 bits

 

You need a Passphrase to protect your secret key.

 

Enter Passphrase: Who put the bang in shebang shebang shebang?

Reenter Passphrase: Who put the bang in shebang shebang shebang?

 

You need a User-ID to identify your key; the software constructs the user id from Real Name, Comment and Email Address in this form:

"Heinrich Heine (Der Dichter)<heinrichh@duesseldorf.de>"

 

Real name: Roy Pardee

Email address: rpardee@nowhere.net

Comment: Home

You selected this USER-ID:

"Roy Pardee (Home) <rpardee@nowhere.net>"

 

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O

 

We need to generate a lot of random bytes. It is a good idea to perform some other action (type on the keyboard, move the mouse, utilize the disks) during the prime generation; this gives the random number generator a better chance to gain enough entropy.

++++++++++.+++++.+++++++++++++++.++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++.++++++++++.+++++++++++++++..++++++++

+++++++>++++++++++.>+++++......................................++

+++

 

public and secret key created and signed.

key marked as ultimately trusted.

 

pub  1024D/22F2385B 2004-09-27 Roy Pardee (Home) <rpardee@nowhere.net>

Key fingerprint = 6F13 46E8 4B5F FE96 F59D  A609 7CD9 F063 22F2 385B

sub  1024g/C887F092 2004-09-27

Your key pair is now stored in gpg’s keyring file. [5] Any time you need to access your secret key, you will need to supply the passphrase you’ve chosen—that’s what protects you against someone hacking into your computer, or otherwise gaining access to your keyring file, and using your secret key to impersonate you.

Exporting Your Public Key

To export your public key, type:

C:\> gpg --armor --export [e-mail address]

Where [e-mail address] is the address you specified when you generated the key.  Gpg will print your public key as text to the screen:

-----BEGIN PGP PUBLIC KEY BLOCK-----

Version: GnuPG v1.2.1 (MingW32)

 

mQGiBEFYFw4RBADmipkBekgk+wm9HF5DI3VDozF1OISNPG4BIDWZ3PuhMZ2RYB1i

mFnH4QJ8LXbqjzz0YcsbrVF+RisMysy8SJZBAVSO20fneHrCPCYSXiXpxwCggwVe

DSi9/5plm+r9FvkAn1uy9UAzEvr8NHYC6C2sTRgPU11P

=mp7B

-----END PGP PUBLIC KEY BLOCK-----

You can either copy that off your console window, or else tell gpg to write it to a file with a command like:

C:\> gpg --armor --export [e-mail address] > mykey.txt

You can then attach mykey.txt to an e-mail, or copy/paste its contents into an e-mail message or otherwise send it around.

Importing Someone Else’s Public Key

The command to import a public key is just:

C:\> gpg --import [file name]

gpg: key 705D1FB9: "Amanda Hugankiss (Pretend person) amanda@hugankiss.com>" not changed

gpg: Total number processed: 1

gpg:              unchanged: 1

Where [file name] is the name of the file to which you’ve saved the key.

Encrypting A File

To encrypt a file using a public key you type:

C:\> gpg --encrypt-files –r [recipient’s e-mail address] [file name]

So for instance:

C:\> gpg --encrypt-files –r amanda@hugankiss.com StudyData.txt

Will result in the creation of a new file called StudyData.gpg. [6] You can then safely send this new file to your friend Amanda and she can decrypt it with her private key.

Decrypting A File

To decrypt a file you type:

C:\> gpg --decrypt-files [file name]

This operation requires the use of your secret key, and so gpg will prompt you for the passphrase you entered when you created it:

You need a passphrase to unlock the secret key for

user: "pardre1 <pardee.r@ghc.org>"

1792-bit ELG-E key, ID E9FD6753, created 2004-05-26 (main key ID 3468AA7D)

Who put the bang in shebang shebang shebang?

gpg: encrypted with 1792-bit ELG-E key, ID E9FD6753, created 2004-05-26

"Roy Pardee (Home) <rpardee@nowhere.net>"

Digitally Signing A File

To digitally sign a file you type:

C:\> gpg --sign [file name]

Here again you get prompted for your passphrase, and the output is written to a new file called [file name minus extension].gpg.

Verifying a Digital Signature

To verify a signed file, type

C:\> gpg --verify [file name]

 

gpg: Signature made 10/11/04 16:39:11  using DSA key ID 3468AA7D

gpg: Good signature from "Roy Pardee (Home) <rpardee@nowhere.net>"

This will of course only work if you have imported the public key of the person who signed the file.

Visit the Gnu Privacy Guard web page for more details on using gpg:

http://gnupg.org/ .

---

[1] For example, the type of encryption you use when you password-protect a .zip file is ‘secret key’ encryption.

[2] This is actually a slight simplification of the process—as an expedient, most digital signature software will compute a hash of the message and then encrypt that .  The American Bar Association has a nice discussion of digital signatures here : http://www.abanet.org/scitech/ec/isc/dsg-tutorial.html

[3] See the appendix, below.

[4] You might ask yourself how the traffic is encrypted in both directions—you haven’t sent anybody a public key for encrypting stuff sent to you, so how is the web server able to encrypt the pages it sends you in such a way that your browser can decrypt them for display?

The answer is that the HTTPS protocol doesn’t use public key cryptography for these exchanges.  It actually uses secret key cryptography—the kind where both sides of the transaction need to know a secret password.  The reason for this is that PKC is fairly computationally intensive.  If both browser client and web server had to PK-encrypt every bit of data that ran back and forth, the wait would be next to intolerable.  So to save on time, the web server randomly generates a secret password, PK-encrypts that , and from that point forward your browser and the web server use secret key encryption.

[5] You don’t normally need to directly manipulate this file, but on a windows system, you will find it at c:\documents and settings\<<username>>\\Application Data\GnuPG\secring.gpg

[6] Note that it’s advisable to rename this file to StudyData.txt.gpg.  The extra file extension gives a cue to the type of the file.  As it is, when Amanda goes to decrypt StudyData.gpg, she will wind up with a file called simply “StudyData”.  If you rename the file she will wind up with “StudyData.txt”

Cool new lab results data visualization

I've been trying to figure out how to put different graphs together in the same picture for a while now--finally got it working & could not contain my glee (thus, the blog post ;-). My main motivation is identifying local_cd's that I shouldn't be using (e.g., results should be character, but are numeric; units are wrong; values are way out of range, etc.). Check it out--I've got a VDW program that spits one of these out per value of test_type:

The graphs depict:

• The number of result records over time.
• The number of result values that are numeric vs. character, stratified by the local lab codes used.
• The distributions of numeric result values, by unit.
• The number of numeric result values, by unit and local lab code.
• The values of character results by local lab code.

I just this morning submitted an abstract for the 2013 HMO Research Network conference & hope to present the graphs & program there.

New Sublime Text package available for SAS Programming

Please use my SAS Package for Sublime Text

I started writing a crazy long treatise on why I like text editors, but really the information I want to put out is this: I've been working on a package for SAS programming with the Sublime Text 3 editor, which I believe is really functional & I would love to get feedback on how to make it better.

I'm using this as my main editor now--have not opened up UltraEdit in literally weeks and could not be happier.

Installation

You will find the package in this github repository.  Installing it amounts to causing the files in that repo to be copied to a new subdirectory under the location where Sublime Text is putting your packages (which you can find out by choosing the 'Browse Packages...' item off the 'Preferences' menu).  There are 2 good ways to do this:

If you've got git

Open a command prompt, cd over to the location where your Sublime packages are stored, and type:

git clone git@github.com:rpardee/sas.tmbundle.git SAS

The nice thing about doing it that way is that you can update the repo anytime thereafter with a simple

git pull

In the package subdirectory.

If you don't have git

The easiest thing would be to download this zip file and extract it to a new SAS subdirectory that you create in your Packages directory.

What do I get?  What's working?

The package includes:

1. A mostly written-from-scratch SAS program syntax definition file, so syntax highlighting works (including SAS' wonky comment style) and the symbol list gets populated w/dataset & macro names.
2. Likewise, a SAS log syntax definition file, which highlights NOTEs, INFO messages and of course ERRORs.
3. A build system (windows-only at the moment) which:
   1. Batch-submits the currently-showing file,
   2. Waits for the program to finish,
   3. Scans the resulting .log file for errors/warnings w/a fairly sophisticated regex (which e.g., ignores license expiration WARNINGs), popping a dialog indicating whether the log is clean), and then
   4. Opens the log file for viewing.
4. A command to skim through log files, jumping right to ERRORs and WARNINGs, by default bound to ctrl-e.  So just skip from trouble spot to trouble spot by hitting ctrl-e!
5. A bunch of very useful snippets.

What's not working yet or is still to be done?

My to-do is:

1. Rewrite the build script (currently a windows script file) as a python command to make it portable across platforms. SORT OF DONE
2. Incorporate this package into Package Control , to make installation & updating that much easier.
3. Move the Roy- and Group Health-specific snippets out of the package & into my User package.
4. Moar snippetts!

Credits

Many many thanks to Jiangtang Hu, whose fork of Jakob Stoeck's SAS bundle was the beginning of my hope that I could profitably use Sublime Text for SAS programming!

Edit!

Looks like there have been late comments to this post, with error reports.  Terribly sorry not to reply--I did not get notification about the comments.

Would anyone who's having trouble please log an issue at the github repository please?

Also--please note that I am no longer supporting sublime text 2 (even though it's nominally the current version).  ST3 has been out for more than a year (?) now, is very stable, and that's all I have installed anywhere, so I'm not in a good position to support ST2.  Sorry!

Thank you!

You're telling me what you need--I'm telling you what we have.

Listening in on some of the PCORI Workshop To Advance Use of Electronic Data, I can't help but be reminded of this bit of the movie Apollo 13:

For organizations like the HMORN, it's crucial to remember that we are data recyclers--we don't get to say what the health plans/providers record in the conduct of their business. We get what we get.

A big part of the valuable expertise that we've built up over the years is how to wring the greatest amount of information possible out of the data that we do have.

A Design Pattern for Production SAS jobs

Okay, that title is probably putting on airs.  But I have been thinking about what a good set of conventions might be for us to use at GH, and I think I've happened upon one, and wanted to flesh it out a bit to see if it looked reasonable all in one place.

So here it is.

Disclaimer

Lots of this relies on my personal preferences (e.g., many small files vs. one single monolithic job) and what may be unusual bits of the GH (or my personal) computing environment.  I do not intend to prescribe these as The One True Way for any and all people who may eventually read this.  I'll try to state my reasons for preferring something when I have reasons.  If I don't state any, feel free to assume that I don't have any & it's just arbitrary preference (or ask in the comments & I'll respond).

Some Context

Couple of salient details about the environment I'm working in.

• (As of this writing anyway) the primary destination for the data marshaled in these jobs is SAS datasets sitting on a central "data warehouse" server.  That's a windows server & is  a SAS/CONNECT rsubmit target.
• Source data come from any or all of:
  • SAS datasets sitting on the same destination server.
  • SAS datasets sitting on a unix server (which is also a SAS/CONNECT rsubmit host).
  • GH's Enterprise Data Warehouse--an RDBMS.
  • GH's Cost Management Data system--a different RDBMS.
  • Clarity--on still a third type of RDBMS.
• My primary machine is a laptop, which often has only a poor connection to the network.
• There are 3 of us working on "data infrastructure".
• We use git for source code control. 

Goals

We want:

• all jobs to run on a schedule, so the humans don't have to remember to kick things off.
• to be able to roll back to a previous version of a file quickly in case there are errors or bad updates.
• to be able to make and test fixes to jobs quickly, and without disturbing the user-facing (production) data.
• it to be reasonably easy for a new person to understand/maintain a job they didn't create.

That last guy is the reason for the conventions.

The Conventions 

Destination Data

Folders

Datasets should live in folders that group them by content area.  (Right now we have all VDW datasets in a single folder, but the idea of putting each one in its own subfolder sometimes appeals to me.)  So encounters/pxs/dxs for instance should all be in a single folder.

• Current production versions of each set of datasets should live in a top level folder, whose name signifies what datasets should be found therein (e.g., "ambulatory_visits").
• Only production data (and associated views thereon) should live in these folders--there should be no ancillary or interim datasets, documents, programs, or other files in there.
• Any ancillary and end-user-useful interim files should live in a subfolder called support.
• Prior versions of datasets should live in a subfolder called past.  Space permitting, the immediate prior version of the dataset should be stored unzipped.
• New versions of datasets should be stored in a subfolder called next, for whatever duration of time it takes for staff and/or users to validate them. 

So jobs build datasets in the /next subfolder, whatever QA & or user validation is going to happen happens, and then dsets migrate from prod -> past, and from next -> prod.  This helps cut down on runtime errors due to users holding locks 

Datasets

• Dataset names should clearly convey what data is contained therein.
• Datasets should be labeled to indicate their contents and their expected-complete-through date (e.g., "Professional claims paid through 31oct2012").
• Variables should similarly be labeled descriptively, and include important caveats if necessary and/or aids to interpretation (e.g., 02 = breast, 03 = colorectal).

Source Code

Control 

In case it doesn't go without saying, all source code needed to produce production data, as well as any associated notes, documents, ancillary data, etc. should be under source code control.

The 'production' repository is the one named in the scheduled job.  At GH we store source in a separate folder structure that largely mirrors the datasets that the jobs create..

Substantive edits to source code must not be made directly to files in the production repo--you should always do real work on a clone whose changes you then pull into prod once they are tested and ready.  The only edits you should do in prod is the minor alterations necessary to have the job run in production mode (that is, so it runs successfully under a service account & replaces the prod data).  Unless you're feeling lucky. ;-)

In my case I usually have 3 repos/job: production, a working copy that lives on my laptop, and a 'staging' copy that lives on the network in a non-production area.  I typically edit on my laptop, pull changes into staging, run/test them from there (usually from an ad-hoc task scheduled on a machine that stays connected to the network).  I use this script for that scheduling, so I get an e-mail when the job finishes.  I do this because the jobs often run longer than I'm connected to the network and I want to run them without interruption.  So the sequence is usually:

1. Clone prod onto my laptop.  Work there.
2. Clone laptop into staging.  Test there.
3. Pull from staging into prod.
4. Pull from prod into laptop to bring that repo up-to-date.

Round-and-round she goes.  Git makes this muy easy.

Organization

Here's where the real fun is.

Like datasets, every job gets its own folder.  The programs necessary for the job all live in the top-level.  I have the following subfolders:

• /tests: for holding unit test type programs--optimally one/macro in the job.
• /supporting files: reference data (usually mdbs, for holding things like our native lab result codes & the VDW test_types they should be translated into), reconnaissance programs, ad-hoc fix programs; sketches for new approaches to things
• /qa: data QA
• /output: QA programs spit results out here.
• /notes: various text files, word docs, etc. describing the job, or questions I asked/answered in the course of the programming.

Also like with datasets, the top-level folder has nothing but the .sas files necessary for the job.  (Because this stuff is all under SCC, we don't have to be sentimental/anal-retentive about obsolete code--we can go back in time anytime we want.)

There are two programs that are part of every job:

1. main.sas, which  is the entry-point for the job--that's what controls execution.
2. environment.sas, which--wait for it--sets up the environment for the job, making it possible/easy to switch the job from "dev" mode (runs as me; maybe on subset data; does not disturb production data) to "prod" mode (runs under a service account; puts data in public-facing places).

By 'environment', I mean things like:

• Where the job will write out any interim datasets.
• Where it looks for ancillary datasets (e.g., our enrollment job has a separate dset of DRUGCOV information that lives in a file that only gets built once in a while--that type of thing).
• Where it looks for files of account credentials/rlogin script files (these are different if I'm running in dev mode (runs as me) vs. production mode (runs as a service account). 
• Crucially--where the job will write out its final datasets.
• Whether are any global subsetting parameters or "obs = X" type conditions that should be in effect during the job.
• Sometimes--the locations of the source data for the job.

Usually I set all these various libs/parameters up to prod-mode values, and then define a macro that sets them to dev values.

I would like to say that environment.sas is the only place you'll find a libname statement in my jobs.  I aspire to that, but it's sometimes tough when I have to rsubmit into server1 to do some stuff, and then rsubmit from that remote session into server2 to do some other stuff.  I'd love to hear ideas for overcoming that.

In addition to these two programs, there are a series of files that define the macros that actually get the work done.  There's typically nothing fancy about these macros--they just take a few parameters (usually naming datasets they should accept as input and spit out as output) and do a chunk of work, assuming that their environment is set up for them.  The macro-fication is strictly about being able to control which bits of the program run when.

So main.sas typically looks something like this:

** Start a remote session on the server. ; 

%include "//server/share/start_rsubmit_session.sas" ;

** Make sure only one of these is in effect (or that the last definition is ;

** the one you want). ;
%**let codehome = //server/share/job_name/ ;       ** <-- prod. ;
%**let codehome = //other_server/share/job_name/ ; ** <-- staging ;
%let codehome = //roys_laptop/share/job_name/ ;    ** <-- laptop ;

%include "&codehome.environment.sas" ; 

** Put us into development mode (comment this out to go prod) ;
%set_dev ;

%include "&codehome.gather_mbhist.sas" ; 

%include "&codehome.gather_retro.sas" ;

%include "&codehome.interleave_enrollment.sas" ;

%gather_mbhist(outset = mbh, obslim = &obs) ;

%gather_retro(outset =  retr, obslim = &obs) ;

%interleave(mbhist = mbh, retro = retr, obslim = &obs, outset = retro_enrollment) ; 

I've seen jobs where the files are named with leading numbers to indicate the order in which they are run.  That can be nice, but I don't tend to follow that convention myself.

There are a couple of advantages to setting up jobs this way:

• It is very easy to see what mode a job is currently in, and to change it to a different mode.
• Because main.sas is the only program actually executing code, I can use it to easily control the job.  If something barfs in a middle step I can comment-out the bits that ran properly and manually submit the rest.
• If I'm smart about how I name the macros that make up a job, main.sas can read like a description of the high-level steps of a job.
• Having code split up into discrete chunks makes it easier to test.  I can have an e.g., job_name/tests/test_gather_mbhist.sas program that just does minimal environment setup & then %includes/calls that one bit of the job. 
• Putting the macros in different files lets me navigate to the code of a given step more easily than if I just had a single monolithic file with everything in it (I use an editor w/a multi-tab interface, so it's easy to jump around). 

So there it is.  What do you think?