Hi Jay,
I'm not sure if you've seen this or not, but PCORnet has a 'create indexes'
program for the CDM tables that can be found here:
https://pcornet.imeetcentral.com/zf-front.php/home/filesfolders/foldertree/download?id=59923700&spid=363332&type=2&entry=1
I've copied and pasted the code from the SAS program on iMeet below in case you
don't have access.
Steff
/*****************************************************************************
* $Source: create_indexes $;
* $Date: 2018/03/13
* Study: PCORNET
*
* Purpose: Create indexes for PATID and ENCOUNTERID in all tables where they
are present
* and on PROVIDERID in the PROVIDER table
*
* Inputs: A development version of PCORnet CDM v3.1 or v4.0 compliant SAS
datasets
* of all CDM tables except HARVEST
*
* Outputs: PCORnet CDM v3.1 or v4.0 SAS datasets and dataset indexes
*
* Assumptions: All programs run in SAS 9.3 or higher
* All CDM tables are present and conform to the
CDM specifications
*
* Instructions:
* 1) Enter the directory information on line 32
(Example: /ct/pcornet/data/)
* 2) If necessary, modify the tables for which
indexes will be created
* as instructed on lines 66-71
******************************************************************************/
options error=0 validvarname=upcase;
/******************************************************************************/
/*Enter directory where a development version of your CDM data is stored.
This program creates indexes on all tables:*/
/******************************************************************************/
%let dpath=/dcri/pcornet/queries/create_indexes/test_data/test40/;
libname pcordata "&dpath" ;
/******************************************************************************/
/*DO NOT EDIT THIS PORTION OF THE CODE
/******************************************************************************/
%macro ind(dsn,enc);
proc sort data=pcordata.&dsn out=&dsn;
by patid &enc;
run;
data pcordata.&dsn(index=(patid &enc));
set &dsn;
run;
%mend ind;
*PROVIDER macro;
%macro ind2 (dsn=);
proc sort data=pcordata.&dsn. out=&dsn.;
by providerid;
run;
data pcordata.&dsn.(index=(providerid));
set &dsn.;
run;
%mend ind2;
/******************************************************************************/
/*Create indexes for each specified CDM table.
*Place an asterisk in front of any table which is not present in your data or
for which
you do not want to create indexes. For example, if you have not populated the
MED_ADMIN
table, change "ind(dsn=MED_ADMIN,enc=encounterid" to "*ind(dsn=MED_ADMIN,
enc=encounterid)""
/******************************************************************************/
*Core tables;
%ind(dsn=DEMOGRAPHIC);
%ind(dsn=ENROLLMENT);
%ind(dsn=ENCOUNTER,enc=encounterid);
%ind(dsn=DIAGNOSIS,enc=encounterid);
%ind(dsn=PROCEDURES,enc=encounterid);
*Optional tables present in CDM v3.1 and CDM v4.0;
%ind(dsn=CONDITION,enc=encounterid);
%ind(dsn=PRESCRIBING,enc=encounterid);
%ind(dsn=PRO_CM,enc=encounterid);
%ind(dsn=VITAL,enc=encounterid);
%ind(dsn=LAB_RESULT_CM,enc=encounterid);
%ind(dsn=DEATH);
%ind(dsn=DEATH_CAUSE);
%ind(dsn=DISPENSING);
%ind(dsn=PCORNET_TRIAL);
*Optional tables added in CDM v4.0;
%ind(dsn=MED_ADMIN,enc=encounterid);
%ind(dsn=OBS_CLIN,enc=encounterid);
%ind(dsn=OBS_GEN,enc=encounterid);
%ind2(dsn=PROVIDER);
proc contents data=pcordata._all_;
run;
--------------------------------------------------------------------------------------------------------------------------
From: Gpc-dev <[email protected]> On Behalf Of Pedersen, Jay G
Sent: Tuesday, April 21, 2020 8:35 AM
To: [email protected]
Subject: [EXTERNAL] Indexing SAS CDM tables?
CAUTION: This email originated from outside of the Marshfield Clinic Health
System. Do not click links or open attachments unless you recognize the sender
and know the content is safe.
Hi,
I am curious if anyone is applying indexes to the SAS CDM tables.
For example, putting on a unique index on the PATID in the DEMOGRAPHIC table.
One way it can be done is with "proc sql" steps using "create index" commands.
I am guessing that this could dramatically impact the time needed for running
the Data Characterization Query and such.
If anyone is doing that and could share the actual indexes that they are
applying,
it would be of help.
Thanks!
Jay Pedersen, M.A.
Department of Pathology/Microbiology
University of Nebraska Medical Center
985900 Nebraska Medical Center
Omaha NE 68198-5900
tel:402-559-9593 (office)
tel:402-350-7851 (mobile)
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.
______________________________________________________________________
The contents of this message may contain private, protected and/or privileged
information. If you received this message in error, you should destroy the
e-mail message and any attachments or copies, and you are prohibited from
retaining, distributing, disclosing or using any information contained within.
Please contact the sender and advise of the erroneous delivery by return e-mail
or telephone. Thank you for your cooperation.
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
