Instructions for importing a batch of DSM Data: Difference between revisions

(49 intermediate revisions by 3 users not shown)

Line 3:

We receive data from DSM that needs to be imported into our main repository.

== Instructions ==

== Instructions Using SAS ==

The ACCESS Import program stopped working after Feb 9,2024 and a temporary solution using SAS was suggested.

=== Step 1- done by Julie ===

# In the batch folder of the current DSM data, create a subfolder labeled JM and save the SAS program labeled DSM_import_version2.sas

# Run the SAS program and the following outputs are generated:

## an excel file containing New labs is exported. The list has to be emailed to Allan Garland for confirmation. If new labs appear, Julie will add them to the SAS codes and re-run the SAS program.

## an excel file containing the matching DSM labs using the criteria that either the LAB dttm or Collect dttm is between the [[Admit date and time]] and [[Dispo DtTm]] in worksheet '''MatchDSM'''.

## an excel file containing the unmatched D_IDs

### the worksheet '''DSM_NoDID''' contains the D_ID where PHIN or chart number are not present in the dumped DSM data file.

### the worksheet '''Log_NoDID''' contains the D_ID in the requestor list but not found in the L_LOG table of CFE. This happens when the D_ID has been changed for some reason after the request has been sent already. if no D_ID label changing happens, then the worksheet will be empty. If not empty, then the SAS program must be modified to incorporate the D_ID label change and also has to be written in the [[L_Problems_D_ID table]] if not entered yet. Presumably the entry to the L_Problems_D_ID table must precede the SAS output.

### the worksheet '''nomatchDSMLab''' contains the D_ID present in the dumped but no LAB dttm or Collect dttm fall between the [[Admit date and time]] and [[Dispo DtTm]]. The file has the same columns with the requestor list plus the LOS sorted from highest to lowest. Shorter LOS (<=2days) may possibly have no lab requests but those with longer LOS may have just missed.

### the worksheet '''LOSgt2_resend''' contains the D_IDs with LOS > 2d to be resent on the '''next batch''' of sending to confirm if really has no lab data following the [[Instructions for requesting a batch of data from DSM]]. This step of resending has stopped 07March2025 because the resent profiles still showed no Lab data on the next batch.

=== Step 2 - done by Pagasa ===

# Open the DSM_Labs_data.accdb

## Goto the External Data and click new datasource from file Excel

## Choose the excel file where the final matchedDSM is saved and append to L_Labs_DSM

## Update the Date imported and write comments if any to the WIKI https://ccmdb.kuality.ca/index.php?title=Instructions_for_requesting_a_batch_of_data_from_DSM

# Close the accdb.

== Instructions Using ACCESS ==

=== Prep ===

Line 9:

Line 30:

#* make a new local directory

#* Put the following into the directory

#** [[Centralized_data.~~mdb~~]]

#** [[Centralized_data.accdb]]

#** [[DSM_Labs_Consistency_check.accdb]]

#** [[DSM Labs data.accdb]]

#** '''PHI not needed''' - [[PHI.mdb]] is not needed during import because it matches by D_ID

# copy the newly received batch of data from [[DSM_Lab_Extract#File_Share]] to a '''different local directory that contains '''only those files'''not the requestor file.

# copy the newly received batch of data from [[DSM_Lab_Extract#File_Share]] to a '''different local directory that contains '''only those files'''not the requestor file & '''''''''the csv with no(#) beside it.'''

# ~~open [[DSM_Labs_Consistency_check~~.~~accdb]]~~

=== Check for new labs ===

# Open [[DSM_Labs_Consistency_check.accdb]]

# Make sure that you remove the combine copy of the csv file on your desk top copy.

# Click the "Check DSM .csv for new labs " button

# pick the directory with the new data files

# wait for the check to complete, about half an hour

# if the program tells you there are new labs~~, review them~~ and ~~ask Trish or Allan~~ if any of ~~them should~~ be ~~considered as part~~ of ~~our counts~~; '''~~click "No" for~~ this'''

# '''if the program tells you there are new labs''' click "no" and hold off on the rest of the import as you might have to re-do it if any of the new labs need to be included; follow the steps at [[Validating new types of DSM results]]; '''do this right away''' since some of the queries are based on labs imported within the last hour

~~#* open~~ the ~~following~~ queries ~~('''they~~ are based on labs imported within the last hour~~''')~~

#* If no additional lab, just close it.

#** s_group, sort by "added_dt" and review today's only

~~#** s_mapping_lab, sort by "date_listed" and review today's only~~

