CPCSSN Secure Research Environment (SRE)

THE ANALYST’S GUIDE

Have these at hand:

• VPN password
• Remote Desktop username and password
• IP address of your Virtual Machine (VM)
• Database username and password
• IP address of the database server: 10.15.1.150
• Name of your SQL Database
• Name of your ODBC Data Source

​

Step 1: Login to the SRE

OpenVPN Connection*

Microsoft Remote Desktop

​

There are 2 components required:

*technical details are provided in the SRE User Guide

Remote Desktop Connection Dialog

1. Enter the Computer IP Address. This is unique to each SRE Project and is provided by the SRE Systems Administrator:
• 10.15.1.xxx
2. Enter your SRE username
• If a different username is populated, select Show Options and then enter your SRE username provided by the SRE System Administrator
3. You will be prompted for your password when you login

Step 2: SQL Server Database Access

SQL Server Management Studio

Step 3: SQL

• Open up Microsoft SQL Server Management Studio
• At the login page put in the server name tcp:10.15.1.150
• Then put in the database login and password that was provided to you
• Once you have successfully logged in you should now be able to see a list of all the CPCSSN tables under your database, listed on the left hand side under ‘Tables’.

​

Open Database Connectivity (ODBC)

• The CPCSSN data is stored in SQL tables.
• The best way to access the SQL tables in your statistics software (ex. SAS, R, SPSS, or Stata), while maintaining the integrity of the data, is to set up an ODBC connection.
• The SRE System Administrator has set up an ODBC connection for you, and the name of your ODBC data source will have been provided to you.

Step 5: SAS

• Open up SAS
• In a new program window create a Library that points SAS to the SQL database (this code is also described in the SRE Documentation)

​

LIBNAME CPCSSN DSN=’cpcssndata’  user=uuuuuu pw=ppppppp schema=dbo;

​

CPCSSN – in this example the Library is called “Sql”, but you can choose any name.

dsn = name of the ODBC data source

user = database username

pw = database password

• Once you have created the new library it will be visible on the left hand side under Libraries (it will have a little ‘world’ icon on it).
• Opening up the Library you should now be able to see all your CPCSSN tables (note this image may have some tables that are not in your database).

Note: the name of your SAS library is not case sensitive

Step 6:�Linking tables in SAS

• There are many different ways you can link tables in SAS.
• Here is a diagram outlining the types of joins you can make using proc sql.

​

​

​

​

​

​

​

​

Step 6: Linking Tables in SAS

Some things to keep in mind

• Your linking variable will most often be the patient ID
• Most tables have many observations per patient
• It is advisable to create a main table that has your sample of patients (one observation per patient), and to link your other tables (that may have >1 observation per patient) to this main table.

​

Other Statistics Programs

R

• Sample R database connect script (access the SQL tables using the ODBC connection)

library(odbc)�con <- dbConnect(odbc(),�            Driver = "SQLServer",�            Server = "10.15.1.150",�            Database = "your_database",�            UID = "your_user_name",�            PWD = rstudioapi::askForPassword(“your database password ")�                 Port = 1433)

​

SPSS

• Click on File -> Import Data -> Database -> New Query.
• The Database Wizard will appear. Connect your ODBC Data Source that was created for you.
• Select the tables that you wish to import.

Please contact:

Technical questions (e.g. using openVPN, remote desktop login, etc): dwhite02@mail.ubc.ca

Analyst questions (SQL, SAS, etc): rachaelm@cpcssn.org