{"id":207,"date":"2014-03-12T21:11:57","date_gmt":"2014-03-12T21:11:57","guid":{"rendered":"http:\/\/www.sebcharrot.com\/blog\/?p=207"},"modified":"2014-03-12T21:11:57","modified_gmt":"2014-03-12T21:11:57","slug":"converting-custom-date-formats-in-sas-information-map-studio","status":"publish","type":"post","link":"http:\/\/www.sebcharrot.com\/blog\/converting-custom-date-formats-in-sas-information-map-studio\/","title":{"rendered":"Converting custom date formats in SAS Information Map Studio"},"content":{"rendered":"

\"\"<\/a>Background<\/h1>\n

Let\u2019s assume you have some dates in a custom format:<\/p>\n

\u00a0 date20131007 month102013<\/pre>\n
SAS Reports need to be able to a) present dates in a human readable format and b) understand dates to allow filtering and other funky stuff.<\/p>\n
For that reason we need a way of translating these custom dates into SAS dates.<\/p>\n
Step 1 – Get an Information Map with a date field<\/h1>\n
Use an existing one, or see\u00a0http:\/\/support.sas.com\/kb\/35\/471.html<\/a> for more information on creating an Information Map.<\/p>\n
Step 2 – Edit the Expression of your date field<\/h1>\n
    \n
  1. Open the Properties for your date field.<\/li>\n
  2. Then on the Definition tab, click “Edit” in the “Expression Settings” section.<\/li>\n<\/ol>\n
    Step 3 – Magic<\/h1>\n
      \n
    1. Change the Type to Date so that SAS can treat it as a date field from now on.<\/li>\n
    2. Then change the Expression Text to something like this:<\/li>\n<\/ol>\n
      input(substr(<<mytable.mydatefield>>,5,8), yymmdd8.)<\/pre>\n
      wat<\/h1>\n
      What’s happening here is that we’re translating the custom date string into a SAS date using what’s known as an INFORMAT.<\/p>\n
      SUBSTR\u00a0(string, 5, 8) – Takes a substring from the given string, starting at character 5, with a length of 8 characters. In other words extracting the string date20131007<\/strong> month102013<\/p>\n
      INPUT (string, yymmdd8.) – Takes a string and interprets it using the informat “yymmdd8.”. That informat is provided by default with SAS. What we’re doing here is saying to SAS “Here’s a string, but I want you to start treating it as a date. So that you know which part is the year, and which part is the month etc, use this informat as a guide”. Then SAS can know that dd = 07, mm = 10, and yy = 2013.<\/p>\n
      We’ve effectively translated a string in custom format into a SAS date.<\/p>\n
      My mind is blown. Now what?<\/h1>\n
      Interpreting the dates as dates means we can now make it human-friendly in our reports, and also allows us to do some excellent SAS-native date filtering.<\/p>\n
      Formatting<\/h1>\n
      What we specified earlier was an INFORMAT – in other words an interpretation format. What we can do, now that the date is stored as a SAS date, is specify an OUTPUT format, so that we can represent the date in a variety of ways in our reports.<\/p>\n
        \n
      1. Go back to the Properties dialog for your date field<\/li>\n
      2. On the Classifications tab is a “Formats” section. Change the “Format Type” to “Date\/Time” and look at the available formats.<\/li>\n<\/ol>\n
        Selecting a format will take your date and represent it as a different string depending on the format you choose. Some examples:<\/p>\n\n\n\n\n\n\n\n\n\n
        Format<\/strong><\/td>\nOutput<\/strong><\/td>\n<\/tr>\n
        DATE<\/td>\n07OCT13<\/td>\n<\/tr>\n
        DAY<\/td>\n7<\/td>\n<\/tr>\n
        WEEKDAY<\/td>\n1<\/td>\n<\/tr>\n
        MONNAME<\/td>\nOctober<\/td>\n<\/tr>\n
        DDMMYY<\/td>\n07\/10\/13<\/td>\n<\/tr>\n
        DOWNAME<\/td>\nMonday<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
        Filtering<\/h1>\n
        We can also now use SAS to filter dates in a very cool way. For example I can now filter all records which were created in 2013, or all records created after a certain date, or on a certain date, etc.<\/p>\n
          \n
        1. Create\u00a0a new Filter and choose your date field as the Data Item.<\/li>\n
        2. Then set your Condition to “Year to date” – this will filter all your results to only show ones where the date falls between 1 Jan 2013 and today.<\/li>\n
        3. Click OK<\/li>\n<\/ol>\n
          A note on filtering<\/h1>\n
          It’s always preferable to apply a filter at the Information Map level, rather than typing in a manual filter when you’re creating your Web Report. A filter on the Information Map will mean the data is filtered at the source, rather than decoding a bunch of information and only filtering once we get to the report level.*<\/p>\n
          * My understanding is that this is only with certain databases. Some allow optimisation by passing through filtering into the queries they make against the source databases. Still a good practice if you can do it.<\/p>\n","protected":false},"excerpt":{"rendered":"
          Background Let\u2019s assume you have some dates in a custom format: \u00a0 date20131007 month102013 SAS Reports need to be able to a) present dates in a human readable format and b) understand dates to allow filtering and other funky stuff. For that reason we need a way of translating these custom dates into SAS dates.… Read More »Converting custom date formats in SAS Information Map Studio<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":""},"categories":[23,24,15],"tags":[83,103,104,152,153],"_links":{"self":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/posts\/207"}],"collection":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/comments?post=207"}],"version-history":[{"count":0,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/posts\/207\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/media?parent=207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/categories?post=207"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.sebcharrot.com\/blog\/wp-json\/wp\/v2\/tags?post=207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}