~~#* '''~~If any of the new ones are identified by Trish or Allan as needing to be counted then you have to ask Tina or Julie''' to add them to the list; the step to add them is to put the name of the lab they will be counted as into the "Our_Lab_Name" field, but we need to confirm first that this would not result in a double-count.

~~#*'''If NO~~ additional lab~~'''~~, just close it.

=== actual data import ===

Line 37:

Line 54:

* Get the Number of D_ID with no match lab DSM by following the steps below.

** Go to design view and add the Import DtTm

** SentDtTm column on criteria put the date you want to ~~ran~~ and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)

** SentDtTm column on criteria put the date you want to run and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)

** Go to pivot table

** Go to Datasheet view and sort lowest to highest then filter to '''blanks only'''

** Go to External Data then click excel

** Go to X CCMDB DSM Lab imports Archive_raw then to the folder you want to save

** Go to S MED MED_CCMED CCMDB DSM Lab imports Archive_raw then to the folder you want to save

** Click Export data with formatting and layout then rename Ex. (No_Match_DSM_450.xlsx) then save.

Line 59:

Line 74:

=== queries for which we have no page yet ===

==== Duplicate RequestNo ====

~~Should never happen~~ due to ~~how import works~~. ~~I am~~ not ~~even sure what that would~~ check for.

=== D_ID Related Issues - Duplicate RequestNo For Incomplete Profiles sent more than once ===

This is caused by patients having more than one D_ID because

# The location portion of the D_ID has been changed after the requestor list has been sent to DSM.

# The D_ID format has been changed to include Laptop Identifier.

Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.

=== D_ID Related Issues - Missing Lab data for Completes ===

This is also caused by the change in the D_ID after the requestor list has been sent to DSM.

# The location portion of the D_ID has been changed.

# The D_ID format has been changed to include Laptop Identifier.

When uploading the DSM data, the new D_ID is being used for finding matches while the dumped Lab data is identified by the old D_ID. While present in DSM dumped file, no match was found resulting to no lab data for that profile.

=== Missing some Lab Dates ===

Found cases where Lab dates between Accept and Arrived are missing.

# This may be due to the changes done in the dates.

=== Duplicates due to Inconsistent Lab labels ===

Found three labs that are not labeled the same

# 'Arterial Lactat ' vs 'Arterial Lactate'

# 'Blood Gas Gluco ' vs 'Blood Gas Glucos'

# 'Blood Gas Sodiu ' vs ’Blood Gas Sodium'

=== Re-do the DSM matching : 29Oct2020 ===

Julie went back to the dumped DSM data file starting from '''batches 2019-01-23 to 2020-10-14'''. She did the following steps using SAS program

# For each batch of data, identify the profiles with D_ID changes by comparing the requestor list and CCMDB.

## Make a new column adding the new D_ID. The old and new D_ID will be saved in CFE [[L_Problems_D_ID table]].

# Get the Accept DtTm and Dispo DtTm for all records in requestor List from CCMDB L_log.

# Get from the dumped DSM datafile only the needed Lab tests.

# Match the subset dumped DSM data file and the requestor list by old D_ID.

# Get the records where Lab_DtTm is within the (Accept_DtTm or Arrived_DtTm) and Dispo_DtTm.

# Replaced those old D_ID (found in step1) with the New D_ID.

# Check duplicates due to lab labels or changed D_IDs and remove them.

# Compare D_ID of the matched DSM dataset with L_log to determine the unmatched cases (and %).

# Get the lab data from the DSM Database '''before the batch 2019-1-23''' and combine it with the new matched dataset.

## check again for any duplication due to lab labels or changed D_IDs and remove duplicates.

## Make sure the lab data is unique by D_ID, registry no, Lab DtTm, Lab Test and result.

# Import the New_Lab_DSM to the DSM database.

Julie is still thinking to re-send those profiles found with no lab data to DSM. Julie will give Pagasa some more list of changed D_IDs to be added to the CFE [[L_Problems_D_ID table]].

=== Re-do the importing: Sept 8-14, 2022 ===

*Tina updated the import program in ACCESS because matches are not completely done in the previous import program. It was agreed to re-do the importing from Allun's DSM dumped files using the new import program in ACCESS.

*Pagasa did the following steps:

# First, remove from the L_DSM_Data all data imported after Oct 2020.

# Using the new import program, upload the DSM data one batch at a time starting from batch Dec 3, 2020 to batch Aug 31, 2022.

== Known issues ==

Line 74:

Line 127:

[[Category: DSM Labs Extract]]

[[Category:DSM Labs Extract]]

