BookmarkSubscribeRSS Feed
Quentin
Super User

Over the years I've seen a few pitches from Electronic Data Capture / Clinical Trial Management Systems, both cloud and non-cloud, so things like Medidata Rave, Orale InForm, Medrio, DataTrak, etc etc etc.

Usually the one question I ask is "As a SAS programmer, I would like to be able to connect to the database via ODBC or whatever and to pull data.  Can I do that?"    And typically the answer is "It's easy to get data from our application to SAS, just click this button on our export menu and then you can download a: SAS transport file | csv and .sas file for reading into SAS | whatever. "

But I don't want a data extract, and I definitely don't want to have to press a button and download a file every time I want to see data from SAS.  I want to be able to pull data from the database.  Not just Case Report Form data, but also trial metadata.  Sometimes the response is that the database structure is intellectual property.  Of course I don't really need the full database ERD for whatever spectacular design has been developed.  If they made standard views to allow easy access to data, that would be fine (and actually helpful).  Sometimes they say that because the database / server is a shared resource, they can't give users direct access (could the security model be that bad?).

So here's a bunch of questions for folks using EDC systems (closed commercial solutions, or even open-source or in-house solutions):

1.  Are SAS programmers able to connect to the EDC/CTMS database and pull data when they need it?

2.  If not, do you have an automated solution for extracting data out of the database and putting it somewhere SAS programmers can see it?

3.  If not, do you have a manual approach for transferring data?

It seems like many of these CTMS applications have the philosophy of "You can do all your trial management (including data capture and trial monitoring reports) in me and then at the end just make one data extract to throw over the wall to the statistician. It's trial management without programmers!"   But even if the CTMS offers great reporting, I feel like I would still want programmers/statisticians to have easy access to trial data while it is under way, for custom reports/analysis etc.

I guess the obvious solution would be to choose an open-source solution such as OpenClinica....  But curious if any of the closed vendors are providing this sort of data access.

Thanks,

--Q.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
5 REPLIES 5
Quentin
Super User

Guess I was thinking about solutions like this one (but hopefully easier integration : ) 

http://www.pharmasug.org/proceedings/2012/AD/PharmaSUG-2012-AD27-SAS.pdf

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, I have worked with a few over time (note this is just my view and not bang up to date so exclusions/omissions etc!):

1.  Are SAS programmers able to connect to the EDC/CTMS database and pull data when they need it?

Medidata provides two options - SAS on demand which is a manual extract and send.  Biostats gateway - this can be accessed directly via proc http.  I have written some code which extracts the study metadata, and then extracts the data based on that metadata.  It works well and can be automated.

Oracle - Depends on hosting.  If its local then you can ODBC into the database directly, much like most locally hosted DB's.  If they host it the impression I get is they don't want direct access.

These are the two big ones.  I do agree with you, access to not just the data, but the metadata is essential nowadays.  DB metadata -> define = automated mapping etc.  Lots of potential, especially if its all ODM in first place.

2.  If not, do you have an automated solution for extracting data out of the database and putting it somewhere SAS programmers can see it?

3.  If not, do you have a manual approach for transferring data?

Have done the above two for many years and there are many ways of doing it.  From the old favourite of dated directories, loading to local DB, creating branches in SVN etc. This is quite a long topic, do you need to keep automated transfers, probably not, so just have one that is overwritten.  For milestones these would need a folder or branch so to keep for all time.  How you handle this in SAS libnames is also pretty wide, could do a dir and take latest date, fix the date manually, have code on branches etc.

Its difficult to say really.  Something you need to disucuss internally, follow SDLC on the whole process.  Decide what the requirements are, tools needed, code to be written etc.  File structure is also a big topic.  Also consider what other tools need to plug into this, e.g. data review, mapping tools etc.  You could write a book on this subject Smiley Happy

ballardw
Super User


As far as

do you need to keep automated transfers, probably not, so just have one that is overwritten.

I would say implementing in this fashion should be held off until you and the data source have everything running routinely through several cycles. I've had too many cases where contractors rather arbitrarily change data characteristics and values. I've had to work through the changes with other agencies that have access to reports generated from "my" data and why their results don't match our reports due to time of extract and one or more of the above issues.

And go back to being cautious at any "upgrade" or requirements change.

AskoLötjönen
Quartz | Level 8

Medidata is offering also third option. Direct access using ODBC into replicated database which includes all Medidata Rave tables, so you can extract all needed data. We have build system based on these tables, we are extracting lot of tables once a day, full load for small tables every night and for big tables like datapoints we are loading incrementally during week and once in week we are doing full download. So we have all needed metadata in our datawarehouse, which we are using i.e. for creation of ClinicalViews like SAS datasets. So this happens automatized for every study every night. Data transfer is quite reliable even we are Finnish based company and we have to do transatlantic data transfer. I'm not sure if MediData is offering this for new customers, because when we started this project there was no Biostat gateway available.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 5 replies
  • 6219 views
  • 3 likes
  • 5 in conversation