• Banner 201707

    INTRODUCING

    Fast, highly accurate platform for data mining and predictive analytics

  • Banner 201707

    INTRODUCING

    Fast, highly accurate platform for data mining and predictive analytics

Download Now Instant Evaluation
Get Price Quote

Working With Date Variables

There are a variety of ways to represent dates in data files and there is standard, which can make life difficult if one is trying to use date variables in a predictive model. Two of the more common representations are the Microsoft date format (used in Excel and other Microsoft products) , which is the number of days since December 30, 1899; and the SAS date format, which is the number of days since January 1, 1960. For the sake of establishing consistency, the data access library used by SPM converts all date variables to Microsoft dates. The advantage of doing so is that one does not have to guess how dates are represented in the input dataset and Microsoft products are common; the disadvantage is that you might be confused if you are using non-Microsoft products (like SAS) to manage your data.

For example, if one has a variable in a SAS dataset named BIRTHDATE, which is formatted as a date, SPM will automatically convert it to a Microsoft date when reading it in, even though the actual representation in the input dataset is a SAS date. Thus, if BIRTHDATE='29Mar1974'd on the SAS dataset, it will be read as 27117 (the Microsoft value) by SPM, instead of 5202 (the SAS value). Furthermore, no conversion will be made when the SPM scores or otherwise saves the dataset; so BIRTHDATE will be a Microsoft date in any output datasets SPM might create. One of the consequences of this is that if BIRTHDATE is used in a model, any coefficients or split points will based on its values as a Microsoft date, rather than as a SAS date (important if one translates the model to SAS).

There are several work-arounds for this:
1. In SAS, one can strip the formats of any date variables before saving the the dataset to be read by SPM, for example:
*strip the date format from BIRTHDATE;
format birthdate;

2. In SPM, one can redefine any date variables as SAS dates, like so:
%let birthdate=birthdate-21916
In this case, it is important to make the same transformations when scoring new data with any models built using the date variable.

3. After scoring the data, one can redefine the relevant date variables as SAS dates in SAS:
format birthdate date.;
birthdate=birthdate+'30dec1899'd;

4. One can avoid the direct use of date variables entirely by using relative measures of time instead. We recommend this as any dates used to build predictive models will always be in the past and will therefore never be seen again. For example (in SPM):
rem Age when account opened
%let startage=(startdate-birthdate)/365.25
It should be noted that the SAS code examples above can easily be adapted to whatever programming language, database manager, or statistical package one cares to use.

[J#354:1602]

Tags: Frequently Asked Questions, FAQs, Support, SPM, Salford-Systems

  • SPM Version 8 Just Released!

    SPM Version 8 Just Released!

    NEW Salford Predictive Modeler software suite.

    Read more

  • Environmental Forecasting

    Environmental Forecasting

    Forecast the evolution of environmental outcomes using changes in habitat and climate as predictors.
  • Sports Analytics

    Sports Analytics

    "Discover the undisclosed predictors to successful athletic performance using modern decision trees."
  • Targeted Marketing

    Targeted Marketing

    Enabling you to get appropriate prospective customers more efficiently than any other marketing strategies.
  • Text Mining

    Text Mining

    Derive high-quality information from text to improve your understanding of behaviours and patterns.
  • Bioinformatics

    Bioinformatics

    "Increase your probability of solving formal and practical challenges arising from the analysis of biological data."
  • Bioinformatics

    Bioinformatics

    Learn how to make knowledge-driven decisions that can revolutionize your business performance.
  • Financial Services

    Financial Services

    Analyze your spending and financial investments to help influence a profitable future for your company
  • Industrial Optimisation

    Industrial Optimisation

    Overcome retail challenges and achieve new levels of predictive accuracy, profitability and reliability.
  • Music

    Music

    Predict musical score groupings, composers that complement each other and what song listeners prefer to listen to.
  • Retail Analytics

    Retail Analytics

    Make smarter decisions to help manage your business more effectively and efficiently.
  • SPM Version 8 Just Released!

    SPM Version 8 Just Released!

    Salford Systems' applications span every major industry and business function

    Read more

  • Environmental Forecasting

    Environmental Forecasting

    Forecast the evolution of environmental outcomes using changes in habitat and climate as predictors.
  • Sports Analytics

    Sports Analytics

    Discover the undisclosed predictors to successful athletic performance using modern decision trees.
  • Targeted Marketing

    Targeted Marketing

    Enabling you to get appropriate prospective customers more efficiently than any other marketing strategies.
  • Text Mining

    Text Mining

    Derive high-quality information from text to improve your understanding of behaviours and patterns.
  • Bioinformatics

    Bioinformatics

    Increase your probability of solving formal and practical challenges arising from the analysis of biological data.
  • Business

    Business

    Learn how to make knowledge-driven decisions that can revolutionize your business performance.
  • Financial Services

    Financial Services

    Analyze your spending and financial investments to help influence a profitable future for your company
  • Industrial Optimisation

    Industrial Optimisation

    Overcome retail challenges and achieve new levels of predictive accuracy, profitability and reliability.
  • Music

    Music

    Predict musical score groupings, composers that complement each other and what song listeners prefer to listen to.
  • Retail Analytics

    Retail Analytics

    Make smarter decisions to help manage your business more effectively and efficiently.

Get In Touch With Us

Request online support

Ph: 619-543-8880
9685 Via Excelencia, Suite 208, San Diego, CA 92126