@@ Line 3: / Line 3: @@
 We receive data from DSM that needs to be imported into our main repository.
-== Instructions ==
+== Instructions Using SAS ==
+The ACCESS Import program stopped working after Feb 9,2024 and a temporary solution using SAS was suggested.
+=== Step 1- done by Julie ===
+# In the batch folder of the current DSM data, create a subfolder labeled JM and save the SAS program labeled DSM_import_version2.sas
+# Run the SAS program and the following outputs are generated:
+## an excel file containing New labs is exported. The list has to be emailed to Allan Garland for confirmation. If new labs appear, Julie will add them to the SAS codes and re-run the SAS program.
+## an excel file containing the matching DSM labs using the criteria that either the LAB dttm or Collect dttm  is between the [[Admit date and time]] and [[Dispo DtTm]] in worksheet '''MatchDSM'''.
+## an excel file containing the unmatched D_IDs
+### the worksheet '''DSM_NoDID''' contains the D_ID where PHIN or chart number are not present in the dumped DSM data file.
+### the worksheet '''Log_NoDID''' contains the D_ID in the requestor list but not found in the L_LOG table of CFE.  This happens when the D_ID has been changed for some reason after the request has been sent already. if no D_ID label changing happens, then the worksheet will be empty. If not empty, then the SAS program must be modified to incorporate the D_ID label change  and  also has to be written in the [[L_Problems_D_ID table]] if not entered yet. Presumably the entry to the L_Problems_D_ID table must precede the SAS output.
+### the worksheet  '''nomatchDSMLab''' contains the D_ID present in the dumped but no LAB dttm or Collect dttm  fall  between the [[Admit date and time]] and [[Dispo DtTm]].  The file has the same columns with the requestor list plus the LOS sorted from highest to lowest.  Shorter LOS (<=2days) may possibly have no lab requests but those with longer LOS may have just missed.
+### the worksheet '''LOSgt2_resend''' contains the D_IDs with  LOS > 2d to be resent on the '''next batch''' of sending to confirm if really has no lab data following the [[Instructions for requesting a batch of data from DSM]].  This step of resending has stopped  07March2025 because the resent profiles still showed no Lab data on the next batch.
+=== Step 2 - done by Pagasa ===
+# Open the DSM_Labs_data.accdb
+## Goto the External Data and click new datasource from file Excel
+## Choose the excel file where the final matchedDSM is saved and append to L_Labs_DSM
+## Update the Date imported and write comments if any to the WIKI https://ccmdb.kuality.ca/index.php?title=Instructions_for_requesting_a_batch_of_data_from_DSM
+# Close the accdb.
+== Instructions  Using ACCESS ==
 === Prep ===
@@ Line 9: / Line 30: @@
 #* make a new local directory
 #* Put the following into the directory
-#** [[Centralized_data.mdb]]
+#** [[Centralized_data.accdb]]
 #** [[DSM_Labs_Consistency_check.accdb]]
 #** [[DSM Labs data.accdb]]
 #** '''PHI not needed''' - [[PHI.mdb]] is not needed during import because it matches by D_ID
-# copy the newly received batch of data from [[DSM_Lab_Extract#File_Share]] to a '''different local directory that contains '''only those files'''not the requestor file.
+# copy the newly received batch of data from [[DSM_Lab_Extract#File_Share]] to a '''different local directory that contains '''only those files'''not the requestor file & '''''''''the csv with no(#) beside it.'''
-# open [[DSM_Labs_Consistency_check.accdb]]
 === Check for new labs ===
 # Open [[DSM_Labs_Consistency_check.accdb]]
+# Make sure that you remove the combine copy of the csv file on your desk top copy.
 # Click the "Check DSM .csv for new labs " button
 # pick the directory with the new data files
 # wait for the check to complete, about half an hour
-# if the program tells you there are new labs, review them and ask Trish or Allan if any of them should be considered as part of our counts; '''click "No" for this'''
+# '''if the program tells you there are new labs''' click "no" and hold off on the rest of the import as you might have to re-do it if any of the new labs need to be included; follow the steps at [[Validating new types of DSM results]]; '''do this right away''' since some of the queries are based on labs imported within the last hour
-#* open the following queries ('''they are based on labs imported within the last hour''')
+#* If no additional lab, just close it.
-#** s_group, sort by "added_dt" and review today's only
-#** s_mapping_lab, sort by "date_listed" and review today's only
-#* '''If any of the new ones are identified by Trish or Allan as needing to be counted then you have to ask Tina or Julie''' to add them to the list; the step to add them is to put the name of the lab they will be counted as into the "Our_Lab_Name" field, but we need to confirm first that this would not result in a double-count.
-#*'''If NO additional lab''', just close it.
 === actual data import ===
