It appears we are passing data characterization and the data looks correct. For our Oracle platform these are the transforms we use to convert Oracle Datetime to SAS dates and times
CAST(TO_CHAR(CAST(E2.START_DATE AS DATE), 'YYYYMMDD' ) AS INTEGER) AS BIRTH_DATE, CAST(TO_CHAR (CAST(E2.START_DATE AS DATE), 'HH24MI' ) AS INTEGER) AS BIRTH_TIME, Jim James R. Campbell MD [email protected]<mailto:[email protected]> Office 402-559-7505 Secretary 402-559-7299 Fax 402-559-8396 Pager 402-888-1230 From: [email protected] [mailto:[email protected]] On Behalf Of Verhagen, Laurel A Sent: Wednesday, April 13, 2016 4:48 PM To: French, Tony; Michael Prittie; Wanta Keith M; [email protected] Cc: Hood, Daniel Robert; Ring, Nate Subject: RE: SAS views for CDM v3 data characterization query Late reply, but I wanted to mention that at Marshfield our exposure with Oracle is limited to a reconciliation process between our production data tables and our data warehouse (Netezza and supplemental MS SQL). We have found that dates and times are not the most straight forward to format on Oracle. Generally speaking, we've always had to pull it down in SAS in order to manipulate. Our work with this was mentioned on ticket #381<https://informatics.gpcnetwork.org/trac/Project/ticket/381>: One complexity is SAS wants numeric values for the time fields. Values containing a colon (HH:MM) are considered character fields. There didn't seem to be a good way to store this via SQL without a lookup table for joining HH:MM to a count of seconds after midnight. We adjusted this on the SAS Dataset(s). Thanks, Laurel From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of French, Tony Sent: Wednesday, April 13, 2016 2:55 PM To: Michael Prittie; Wanta Keith M; [email protected]<mailto:[email protected]> Cc: Hood, Daniel Robert; Ring, Nate Subject: RE: SAS views for CDM v3 data characterization query Thanks everyone for your earlier replies! They were all very helpful! One other thing that I just noticed in the SCILHS Oracle code, as well as the KUMC fork, is that the times are being stored in 12 hour format (HH:MI). I would have expected 24 hour format (HH24:MI). In addition, I would have thought that the dates would have been truncated where the times are being stored separately. That said, I've not found any definitive documentation to know for sure. I'm guessing that most sites have already posted their results with the 12 hour format, so I'm going to go ahead and post our diagnostic query results now and hopefully post the characterization results by the end of the week. We'll then circle back to correct the 12/24 hour format later , if needed. --Tony From: Michael Prittie [mailto:[email protected]] Sent: Wednesday, April 13, 2016 2:01 PM To: French, Tony; Wanta Keith M; [email protected]<mailto:[email protected]> Cc: Ring, Nate; Hood, Daniel Robert Subject: Re: SAS views for CDM v3 data characterization query Hi Tony, The data-step-views don't contain any of CDM data, they simply point to the original data source and define any data-type conversions to apply. Originally we were running the DQ directly against our Oracle database, but keep coming up with data type complaints in the result PDF. The data-step-views resolved these issues for us. If you have any question about our data-step-view SAS code that we have up on GitHub let me know. Angela Bos had put together an excellent set of instructions on configuring everything for the DQ using data-step-views. If I can find them I'll forward them on. Best, Michael Prittie Biomedical Informatics Software Engineer Division of Medical Informatics University of Kansas Medical Center From: "French, Tony" <[email protected]<mailto:[email protected]>> Date: Wednesday, April 13, 2016 at 12:37 PM To: Michael Prittie <[email protected]<mailto:[email protected]>>, Wanta Keith M <[email protected]<mailto:[email protected]>>, "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Cc: "Ring, Nate" <[email protected]<mailto:[email protected]>>, "Hood, Daniel Robert" <[email protected]<mailto:[email protected]>> Subject: RE: SAS views for CDM v3 data characterization query 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 [cid:[email protected]] 1101 West Tenth Street Indianapolis, IN, 46202 Tel 317-274-9087 | Fax: 317-274-9305 Twitter: @Regenstrief | Facebook.com/regenstriefinstitute<https://urldefense.proofpoint.com/v2/url?u=http-3A__facebook.com_regenstriefinstitute&d=BQMFAg&c=KNVzINr6WAqWApikNSnyDeOu0ck0iFwcrMz92MxUhIs&r=A2PXk2ZGVGBzoEOQMwrrbqse0CnDMUgS4P3-3HOTSAwE8Vfvoc1Wm0NCYFbuU-_5&m=P-6t8mmNRlpvjvnlFo7Cbx40vuoWTQGEykKEo3MDmCo&s=4YrEiRXZnFF6f_FIEDimZM-dDhbwxFq6d1KwZJ4nLbs&e=> www.regenstrief.org<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.regenstrief.org_&d=BQMFAg&c=KNVzINr6WAqWApikNSnyDeOu0ck0iFwcrMz92MxUhIs&r=A2PXk2ZGVGBzoEOQMwrrbqse0CnDMUgS4P3-3HOTSAwE8Vfvoc1Wm0NCYFbuU-_5&m=P-6t8mmNRlpvjvnlFo7Cbx40vuoWTQGEykKEo3MDmCo&s=bKI3HdWTvSUaKBHiiK1wYnQxfz0hPsieYcaqZhLjpck&e=> 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]> [mailto:[email protected]] On Behalf Of Michael Prittie Sent: Tuesday, April 12, 2016 9:01 AM To: Wanta Keith M; [email protected]<mailto:[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://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_kumc-2Dbmi_PCORnet-2DDiagnostic-2DQuery_tree_master_data-5Fstep-5Fviews&d=BQMFAg&c=KNVzINr6WAqWApikNSnyDeOu0ck0iFwcrMz92MxUhIs&r=A2PXk2ZGVGBzoEOQMwrrbqse0CnDMUgS4P3-3HOTSAwE8Vfvoc1Wm0NCYFbuU-_5&m=P-6t8mmNRlpvjvnlFo7Cbx40vuoWTQGEykKEo3MDmCo&s=ADF0lX26rYIvdynvff3NgR_bBQUaHfUeOLewuXurfsY&e=>). 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]<mailto:[email protected]>> on behalf of Wanta Keith M <[email protected]<mailto:[email protected]>> Date: Monday, April 11, 2016 at 7:07 PM To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[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) The information in this e-mail may be privileged and confidential, intended only for the use of the addressee(s) above. Any unauthorized use or disclosure of this information is prohibited. If you have received this e-mail by mistake, please delete it and immediately contact the sender.
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
