Pharm and Oracle Clinical
in [SAS]
|
From: "polmac on 18 Feb 2010 06:01 Hi Dan, For a basic introduction to OC I found the following to be quite good: http://www2.sas.com/proceedings/forum2008/396-2008.pdf (there are lots of more advanced papers to be found as well) With regards to getting the date of the DCMs, try the following code: proc sql; connect to oracle(path=�...�); create view EXTRACT.DEM as select STUDY as STUDY label =�Clinical Study� format $15. ,DCMNAME as DCMNAME label=�DCM Name� format $16. ,DCMDATE as DCMDATE label=�DCM Date� format $8. from connection to oracle (select STUDY STUDY, DCMNAME DCMNAME, DCMDATE DCMDATE from ABC$CURRENT.DEM); disconnect from oracle; quit; I hope this helps somewhat. Best regards, -- Pol M. Programador SAS Barcelona SUBSCRIBE SAS-L Dan wrote: > Dear SASLers: > > I am working at a CRO company. I now need to generate a Missing Page > Report. The basic ideal is: for one patient, there are some DCMs (Data > Collection Module), ie AE (Adverse Event), MH (Medical History). Now I need > to get the date of these DCMs received, and then compared with a certain > date, ie the date of screening. > > Now, the data are already in OC (Oracle Clinical). Our DBA showed me that > she has a table name STUDYID$current_rdcm_view which has all the dates info > for every DCMs and every patients. But this is a extracted excel file she > had generated (By TOAD?) from OC. > > I need to use SAS to extract that piece of information. The code I used is > below: > > proc sql; > connect to oracle(path=PATH user='user' pass=XXXX); > > create table here.view as select * from connection to oracle > > ( > select * > from s797015$current_rdcm_view ); > quit; > > > The code did not work out since > > ERROR: ORACLE prepare error: ORA-00942: table or view does not exist. SQL > statement: select * > > > To test the water, I tried the following code: > > proc sql; > connect to oracle(path=PATH user='user' pass=XXXX); > > create table here.view as select * from connection to oracle > > ( > select * > from s797015$current.demog ); > quit; > > This worked, however, I only get the normal variables for DEMOG. I did not > get oracle system variables, ie the generation dates of records. > > My questions: > > 1 How are the tables arranged at the back end of the OC. Is there a way > to "peek" into the OC data base? Like SAS: > > proc datasets library=Mylib ; > contents data=_all_ memtype=data; > run; > > This code give me all the data sets in Mylib, is there something like this > in OC? > > 2 Where to find good refs for OC starters? > > Thanks for your help. > > Dan > >
From: Tom Abernathy on 18 Feb 2010 08:53 Might be as simple as the table reference you are using. OC makes schemas for each study$snapshot. So s797015$current_rdcm_view should probably need to be s797015$current.rdcm_view or s797015$current.rdcm to have any hope of working. But I am not sure if there is a snapshot level view into RDCM. With SAS you can make a LIBNAME pointing to a SCHEMA in ORACLE. Then you can just do PROC CONTENTS to see what views are available for your snapshot. On Feb 17, 5:49 pm, deniseyu...(a)GMAIL.COM (SUBSCRIBE SAS-L Dan) wrote: > Dear SASLers: > > I am working at a CRO company. I now need to generate a Missing Page > Report. The basic ideal is: for one patient, there are some DCMs (Data > Collection Module), ie AE (Adverse Event), MH (Medical History). Now I need > to get the date of these DCMs received, and then compared with a certain > date, ie the date of screening. > > Now, the data are already in OC (Oracle Clinical). Our DBA showed me that > she has a table name STUDYID$current_rdcm_view which has all the dates info > for every DCMs and every patients. But this is a extracted excel file she > had generated (By TOAD?) from OC. > > I need to use SAS to extract that piece of information. The code I used is > below: > > proc sql; > connect to oracle(path=PATH user='user' pass=XXXX); > > create table here.view as select * from connection to oracle > > ( > select * > from s797015$current_rdcm_view ); > quit; > > The code did not work out since > > ERROR: ORACLE prepare error: ORA-00942: table or view does not exist. SQL > statement: select * > > To test the water, I tried the following code: > > proc sql; > connect to oracle(path=PATH user='user' pass=XXXX); > > create table here.view as select * from connection to oracle > > ( > select * > from s797015$current.demog ); > quit; > > This worked, however, I only get the normal variables for DEMOG. I did not > get oracle system variables, ie the generation dates of records. > > My questions: > > 1 How are the tables arranged at the back end of the OC. Is there a way > to "peek" into the OC data base? Like SAS: > > proc datasets library=Mylib ; > contents data=_all_ memtype=data; > run; > > This code give me all the data sets in Mylib, is there something like this > in OC? > > 2 Where to find good refs for OC starters? > > Thanks for your help. > > Dan
From: Nancy Brucken on 18 Feb 2010 09:25 Hi Dan, To build on Pol's response, there is a list of all of the underlying OC tables available as part of the OC documentation- your DBA should be able to provide you with a copy of it. Shameless self-promotion- I wrote a paper on retrieving some types of clinical study information from Oracle, which can be viewed at http://www2.sas.com/proceedings/sugi26/p132-26.pdf. While the paper dates back to SUGI 26 (so some of the tables referenced may have moved around in OC), the concepts may be helpful. Hope this helps, Nancy Nancy Brucken brucken at provide dot net On Thu, 18 Feb 2010 12:01:46 +0100, polmac(a)gmail.com <polmac(a)GMAIL.COM> wrote: >Hi Dan, > >For a basic introduction to OC I found the following to be quite good: >http://www2.sas.com/proceedings/forum2008/396-2008.pdf >(there are lots of more advanced papers to be found as well) > >With regards to getting the date of the DCMs, try the following code: > >proc sql; >connect to oracle(path=?...?); >create view EXTRACT.DEM as select >STUDY as STUDY label =?Clinical Study? format $15. >,DCMNAME as DCMNAME label=?DCM Name? format $16. >,DCMDATE as DCMDATE label=?DCM Date? format $8. >from connection to oracle (select >STUDY STUDY, DCMNAME DCMNAME, DCMDATE DCMDATE >from ABC$CURRENT.DEM); >disconnect from oracle; >quit; > >I hope this helps somewhat. > >Best regards, >-- > >Pol M. >Programador SAS >Barcelona > > >SUBSCRIBE SAS-L Dan wrote: >> Dear SASLers: >> >> I am working at a CRO company. I now need to generate a Missing Page >> Report. The basic ideal is: for one patient, there are some DCMs (Data >> Collection Module), ie AE (Adverse Event), MH (Medical History). Now I need >> to get the date of these DCMs received, and then compared with a certain >> date, ie the date of screening. >> >> Now, the data are already in OC (Oracle Clinical). Our DBA showed me that >> she has a table name STUDYID$current_rdcm_view which has all the dates info >> for every DCMs and every patients. But this is a extracted excel file she >> had generated (By TOAD?) from OC. >> >> I need to use SAS to extract that piece of information. The code I used is >> below: >> >> proc sql; >> connect to oracle(path=PATH user='user' pass=XXXX); >> >> create table here.view as select * from connection to oracle >> >> ( >> select * >> from s797015$current_rdcm_view ); >> quit; >> >> >> The code did not work out since >> >> ERROR: ORACLE prepare error: ORA-00942: table or view does not exist. SQL >> statement: select * >> >> >> To test the water, I tried the following code: >> >> proc sql; >> connect to oracle(path=PATH user='user' pass=XXXX); >> >> create table here.view as select * from connection to oracle >> >> ( >> select * >> from s797015$current.demog ); >> quit; >> >> This worked, however, I only get the normal variables for DEMOG. I did not >> get oracle system variables, ie the generation dates of records. >> >> My questions: >> >> 1 How are the tables arranged at the back end of the OC. Is there a way >> to "peek" into the OC data base? Like SAS: >> >> proc datasets library=Mylib ; >> contents data=_all_ memtype=data; >> run; >> >> This code give me all the data sets in Mylib, is there something like this >> in OC? >> >> 2 Where to find good refs for OC starters? >> >> Thanks for your help. >> >> Dan >> >>
From: SUBSCRIBE SAS-L Dan on 18 Feb 2010 10:06 Hi. Tom: Thanks for the tip. How to set the libname to SCHEMA? I can set up libname to oracle like below: Libname MYor oracle user='report' pass=r3p0rt path=opticon ; Thanks for the help. Dan On Thu, 18 Feb 2010 05:53:15 -0800, Tom Abernathy <tom.abernathy(a)GMAIL.COM> wrote: >Might be as simple as the table reference you are using. OC makes >schemas for each study$snapshot. >So s797015$current_rdcm_view should probably need to be >s797015$current.rdcm_view or s797015$current.rdcm to have any hope of >working. But I am not sure if there is a snapshot level view into >RDCM. > >With SAS you can make a LIBNAME pointing to a SCHEMA in ORACLE. Then >you can just do PROC CONTENTS to see what views are available for your >snapshot. > >On Feb 17, 5:49 pm, deniseyu...(a)GMAIL.COM (SUBSCRIBE SAS-L Dan) wrote: >> Dear SASLers: >> >> I am working at a CRO company. I now need to generate a Missing Page >> Report. The basic ideal is: for one patient, there are some DCMs (Data >> Collection Module), ie AE (Adverse Event), MH (Medical History). Now I need >> to get the date of these DCMs received, and then compared with a certain >> date, ie the date of screening. >> >> Now, the data are already in OC (Oracle Clinical). Our DBA showed me that >> she has a table name STUDYID$current_rdcm_view which has all the dates info >> for every DCMs and every patients. But this is a extracted excel file she >> had generated (By TOAD?) from OC. >> >> I need to use SAS to extract that piece of information. The code I used is >> below: >> >> proc sql; >> connect to oracle(path=PATH user='user' pass=XXXX); >> >> create table here.view as select * from connection to oracle >> >> ( >> select * >> from s797015$current_rdcm_view ); >> quit; >> >> The code did not work out since >> >> ERROR: ORACLE prepare error: ORA-00942: table or view does not exist. SQL >> statement: select * >> >> To test the water, I tried the following code: >> >> proc sql; >> connect to oracle(path=PATH user='user' pass=XXXX); >> >> create table here.view as select * from connection to oracle >> >> ( >> select * >> from s797015$current.demog ); >> quit; >> >> This worked, however, I only get the normal variables for DEMOG. I did not >> get oracle system variables, ie the generation dates of records. >> >> My questions: >> >> 1 How are the tables arranged at the back end of the OC. Is there a way >> to "peek" into the OC data base? Like SAS: >> >> proc datasets library=Mylib ; >> contents data=_all_ memtype=data; >> run; >> >> This code give me all the data sets in Mylib, is there something like this >> in OC? >> >> 2 Where to find good refs for OC starters? >> >> Thanks for your help. >> >> Dan
|
Pages: 1 Prev: Getting Base Observation values Next: Datetime prob in DI sudio |