@@ Line 37: / Line 54: @@
 * Get the Number of D_ID with no match lab DSM by following the steps below.
 ** Go to design view and add the Import DtTm
-** SentDtTm column on criteria put the date you want to ran and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)
+** SentDtTm column on criteria put the date you want to run and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)
-** Go to pivot table
-** Go to Datasheet view and sort lowest to highest then filter to '''blanks only'''
 ** Go to External Data then click excel
-** Go to X CCMDB DSM Lab imports Archive_raw then to the folder you want to save
+** Go to S MED MED_CCMED CCMDB DSM Lab imports Archive_raw then to the folder you want to save
 ** Click Export data with formatting and layout then rename Ex. (No_Match_DSM_450.xlsx) then save.
@@ Line 59: / Line 74: @@
 === queries for which we have no page yet ===
-==== Duplicate RequestNo ====
-Should never happen due to how import works. I am not even sure what that would check for.
+=== D_ID Related Issues - Duplicate RequestNo For Incomplete Profiles sent more than once ===
+This is caused by patients having more than one D_ID because
+# The location portion of the D_ID has been changed after the requestor list has been sent to DSM.
+# The D_ID format has been changed to include Laptop Identifier.
+Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.
+=== D_ID Related Issues - Missing Lab data for Completes ===
+This is also caused by the change in the D_ID after the requestor list has been sent to DSM.
+# The location portion of the D_ID has been changed.
+# The D_ID format has been changed to include Laptop Identifier.
+When uploading the DSM data, the new D_ID is being used for finding matches while the dumped Lab data is identified by the old D_ID. While present in DSM dumped file, no match was found resulting to no lab data for that profile.
+=== Missing some Lab Dates ===
+Found cases where Lab dates between Accept and Arrived are missing.
+# This may be due to the changes done in the dates.
+=== Duplicates due to Inconsistent Lab labels ===
+Found three labs that are not labeled the same
+# 'Arterial Lactat ' vs 'Arterial Lactate'
+# 'Blood Gas Gluco ' vs 'Blood Gas Glucos'
+# 'Blood Gas Sodiu ' vs ’Blood Gas Sodium'
+=== Re-do the DSM matching : 29Oct2020 ===
+Julie went back to the dumped DSM data file starting from '''batches 2019-01-23 to 2020-10-14'''. She did the following steps using SAS program
+# For each batch of data, identify the profiles with D_ID changes by comparing the requestor list and CCMDB.
+## Make a new column adding the new D_ID.  The old and new D_ID will be saved in CFE [[L_Problems_D_ID table]].
+# Get the Accept DtTm and Dispo DtTm for all records in requestor List from CCMDB L_log.
+# Get from the dumped DSM datafile only the needed Lab tests.
+# Match the subset dumped DSM data file and the requestor list by old D_ID.
+# Get the records where Lab_DtTm is within the (Accept_DtTm or Arrived_DtTm) and Dispo_DtTm.
+# Replaced those old D_ID (found in step1) with the New D_ID.
+# Check duplicates due to lab labels or changed D_IDs and remove them.
+# Compare D_ID of the matched DSM dataset with L_log to determine the unmatched cases (and %).
+# Get the lab data from the DSM Database '''before the batch 2019-1-23''' and combine it with the new matched dataset.
+## check again for any duplication due to lab labels or changed D_IDs and remove duplicates.
+## Make sure the lab data is unique by D_ID, registry no, Lab DtTm, Lab Test and result.
+# Import the New_Lab_DSM to the DSM database.
+Julie is still thinking to re-send those profiles found with no lab data to DSM. Julie will give Pagasa some more list of changed D_IDs to be added to the CFE [[L_Problems_D_ID table]].
+=== Re-do the importing: Sept 8-14, 2022 ===
+*Tina updated the import program in ACCESS because matches are not completely done in the previous import program.  It was agreed to re-do the importing from Allun's DSM dumped files using the new import program in ACCESS.
+*Pagasa  did the following steps:
+# First, remove from the L_DSM_Data all data imported after Oct 2020.
+# Using the new import program, upload the DSM data one batch at a time starting from batch  Dec 3, 2020 to batch Aug 31, 2022.
 == Known issues ==
@@ Line 74: / Line 127: @@
 {{Related Articles}}
-[[Category: DSM Labs Extract]]
+[[Category:DSM Labs Extract]]