Hi Tony, Here at UMN we had the same problem and fixed it using the data step views you mentioned. Running the prep code doesn't duplicate the data it seems. Instead it stores more of a mapping that sas can use to reference your actual views. Some problems we ran into were that the prep code ended up altering the lengths of all of our varchar2 fields (on the sas side, not the oracle side) so we had to modify the code a bit to manually set the proper lengths. If you run into any issues let me know because it's possible we ran into them before as well.
Regards, Andrew Hangsleben University of Minnesota On Wed, Apr 13, 2016 at 12:37 PM, French, Tony <[email protected]> wrote: > Michael and all, > > > > We are stuck on the _TIME column conversions. We are using Oracle and > have those columns defined as varchar2(5) based on the RDBMS specs. We > have chosen to run SAS directly against Oracle, at least for now. However, > we are unclear on how to map the varchar2(5) columns to a format that SAS > will automatically map to as numeric. I believe that’s likely the exact > purpose of the data step views that Michael mentioned below, correct? Does > the data step attempt to read the CDM V3 schema from oracle (oracdata) and > then write the transformed data to the local file system as sasdata? > > > > Has anyone had success at making the conversion using Oracle Views > directly, so that the data isn’t duplicated a third time? > > > > We are exploring both approaches in parallel, but are not clear as to how > to format the column(s) in Oracle (via a view) so that SAS will make the > correct transformation to numeric. > > > > > > > > Thanks, > > Tony > > > > > > > > *Tony French *| *Software Development Manager* > > * Tools and Shared Resources* > > * Center for Biomedical Informatics* > > *1101 West Tenth Street * > > *Indianapolis, IN, 46202* > > Tel 317-274-9087 | Fax: 317-274-9305 > > Twitter: @Regenstrief | Facebook.com/regenstriefinstitute > <http://facebook.com/regenstriefinstitute> > > www.regenstrief.org > > > > Confidentiality Notice: The contents of this message and any files > transmitted with it may contain confidential and/or privileged information > and are intended solely for the use of the named addressee(s). > Additionally, the information contained herein may have been disclosed to > you from medical records with confidentiality protected by federal and > state laws. Federal regulations and State laws prohibit you from making > further disclosure of such information without the specific written consent > of the person to whom the information pertains or as otherwise permitted by > such regulations. A general authorization for the release of medical or > other information is not sufficient for this purpose. > > If you have received this message in error, please notify the sender by > return e-mail and delete the original message. Any retention, disclosure, > copying, distribution or use of this information by anyone other than the > intended recipient is strictly prohibited. > > > > > > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Michael Prittie > *Sent:* Tuesday, April 12, 2016 9:01 AM > *To:* Wanta Keith M; [email protected] > *Subject:* Re: SAS views for CDM v3 data characterization query > > > > Hi Keith, > > The SAS code that I wrote to build and validate the data-step-views off of > our Oracle backend here at KUMC is available on GitHub ( > kumc-bmi/PCORnet-Diagnostic-Query/data_step_views > <https://github.com/kumc-bmi/PCORnet-Diagnostic-Query/tree/master/data_step_views>). > If you’re not using Oracle, you would need to modify the code to work with > your RDBMS. Let me know if you have any questions. I know that Angela Bos > (UTHSCSA) and George Kowalski (MCW) have also used the data-step-view > approach, so they might be able to help you as well if you run into any > issues getting this working. > > > > Best, > > Michael Prittie > > (KUMC) > > > > *From: *<[email protected]> on behalf of Wanta Keith M < > [email protected]> > *Date: *Monday, April 11, 2016 at 7:07 PM > *To: *"[email protected]" <[email protected]> > *Subject: *SAS views for CDM v3 data characterization query > > > > GPC – > > > > Is there anyone in GPC that has built the SAS views for the data > characterization query? I couldn’t find any files that contain the SAS > Views source code for the date and time conversions for the data > characterization query for CDM v3. It appears that we have a choice of > using them or not. I’m guessing PCORI has a good reason to use them other > than for the date/time conversions that is not in the current requirements, > that has not been disclosed, so I’d like to try it anyway. If nobody from > our group has not, I’ll take the time to do so and give it a shot. > > > > Thanks, > > Keith Wanta > > (WISC) > > _______________________________________________ > Gpc-dev mailing list > [email protected] > http://listserv.kumc.edu/mailman/listinfo/gpc-dev > >
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
