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@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...@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