Monitor product shortage predictions

When using predictions generated from the Product Availability Intelligence system, Owners have to look up the NDC category and industry classification code in their internal systems which can be time-consuming. To help with this process, Owners can now provide the NDC Category (NDCCategory) and the Industry Classification Code (UserClassCode) values to TraceLink as optional input data in addition to the NDC numbers for which the predictions are generated. When prediction files are generated, NDC, NDCCategory, and UserClassCode values are included in the output CSV files. The values in the prediction file help Owners to match their internal codes and categorization (primary and alternative) correctly to the NDCs. Owners no longer have to manually look up information in an external database or take the time to analyze prediction data.

Owners can provide the values for NDC, NDCCategory, and UserClassCode in a comma-separated file (CSV) format and upload to TraceLink via an SFTP to the folder inbox/PAI_NDC_LIST. The CSV file name is PAI_NDC_list.csv.

• NDC – Required. The 11-digit National Drug Code (NDC) that the prediction is made for.
• NDCCategory – Optional. An internal categorization of NDCs (e.g. primary, alternate).
• UserClassCode – Optional. An industry drug classification coding number to find alternative drugs (e.g. GPI and SPID are common standard coding types).

For each NDC:

• If the NDCCategory is provided, UserClassCode is also required, and vice versa.

• If only the NDCCategory is provided, leave the column blank for UserClassCode in the CSV file.

• If only the UserClassCode is provided, leave the column blank for NDCCategory in the CSV file.
• If the NDCCategory and UserClassCode are not provided, add a single space for each NDCCategory and UserClassCode value.

Do not include the NDC, NDCCategory, and UserClassCode header names in the CSV file.

Product Availability Intelligence system polls for new inbound file every hour. Once a file is validated and accepted for daily shortage prediction, an email notification is sent to the Owner. Product Availability Intelligence uses only the values from the latest valid CSV file to generate the shortage prediction. If the information is not provided in any of the NDC rows, the entire input file is rejected and an email is generated with error details and sent to the Owner. In this scenario, the older uploaded CSV file is used to generate the daily shortage predictions.

Once the latest valid CSV file is processed for shortage predictions, the file is moved to an archive directory inbox/PAI_NDC_LIST/ARCHIVE for record keeping. Owners can remove the archived files. Product Availability Intelligence uses only the values from the latest valid CSV file to generate the shortage prediction. It does not use values from earlier uploaded files to generate the shortage prediction.

Examples: Valid CSV input with additional data

NDC Values	Information
12345-6767-81,Primary,3435772	NDC, NDCCategory, and UserClassCode values are provided.
56778-8726-21,Alternate,7882XG7	NDC, NDCCategory, and UserClassCode values are provided.
73878-2892-21, ,738800011	Only NDC and UserClassCode are provided. A single space is added for NDCCategory.
73878-2922-01	Only NDC value is provided.
73878-2922-01,NA,	Only NDC and UserClassCode values are provided. A single space is added for UserClassCode.
73878-2922-01, ,	Only NDC value is provided. For NDCCategory and UserClassCode, a single space is added.

Examples: Invalid CSV input with additional data

As some of the rows are not valid, the entire input CSV file is rejected.

NDC Values	Information
12345-6767-81,3435772	This is not a valid row as only NDC and UserClassCode values are provided. No value or space is added for NDCCategory.
56778-8726-21,Alternate	This is not a valid row as only NDC and NDCCategory values are provided. No value or space is added for UserClassCode.
73878-2922-01	This is a valid row as only NDC value is added.
73878-2922-01,NA,	This is a valid row as only NDC and UserClassCode values are provided. A single space is added for UserClassCode.

Watch the video!

• Connect to TraceLink using an SFTP client (e.g. WinSCP, FileZilla).

• Contact the TraceLink Services team for information about connecting to TraceLink using SFTP.

Product Availability Intelligence provides two types of CSVs to access the prediction data for specific NDCs. One is targeted for human users, and the other is targeted for end-to-end integration with a customer analytics system (e.g. import into a database).

Watch the video!

1. Connect to TraceLink using an SFTP connection.
2. Select the PAI_DAILY_SHORTAGE_PREDICTIONS folder.

