1 / 20

Handling Ambiguous Dates in SAS: A Comprehensive Macro Solution

This paper presents a detailed macro solution for managing ambiguous date formats in SAS, authored by Charlotte King and John Fleming from Alberta Health Services. It addresses the common pitfalls associated with identifying and standardizing date values across datasets. The solution leverages a macro named `%macro.dates` to clean and format date variables effectively by accommodating messy input formats and facilitated data cleansing, ensuring accurate date interpretation and analysis. This presentation is vital for analysts working with historical data or varied date formats.

lana
Télécharger la présentation

Handling Ambiguous Dates in SAS: A Comprehensive Macro Solution

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. When “ANYDATE” Doesn’t Mean Any Date: A Macro Solution Charlotte King, Alberta Health Services John Fleming, Alberta Health Services SAS Global Forum Paper 117 - 2011 Edmonton SAS User Group May 11, 2011

  2. Problem

  3. Macro %macro dates(inds, outds, messy, clean, year_start, missing_year_start_date, missing_year, dstyle); /* MACRO Part 1 SAS Code Here */ /* MACRO Part 2 SAS Code Here */ /* MACRO Part 3 SAS Code Here */ %mend dates;

  4. Macro %macro dates(inds, outds, messy, clean, year_start, missing_year_start_date, missing_year, dstyle); /* MACRO Part 1 SAS Code Here */ /* MACRO Part 2 SAS Code Here */ /* MACRO Part 3 SAS Code Here */ %mend dates; Input And Output Data Sets

  5. Macro %macro dates(inds, outds, messy, clean, year_start, missing_year_start_date, missing_year, dstyle); /* MACRO Part 1 SAS Code Here */ /* MACRO Part 2 SAS Code Here */ /* MACRO Part 3 SAS Code Here */ %mend dates; Dirty And Cleaned Date Variables

  6. Macro %macro dates(inds, outds, messy, clean, year_start, missing_year_start_date, missing_year, dstyle); /* MACRO Part 1 SAS Code Here */ /* MACRO Part 2 SAS Code Here */ /* MACRO Part 3 SAS Code Here */ %mend dates; Date Parameters

  7. Macro %macro dates(inds, outds, messy, clean, year_start, missing_year_start_date, missing_year, dstyle); /* MACRO Part 1 SAS Code Here */ /* MACRO Part 2 SAS Code Here */ /* MACRO Part 3 SAS Code Here */ %mend dates; Date Style Option

  8. Ambiguous Dates January 2, 2003? January 3, 2002? February 3, 2001? March 2, 2001? February 1, 2003? March 1, 2002? (MDY) (MYD) (YMD) (YDM) (DMY) (DYM) How Do We Read This Date? 01/02/03 Which Of These Is It?

  9. Ambiguous Dates options datestyle = mdy|myd|ymd|ydm|dmy|dym|locale;

  10. Macro Part 1 data _null_; year_end = &year_start + 99; call symput('year_end',year_end); missing_year_end = mdy(month("&missing_year_start_date"d), day("&missing_year_start_date"d), year("&missing_year_start_date"d + 99)); call symput('missing_year_end_date',missing_year_end); run;

  11. Macro Part 2 libname dateform "T:\date macro "; options yearcutoff = &year_start fmtsearch = (dateform) datestyle = &dstyle; %include "T:\date macro\date formats.sas";

  12. Custom Informats proc format; picture mondyyyy (default=11) other ='%b %d %Y' (datatype=date); run; data infmt3; retain fmtname "mondyyyy" type "I"; do label = "1jan1900"d to "31dec2025"d; start = put (label,mondyyyy.); start = trim ( left (start) ); start=compress(compress(lowcase(start), ,'s'), ,'p'); output; end; hlo='O'; label=.; start=' '; output; run; proc format cntlin=infmt3; run;

  13. Custom Informats proc format; picture mondyyyy (default=11) other ='%b %d %Y' (datatype=date); run; data infmt3; retain fmtname "mondyyyy" type "I"; do label = "1jan1900"d to "31dec2025"d; start = put (label,mondyyyy.); start = trim ( left (start) ); start=compress(compress(lowcase(start), ,'s'), ,'p'); output; end; hlo='O'; label=.; start=' '; output; run; proc format cntlin=infmt3; run;

  14. Custom Informats proc format; picture mondyyyy (default=11) other ='%b %d %Y' (datatype=date); run; data infmt3; retain fmtname "mondyyyy" type "I"; do label = "1jan1900"d to "31dec2025"d; start = put (label,mondyyyy.); start = trim ( left (start) ); start=compress(compress(lowcase(start), ,'s'), ,'p'); output; end; hlo='O'; label=.; start=' '; output; run; proc format cntlin=infmt3; run;

  15. Custom Informats proc format; picture mondyyyy (default=11) other ='%b %d %Y' (datatype=date); run; data infmt3; retain fmtname "mondyyyy" type "I"; do label = "1jan1900"d to "31dec2025"d; start = put (label,mondyyyy.); start = trim ( left (start) ); start=compress(compress(lowcase(start), ,'s'), ,'p'); output; end; hlo='O'; label=.; start=' '; output; run; proc format cntlin=infmt3; run;

  16. Custom Informats proc format; picture mondyyyy (default=11) other ='%b %d %Y' (datatype=date); run; data infmt3; retain fmtname "mondyyyy" type "I"; do label = "1jan1900"d to "31dec2025"d; start = put (label,mondyyyy.); start = trim ( left (start) ); start=compress(compress(lowcase(start), ,'s'), ,'p'); output; end; hlo='O'; label=.; start=' '; output; run; proc format cntlin=infmt3; run;

  17. Macro Part 3 data &outds; set &inds; &messy = compress(compress(lowcase(&messy), ,'s'), ,'p');

  18. Macro Part 3 &clean=input(&messy, worddate.); if missing(&clean) then &clean=input(&messy, anydtdte.); if missing(&clean) then &clean=input(&messy, worddatx.); if missing(&clean) then &clean=input(lowcase(&messy), mondyyyy.); if missing(&clean) then &clean=input(&messy, mondyy.); if missing(&clean) then &clean=input(&messy, monddyyyy.); if missing(&clean) then &clean=input(&messy, monddyy.); if missing(&clean) then &clean=input(&messy, ddmon.); if missing(&clean) then &clean=input(&messy, dmon.); if missing(&clean) then &clean=input(&messy, montddyy.); if missing(&clean) then &clean=input(&messy, montdyy.); format &clean date9.; run; %mend dates;

  19. Summary • Parameters • dsin, dsout, • messy, clean • year_start, missing_year_start_date • missing_year • dstyle • Robust and Expandable • Future Versions

  20. Contact Information Charlotte King Charlotte.King@albertahealthservices.ca John Fleming John.Fleming@albertahealthservices.ca Community Oncology Alberta Health Services – Cancer Care http:www.albertahealthservices.ca

More Related