3. Select the file with the date that the prediction was generated (e.g. today's date). There are two prediction files:

   • PAI_Daily_Shortage_Predictions_YYYY_MM_DD.csv – This file is used to integrate with a customer analytics system (e.g. import into a database).
   • PAI_DAILY_SHORTAGE_PREDICTIONS_SUMMARY_REPORT_YYYY_MM_DD.csv – This file contains the average of all the past prediction values received for each target date included in dedicated columns for easy human-readable analysis.

     For example, for 04 Apr 2023, there would be 5 predictions in the past (as part of 10 days, 30, 45, 60, and 90 days time periods). The summary report file contains the average of the 5 predictions and displays it under the 04/24/2023 column.

   Product Availability Intelligence generates a new file each day with that day's predictions.

   The file opens.

   See the information that displays in the CSV file with the name format PAI_Daily_Shortage_Predictions_YYYY_MM_DD.csv

   • NDC – The 11-digit National Drug Code (NDC) that the prediction is made for.
   • Drug Name – The FDA-defined drug name of the product.
   • Description – The FDA-defined description of the NDC, including the dosage form, active numerator strength, and active ingredient unit.
   • Package Description – The FDA-defined description of the product's packaging (e.g. 4 bottles in 1 carton and 100 tablets in 1 bottle). For multilevel packages, the descriptions are concatenated together.
   • Labeler Name – The name of the pharmaceutical manufacturer that owns the license for the product, as on record with the FDA.
   • Generic Name – The FDA-defined generic name of the product.
   • Route of Administration – The method used to administer the product to the patient, as on record with the FDA.
   • Date Prediction Targeted – The date that the NDC prediction is expected to occur.
   • Probability – The likelihood that a shortage of the NDC will occur on the date that the prediction is targeted for (e.g. 0.8 or 80%).
   • Accuracy – The expected accuracy of the shortage prediction probability, based on past data from ASHP (e.g. 0.8 or 80%). 
   • ASHP Shortage Status – The NDC shortage status as identified by the American Society of Health-System Pharmacists (ASHP).
     • Y – The ASHP has identified the NDC to be in shortage as of the date the CSV file is generated.
     • N – The ASHP has not identified the NDC to be in shortage as of the date the CSV file is generated.

     Drug Shortage Bulletins are copyrighted by the Drug Information Service of the University of Utah, provided by ASHP as its exclusive authorized distributor, and used with permission. ASHP and the University of Utah make no representations or warranties, express or implied, including, but not limited to, any implied warranty of merchantability and/or fitness for a particular purpose, with respect to such information, and specifically disclaim all such warranties. Users of this information are advised that decisions regarding the use of drugs and drug therapies are complex medical decisions and that in using this information, each user must exercise his or her own independent professional judgment. Neither Contract No.: 3.11.337 (a) - 3 – (Final 1.May.2022) ASHP nor the University of Utah assumes any liability for persons administering or receiving drugs or other medical care in reliance upon this information, or otherwise in connection with this Bulletin. Neither ASHP nor the University of Utah endorses or recommends the use of any particular drug.

   • Prediction Model ID – The ID of the predictive analytics model used to generate the prediction.
   See the information that displays in the CSV file with the name format PAI_DAILY_SHORTAGE_PREDICTIONS_SUMMARY_REPORT_YYYY_MM_DD.csv

   • NDC – The 11-digit National Drug Code (NDC) that the prediction is made for.
   • Drug Name – The FDA-defined drug name of the product.
   • Generic Name – The FDA-defined generic name of the product.
   • UserClassCode – An industry drug classification coding number to find alternative drugs (e.g. GPI and SPID). Cannot exceed 40 characters and cannot contain a comma.
   • NDC Category – An internal categorization of NDCs (e.g. primary, alternate). Cannot exceed 40 characters and cannot contain a comma.
   • Labeler – The name of the pharmaceutical manufacturer that owns the license for the product, as on record with the FDA.
   • Dosage – The FDA-defined dosage of the product.
   • Strength – The FDA-defined active numerator strength of the product.
   • Package Size – The FDA-defined package size of the product (e.g. 4 bottles in 1 carton and 100 tablets in 1 bottle). For multilevel packages, the descriptions are concatenated together.
   • Route – The method used to administer the product to the patient, as on record with the FDA.
   • State (latest) – The NDC shortage status as identified by the American Society of Health-System Pharmacists (ASHP).
     • SHORTAGE – The ASHP has identified the NDC to be in shortage as of the date the CSV file is generated.
     • NOSHORTAGE – The ASHP has not identified the NDC to be in shortage as of the date the CSV file is generated.
     • UNKNOWN - The ASHP has not identified any state for the NDC as of the date the CSV file is generated.
   • Probability of Shortage in 1 Day – Indicates the average of all the past predictions (up to 5 predictions) for 1 day in the future.
   • Probability of Shortage in 10 Days – Indicates the average of all the past predictions (up to 5 predictions) for 10th day in the future.
   • Probability of Shortage in 30 Days – Indicates the average of all the past predictions (up to 4 predictions) for 30th day in the future.
   • Probability of Shortage in 45 Days – Indicates the average of all the past predictions (up to 3 predictions) for 45th day in the future.
   • Probability of Shortage in 60 Days – Indicates the average of all the past predictions (up to 2 prediction) for 60th day in the future.
   • Probability of Shortage in 90 Days – Indicates the prediction for 90th day in the future.
   • 90-Day Average – The average of all the prediction values available for a duration of 90 days.
   • 90-Day Trend (%/day) – The change of shortage probability per day in percent. For example, a value of -0.1126 means that for each day going forward the trend line goes down by 0.1126 percentage points. Therefore, if today's trend line value is 50.1125 percent, tomorrow's trend value is 49.9999 percent. The trend is computed using the probability of shortage from the two end points:

     • The day after the prediction file is generated.

     • 90 days after the date the prediction file is generated
   • 50% Crossing and Direction – The number of days from when the prediction file is generated and the shortage probability is expected to cross 50%. The trend is computed using the probability of shortage from the two end points:

     • The day after the prediction file is generated.

     • 90 days after the date the prediction file is generated

   Crossing over direction can be from:

   • Ascending – The shortage probability will cross over from below 50% to above 50%.
   • Descending – The shortage probability will cross over from above 50% to below 50%.

   If the shortage probability has already crossed over the 50% threshold, this column shows how far in the past the threshold cross over has occurred.

   A probability for shortage must be available for 1 day after and 90 days after the prediction file is generated to calculate the trend value. When there is not enough data to compute the trend, the crossing value is listed as Insufficient data.

   Valid values are:
   • 0-30 days ascending
   • 0-30 days descending
   • 31-60 days ascending
   • 31-60 days descending
   • 61-90 days ascending
   • 61-90 days descending
   • 91-180 days ascending
   • 91-180 days descending
   • 181-365 days ascending
   • 181-365 days descending
   • > 365 days ascending
   • > 365 days descending
   • 0-10 days ago ascending
   • 0-10 days ago descending
   • 11-30 days ago ascending
   • 11-30 days ago descending
   • > 30 days ago ascending
   • > 30 days ago descending
   • No crossing
   • Insufficient data

1. Select [Company Name] Product Availability Intelligence from the Network drop-down in the header.
2. Select PAI Prediction Result from the Process drop-down in the header.
3. Select the Go button in the header.

   The PAI Daily Report - Consolidated Summary table displays.

   Product Availability Intelligence generates a new file each day with that day's predictions.

   See the information that displays in the table

   • NDC – The 11-digit National Drug Code (NDC) that the prediction is made for.
   • Drug Name – The FDA-defined drug name of the product.
   • Generic Name – The FDA-defined generic name of the product.
   • UserClassCode – An industry drug classification coding number to find alternative drugs (e.g. GPI and SPID). Cannot exceed 40 characters and cannot contain a comma.
   • NDC Category – An internal categorization of NDCs (e.g. primary, alternate). Cannot exceed 40 characters and cannot contain a comma.
   • Labeler – The name of the pharmaceutical manufacturer that owns the license for the product, as on record with the FDA.
   • Dosage – The FDA-defined dosage of the product.
   • Strength – The FDA-defined active numerator strength of the product.
   • Package Size – The FDA-defined package size of the product (e.g. 4 bottles in 1 carton and 100 tablets in 1 bottle). For multilevel packages, the descriptions are concatenated together.
   • Route – The method used to administer the product to the patient, as on record with the FDA.
   • State (latest) – The NDC shortage status as identified by the American Society of Health-System Pharmacists (ASHP).
     • SHORTAGE – The ASHP has identified the NDC to be in shortage as of the date the CSV file is generated.
     • NOSHORTAGE – The ASHP has not identified the NDC to be in shortage as of the date the CSV file is generated.
     • UNKNOWN - The ASHP has not identified any state for the NDC as of the date the CSV file is generated.
   • Probability of Shortage in 1 Day – Indicates the average of all the past predictions (up to 5 predictions) for 1 day in the future.
   • Probability of Shortage in 10 Days – Indicates the average of all the past predictions (up to 5 predictions) for 10th day in the future.
   • Probability of Shortage in 30 Days – Indicates the average of all the past predictions (up to 4 predictions) for 30th day in the future.
   • Probability of Shortage in 45 Days – Indicates the average of all the past predictions (up to 3 predictions) for 45th day in the future.
   • Probability of Shortage in 60 Days – Indicates the average of all the past predictions (up to 2 prediction) for 60th day in the future.
   • Probability of Shortage in 90 Days – Indicates the prediction for 90th day in the future.
   • 90-Day Average – The average of all the prediction values available for a duration of 90 days.
   • 90-Day Trend (%/day) – The change of shortage probability per day in percent. For example, a value of -0.1126 means that for each day going forward the trend line goes down by 0.1126 percentage points. Therefore, if today's trend line value is 50.1125 percent, tomorrow's trend value is 49.9999 percent. The trend is computed using the probability of shortage from the two end points:

     • The day after the prediction file is generated.

     • 90 days after the date the prediction file is generated
   • 50% Crossing and Direction – The number of days from when the prediction file is generated and the shortage probability is expected to cross 50%. The trend is computed using the probability of shortage from the two end points:

     • The day after the prediction file is generated.

     • 90 days after the date the prediction file is generated

   Crossing over direction can be from:

   • Ascending – The shortage probability will cross over from below 50% to above 50%.
   • Descending – The shortage probability will cross over from above 50% to below 50%.

   If the shortage probability has already crossed over the 50% threshold, this column shows how far in the past the threshold cross over has occurred.

   A probability for shortage must be available for 1 day after and 90 days after the prediction file is generated to calculate the trend value. When there is not enough data to compute the trend, the crossing value is listed as Insufficient data.

   Valid values are:
   • 0-30 days ascending
   • 0-30 days descending
   • 31-60 days ascending
   • 31-60 days descending
   • 61-90 days ascending
   • 61-90 days descending
   • 91-180 days ascending
   • 91-180 days descending
   • 181-365 days ascending
   • 181-365 days descending
   • > 365 days ascending
   • > 365 days descending
   • 0-10 days ago ascending
   • 0-10 days ago descending
   • 11-30 days ago ascending
   • 11-30 days ago descending
   • > 30 days ago ascending
   • > 30 days ago descending
   • No crossing
   • Insufficient data

Tips

• Select Download Predictions Summary Report to download the prediction file in CSV format.
• Select any NDC link to display a Time Series chart with for the NDC that shows two charts of the prediction probability value over time:

  • The upper pane shows the predictions generated for 10, 30, 45, 60, and 90 days from the current date.

  • The lower pane shows:

    • Green plots – The average of predictions generated for 10, 30, 45, 60, and 90 days model from each day.

    • Block plot – A moving average line of all plotted green values.

    • Trend line – A trend line drawn from prediction average value for today until the prediction average value of 90 days from the current date.

• Select any Generic Name link to display a Heatmap of all the NDCs that belong to a single generic drug category.

• Sort the table based on the columns.
• Rearrange the columns of the table based on users preferences.

Search

• Enter the search criteria and system narrows down the results to the rows of the NDCs that matches the criteria in any columns. For example, if NDC table initially has 20,000 plus rows and user wants to narrow down the list to NDCs related to Amoxicillin, user can type Amox in the Search field. System will narrow down the results to rows of NDCs that contain the search string ‘Amox’ in any of the columns. Generic name, proprietary name columns containing Amoxicillin only appears in the table.