Bobbie Wagoner

9月 082017
 

In SAS Visual Analytics 8.1, report creators have the ability to include drive-distance and drive-time in their geographical maps, but only if their site has an Esri ArcGIS Online account and they have valid credentials for the account.

In the user Settings for SAS Visual Analytics Geographic Mapping 8.1 release, there are three choices for selection of a geographic map provider.  The map provider creates the background map for geo maps and for network diagrams that display a map.

The map provider options are:

  • OpenStreet Map service, hosted at SAS.
  • Esri ArGIS Online Services, which only requirFinale acceptance of the terms and conditions.
  • Esri premium services, which requires a credential validation.

If Esri premium services is selected, there is an additional prompt for valid credentials, and you must still accept the Esri ArcGIS Online Services terms in order to select the premium services checkbox.

It’s also worth pointing out here, that even if you have Esri premium credentials, in order for these credentials to be validated in SAS Visual Analytics, you must also be a member of the ESRI Users custom group.  Users can be added to this group in SAS Environment Manager, as shown below.

Note that without the Esri ‘premium’ service and validated credentials, when you right-click and Create geographic selection in your report map, you are only able to select the Distance selection, which displays the radial distance for the selection point.

With premium services in effect, you can also select drive-time or drive-distance.  An example of a drive-time selection is shown here.  Drive-time creates an irregular selection based on the distance that can be driven in the specified amount of time.

A drive-distance example is shown below.  Drive-distance creates an irregular selection based on the driving distance using roads.

When selecting drive-time or drive distance, you can also add breaks to show, as in the example below, the 5-mile distance, the 10-mile distance, and the 15-mile distance on the maps.

It’s also worth pointing out, that if a viewer of the report has not had Esri premium credentials validated, the viewer will be unable to view the drive-distances and drive-time features.  The settings for users of the report viewer are also stored in the Report Viewer Geographic Mapping user settings.

If a user is adding a connection to the server in SAS Mobile BI 8.15 and their account is a member of the Esri Users group, they will be prompted for their Esri premium credentials when adding the server connection:

I hope you’ve found this post helpful.

How do I access the Premium Esri Map Service for my SAS Visual Analytics reports? was published on SAS Users.

6月 292017
 

In my last blog, we examined the data pane in SAS Visual Analytics 8.1. That blog discussed how to have the data pane display the data items of your active data source, and how to perform tasks such as viewing measure details, changing data item properties, and creating geographic data items, hierarchies, and custom categories.  In this blog, we’ll look at creating new calculated data items and calculated aggregations.

If you recall, l you display the Data pane in the Visual Analytics interface by clicking the Data icon on the left menu.

A calculated data item is a new data item created from existing data by using an expression.

  • Calculations are performed on un-aggregated data—the expression is evaluated on each row before aggregations are calculated.
  • Calculated data items can accept parameters.
  • A hierarchy can contain calculated category data items.
  • Calculated data items can be changed to geography data items and used in geo maps.

You can create a derived calculation from a category or measure data item by right-clicking on the data item and selecting Create calculation from data item.

For a category data item, you can create a distinct count, count, or number missing. Creating a derived calculation from a category data item:

For a measure data item, you can create a percent of total, or a periodic calculation based on one of your date data items. Creating a derived calculation from a measure data item:

Notice that in both cases, the new data item is an aggregation, so the new item will appear under the Aggregated Measure category in the data pane.

Note:  In order to use the periodic calculation types, your selected data item must include the year.

You can also edit these new data items by right-clicking on the data item and selecting Edit. Editing a derived calculation:

There is now a single interface for creating calculated data items of type Numeric, Character, Date or Datetime or Aggregated measures.

  • This interface provides both Visual mode and Text mode for viewing and editing the expression.
  • You can drag and drop data items or parameters and operators onto the expression in either mode.
  • In text mode, you can also type in your expression.

Creating a calculated data item or aggregated measure:

Specifying the calculation result type and format:

Some notes for using operators in calculations and aggregations:

  • Operators are provided for both calculations and aggregations.
  • You can expand and collapse each category of operators.
  • If you add an Aggregated operator to an expression, the result type will be changed to Aggregated Measure.
  • You cannot have nested aggregations in an expression.You also have access to periodic operators and simple and advanced aggregated operators for calculation aggregations.

In the same interface, you have access to simple and advanced numeric operators, simple and advanced text operators, along with boolean, date and time, and comparison operators for your calculations.

You also have access to periodic operators and simple and advanced aggregated operators for calculation aggregations.

The most important point to remember in using this interface is to think ahead as to whether you are creating a calculation (operating on each row) or an aggregation (operating across rows) and specify the data type and format before you begin to drag and drop data items and operators.  The default data type is Numeric, but if you add an aggregation operator, the type will automatically switch to Aggregated Measure.

Remember that you also create calculated items of character, date, and datetime data types–and you can choose from a list of date and datetime formats for those data types.

The SAS Visual Analytics 8.1 Data Pane: Creating Calculations and Aggregations was published on SAS Users.

6月 052017
 

Refining your data for effective reports is even easier in the 8.1 release of SAS Visual Analytics. In this blog post, I’ll take a look at the data pane, how it displays data from your active data source, and a few tasks that you might want to perform, such as viewing measure details, changing data item properties, and creating geographic data items, hierarchies, and custom categories.  In a future blog, I’ll look at creating filters, new calculated data items, and calculated aggregations.

In the SAS Visual Analytics interface, you can display the Data pane by clicking the Data icon on the left menu.

Measure details

You can view data item properties and make these changes:

  • The name of a data item.
  • The Classification of Measure or Category data item.
  • The format of a Measure or Date/Time/Datetime data item.
  • The aggregation of a Measure data item.
  • Note:  User-defined formats for Category data items are honored, but cannot be changed.  The user-defined format must be available to the CAS server where the data is loaded.

A Measure classification can be changed to Category or Geography. A Category classification can be changed to Geography.  Date and Tim classifications cannot be changed.

You can modify the aggregation for a Measure data item:

You can modify measure formats:

  • You can expand to display available Duration and Currency formats.
  • Over thirty different Currency formats are available for local or international currency.  (Example: $56,790 or USD56,789)

Geography data items identity a geographic region or location, and are typically used to visualize data on a geographic map.

  • You can create geography data items by using predefined geography classifications such as countries or states.
  • You can create a custom geographic classification by providing latitude and longitude coordinates in your data source.
  • For predefined classifications, the values of your category data items must match the lookup values documented here.

Designating a Geography data item:

Designating a custom Geography data item:

A hierarchy enables you to add drill-down functionality to a report.

  • A hierarchy can display information from general to specific.
  • You can create category, date/datetime, or geography hierarchies.
  • Not all report objects support all types of hierarchies.

  • A custom category is a category data item which associates a set of alphanumeric values with intervals, ranges, or specific values of a data item.
  • A custom category can be based on a category or a measure data item.
  • A custom category can provide functionality in a report similar to that of a user-defined format—without having to have a format previously associated with the data item.

Example:
1-20 = ‘First group’
21-30 = ‘Second group’
31-40 = ‘Third group’

Depending on the report object, some roles require only one value; other roles may require multiple values.

You can right-click on a data item in the Roles pane to remove the data item from a role.

Duplicating a data item can enable you to display data in two different ways.

Duplicating the Date data item with 939 different values can enable you to create a report that analyzes production on each day of the week: Monday, Tuesday, Wednesday,…

Duplicating a measure data item can enable you to use the column as a category data item in reports as well.

Note: You can only delete data items that you have created, such duplicated data items or calculated data items.  Deleting a data item means that it no longer appears in the data pane.

This covers many of the basic tasks that you can complete in the new data pane. In my next blog, I’ll take a look at the visual and text editors for creating filter expressions and calculated items and aggregations in the data pane.

The new Data Pane in SAS Visual Analytics - It's painless! was published on SAS Users.

4月 102017
 

You can expand on the functionality of SAS Visual Data Builder in SAS Visual Analytics by editing the query code, adding code for pre- and post-processing, or even writing your own query.   You can process single tables or join multiple tables, writing the output to a LASR  library, a SAS library, or a DBMS library.  But you can also easily schedule your queries, right from the Visual Data Builder interface.

Here’s how.

When a query is open in the workspace of Visual Data Builder, you can schedule the query from the application by clicking the Schedule (clock) icon.

The scheduling server used is determined by the SAS Visual Data Builder Scheduling preferences setting, shown below.

By default, the Visual Analytics deployment includes the Operating System Services scheduling server, so it appears automatically as the default.

The Server Manager plug-in to SAS Management Console identifies the scheduling servers that are included in your deployment. You can specify a different scheduling server, such as Platform Suite for SAS server, if your deployment includes it.

Note:  The Distributed In-process scheduling server is not supported.

Any scheduling preferences that you change are used the next time you create and schedule a query. If you need to change the settings for a query that is already scheduled, you can use SAS Management Console Schedule Manager to redeploy the deployed job for the query.

When you schedule a query, the SAS statements are saved in a file in the default deployment directory path: SAS-config-dir/Lev1/SASApp/SASEnvironment/SASCode/Jobs.

In the examples in this blog, the SAS-config-dir is /opt/sasinside/vaconfig.
The metadata name of the directory is Batch Jobs.
The default SAS Application Server name associated with the directory is SASApp.

If you are working in a VA environment where multiple application servers are defined, you should be aware of the following SAS Notes at the links below, relating to the application’s choice of application servers for scheduling.

SAS Note 58186SAS® Visual Data Builder might use the wrong application server for scheduling
SAS Note 52977SAS® Visual Data Builder requires the default SAS® Application Server and the default scheduling servers to be located on the same physical machine

To schedule a query, open the query and select the Schedule (clock) icon. (The clock is grayed out if you have not saved the query.)

You can schedule the query to run immediately (Run now) or at a specified time event.  To define a time event, select the Select one or more triggers for this query button and click New Time Event.  Grouping events are not supported for the default server, but may be supported for other scheduling servers, such as Platform Suite.

You can schedule for One time only, or More than once, running Hourly, Daily, Weekly, Monthly, or Yearly.  The appearance of the interface and scheduling parameters change with your specification.

In this example, a One time only event is specified.

 

The time event specification gets recorded in the Trigger list on the Schedule page, and is selected in the Used column.

After you click OK in the Schedule window, you will get the confirmation below.

After the time event has passed, you can verify that the table has been loaded on the LASR Tables tab of the Visual Analytics Administrator.

When you schedule, the Visual Data Builder:

  • creates a job that executes the query.
  • creates a deployed job from the job.
  • places the job into a new deployed flow.
  • schedules the flow on a scheduling server.

The files are named according to vdb_query_id_timestamp.
In this example the files are named vdb_CustomerInfoData_1490112883364_timestamp.
When the query executes at the scheduled time, the SAS code that is written to the /opt/sasinside/vaconfig/Lev1/SASApp/SASEnvironment/SASCode/Jobs directory.  The query is run with the user ID that scheduled it.

If you right-click on Server Manager in SAS Management Console and view Deployment Directories, you will see that this is the Deployment directory (Batch Jobs) for SASApp.

In the /opt/sasinside/vaconfig/Lev1/SASApp/BatchServer/Logs directory, you can view the SAS Log.

The scheduling server script and log are in /opt/sasinside/vaconfig/Lev1/SchedulingServer/Ahmed/vdb_CustomerInfoData_14900112883364

Observe that the script was written to this location at the time the job was scheduled, rather than at execution time.

If you edit a data query that is already scheduled, you must click the schedule icon again so that the SAS statements for the data query are regenerated and saved.

If you edit the query again and specify additional time events, each event appears in the trigger list, and you can check which time event is to be used for scheduling.

If scheduling a query according to time events, you should also be aware of this Usage note:

Usage Note 55880: Scheduled SAS® Visual Data Builder queries are executed based on the time zone of the scheduling server 

And to add to the fun, also keep in mind that if your deployment includes SAS Data Integration Studio, you can also export a query as a Job and then perform the deployment steps using DI Studio.

Just right-click on the query in the SAS folder panel in Visual Data Builder and Select Export as a Job!

Easy Scheduling in Visual Data Builder - SAS Visual Analytics 7.3 was published on SAS Users.

3月 092017
 

Several months ago, I posted a blog about calculating moving averages for a measure in the Visual Analytics Designer. Soon after that, I was asked about calculating not only the average, but also the standard deviation over a period of months, when the data might consist of one or more repeated values of a measure for each month of a series of N months.  For the example of N=20 months, we might want to view the average and standard deviation over the last n months, where n is any number between 3 and 20.

The example report shown below allows the user to type in a number, n, between 3 and 20, to display a report consisting of the amount values for past n months, the amount values for Current Month Amt-Previous, the Avg over the last n months, the Standard Deviation over the last n months, and the absolute value of the (Current Month Amt – Previous Month Amt), divided by the Standard Deviation over the last n months. A Display rule is applied to the final Abs column, showing Green for a value less than 1 and red for a value greater than or equal to 1.

The data used in this example had multiple Amount values for each month, so we first used the Visual Data Builder to create a SUM aggregation for Amount for each unique Date value.  This step gives more flexibility in using the amount value for aggregations in the designer.

When the modified data source is initially added to the report, it contains only the Category data item Month, with a format of MMYYYY, and the measure Amount Sum for Month.

The data will be displayed in a list table. The first columns added to the table will be Month, displayed with a MMYYYY format, and Amount Sum for Month.

Specify the properties for the list table as below:

Since we want to display the last n months, we create a new calculated data item, Numeric Date, calculated as below, using the TREATAS operator on the Month data item:

Then we create the Current Month Amt-Previous aggregated measure using the RelativePeriod date operator:

Next, create the Avg over all displayed months aggregated measure as below:

Then, create the Std.Dev. over all displayed months aggregated measure as shown below:

Create the Abs (Current-Previous/StdDev) as shown below:

Create a numeric parameter, Number of Months, as shown, with minimum value of 3 (smallest value that a standard deviation will make sense) and maximum value of 20 (the number of months in our data). You can let the default (Current value) value be any value that you choose:

For the List Table, create a Rank, as shown below. Note that we are creating the rank on the Numeric Date (not the Month data item), and rather than a specific value for count, we are going to use the value of the parameter, Number of Months.

Create a text input object that enables the user to type in a ‘number of months’ between 3 and 20.

Associate the Parameter with the Text input object:

If you wish, you can add display rules to sound an alarm whenever there is an alarming month-to-month difference in comparison to the standard deviation for the months.

So the final result of all of the above is this report, which points out month-to-month differences, which might deserve further concern or investigation. Note that the Numeric Date value is included below just to enable you to see what those values look like—you likely would not want to include that calculated data item in your report.

Calculating standard deviation of a measure in Visual Analytics Designer was published on SAS Users.

9月 072016
 

Calculate a moving average using periodic operators in SAS Visual AnalyticsThumbAnalysts often use a simple moving average to get an idea of the trends in data. This is simply an average of a subset of time periods, and the size of the subset can differ depending on the application. The technique can be used with data based on time periods, such as sales data, expense data, telecom data, or stock market data. The average is called ‘moving’ because it is continually recomputed as more data becomes available. This type of average is also called a ‘rolling average’ or ‘running average’. In this post, I’ll share a little bit about how to use the periodic operators in SAS Visual Analytics Designer to calculate a simple moving average.

The report below, created in the designer, shows the summary of the Amount column by month. The Three-Month Moving Average column displays the average of a month and the previous two months’ Amount sums. The 3-month sum is simply divided by 3.

Calculate a moving average using periodic operators in SAS Visual Analytics

This Three-Month Moving Average column is an aggregation, calculated using the RelativePeriod Periodic operator.  Both the visual and the text forms of the aggregation are shown below:

Calculate a moving average using periodic operators in SAS Visual Analytics2

Calculate a moving average using periodic operators in SAS Visual Analytics3

The RelativePeriod operator returns aggregated values - sum of Amount, in this case - relative to the current period – in this case, the previous month. The data item for the period calculation is Month, which is a date value with an associated YYYYMM format. The interval is _ByMonth_, and the 0, -1, and -2 offset values represent the current month, the previous month, and the month before the previous, respectively. The division by three creates the 3-month moving average, but the number of RelativePeriod expressions and the divisor could be adjusted to calculate an average based on any number of months.

The report below displays a 3-Year Moving Average column.

Calculate a moving average using periodic operators in SAS Visual Analytics4

In this report, the RelativePeriod operator is used to calculate the three-year moving average. The data item for the period calculation is Year, which is a date value with an associated Year format.

Calculate a moving average using periodic operators in SAS Visual Analytics5

The interval is _ByYear_, with the 0, -1, and -2 offset values representing the current year, the previous year, and the year before the previous, respectively. Again, the number of RelativePeriod expressions and the divisor could be adjusted to calculate an average based on any number of years.

Calculate a moving average using periodic operators in SAS Visual Analytics6

Calculate a moving average using periodic operators in SAS Visual Analytics7

The ParallelPeriod operator is used in the report below to display a 3-month moving average based on amounts corresponding to the same month in each of the three years. This report, of course, has missing values for all months up until the third year of data.

Calculate a moving average using periodic operators in SAS Visual Analytics8

Calculate a moving average using periodic operators in SAS Visual Analytics9

The first moving average value to be calculated is based on data from January 2010, 2011, and 2012. The measure is Amount and the periodic item for the aggregation Month. The inner interval for the aggregation is _ByMonth_ and the outer interval is _ByYear_. The 0, -1, and -2 offset values represent months of the current year, the previous year, and the year before the previous, respectively. These ParallelPeriod expressions and the divisor could also be adjusted to calculate an average based a different divisor.

Calculate a moving average using periodic operators in SAS Visual Analytics10

Calculate a moving average using periodic operators in SAS Visual Analytics11

Do keep in mind that for all of these periodic aggregations the ‘aggregation by’ column representing month or year must be included in the report.

I hope these examples using the periodic operators will be helpful to you in creating your Visual Analytics reports.

tags: business intelligence, SAS Professional Services, SAS Visual Analytics

Calculate a moving average using periodic operators in SAS Visual Analytics Designer was published on SAS Users.

8月 232016
 

After posting a couple of blogs on the subject of dates and date formats in Visual Analytics Designer, I got a question from a user who wondered how to compare data for a selected date to data from the same day of the previous year. Here’s one way to do this.

The example report enables a user to type in a date value in a variety of formats and displays the sale amount for the specified date, along with the sale amount for the same day of the previous year.

Working with dates in SAS Visual Analytics

The data source includes information on thousands of orders. Irrelevant data items have been hidden, with the items of interest shown below. Transaction Date has an associated MMDDYYYY format and Transaction Weekday is simply a duplicate of the date with an associated Day of Week format.

Working with dates in SAS Visual Analytics02

A parameter, Param date, is associated with the Parameter role of the Text input field and will store the value typed in the field.

Working with dates in SAS Visual Analytics03

Working with dates in SAS Visual Analytics04

Several calculated data items are created for ‘behind the scenes’ filtering and Ref Date is a data item that will store the date converted from the entered text version of the date.

Working with dates in SAS Visual Analytics05

Note that the ANYDTDTE informat is a great one to use when you are uncertain as to exactly how users will be typing in a date value.
Ref Date (Yr-1) is the ‘same day a year ago’ date.

Working with dates in SAS Visual Analytics06

A filter on the list table completes the report:
( Ref Date = Transaction Date ) OR ( Ref Date (Yr-1) = Transaction Date )
The data items below are now used to populate the report showing the data for the specified date for comparison with the data for the same day of the previous year.

Working with dates in SAS Visual Analytics07

Working with dates in SAS Visual Analytics08

With the addition of a few more calculated data items and filters, some additional report objects can offer alternate ways of displaying the data.
Prod Sale (ref date) and Prod Sale (Yr-1) are calculated as below:

Working with dates in SAS Visual Analytics09

The addition of the two new data items allow the information to be presented in the list table below, with this filter applied:  (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing)

Working with dates in SAS Visual Analytics10

The same filter (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing) can be applied to a crosstab object to produce the result below:

Working with dates in SAS Visual Analytics11

The addition of one additional calculated data item, Date, and a new crosstab object with a filter on Date, enables still a different display.

Working with dates in SAS Visual Analytics12

I found this to be an interesting example–both the problem and the solutions. I hope this blog will give you more ideas about using your dates to the best advantage in report.

tags: SAS Professional Services, SAS Visual Analytics

Working with dates in SAS Visual Analytics: Comparing to the previous year was published on SAS Users.

6月 222016
 

Report designers often discover after aggregating data by groups in the Visual Analytics Designer that it would also be nice to see additional aggregations of the data, for example, a maximum or minimum of that sum across groups. This means creating an ‘aggregation of an aggregation.’ If you plan your report objectives in advance of loading your data, you can do this by creating an aggregation initially in the Visual Data Builder, rather than the Designer.  And in many cases, it’s possible to get the desired report results by simply loading a small additional table to memory.

Here’s an example of a report with a sum aggregation on the profit measure and Facility Region as the category data item. The report also shows the maximum, minimum, and average of the regional profit values, along with the difference between each region’s profit sum and the average. It’s possible to assign a SUM aggregation to the Profit measure, but the challenge appears when trying then to create a MAX aggregation across the regions.

Visual Data Builder to pre-aggregate your data01

If you have awareness of the need for this type of report prior to loading the data, you can easily load a much smaller version of the data with the Profit data item already pre-aggregated. The data builder query, shown below, creates and loads a file containing only the FacilityRegion as a GROUP BY column and the Profit column as a SUM aggregation.

Visual Data Builder to pre-aggregate your data02

Visual Data Builder to pre-aggregate your data03

In the designer, for reporting purposes, the Profit_SUM data item is renamed to Sum of Profit for Region.

Visual Data Builder to pre-aggregate your data04

Now the Sum of Profit for Region data item can be used to calculate these aggregated measures:  Max Regional Profit Sum, Min Regional Profit Sum, and Avg Regional Profit Sum, using the formulas shown below:

Visual Data Builder to pre-aggregate your data05

Once you have the Avg Regional Profit Sum calculation, you can create the additional aggregated measure, Regional Profit – Average:

Visual Data Builder to pre-aggregate your data06

So, this is a list table displaying all of the information that was derived from the two columns and four rows of data.

Visual Data Builder to pre-aggregate your data07

To emphasize the region with the maximum profit and minimum profit, you may want to add the following display rules.

Visual Data Builder to pre-aggregate your data08

And since you have the original larger table loaded as well, you can add an additional data source and some interactions, so that the user can click on a row to view more detail data corresponding to each region of interest.

Visual Data Builder to pre-aggregate your data09

Now, for example, the user can see what facilities contributed to the max value for the Western region, and the corresponding Expenses as well!

Visual Data Builder to pre-aggregate your data10

So, although it may not be practical to pre-aggregate the values of every measure in your data, sometimes pre-aggregating and storing the much smaller table can give you the benefit of being able to get the additional aggregations that you need for a specialized report, while relying on a larger stored table for the details.

tags: SAS Professional Services, SAS Programmers, SAS Visual Analytics

Need an aggregation of an aggregation? Use Visual Data Builder to pre-aggregate your data was published on SAS Users.

4月 302016
 

In a previous blogVisual Analytics audit data collection, I discussed SAS date and time values, and how date and time formats can be used to your advantage in SAS Visual Analytics. That blog addressed some of the features provided for handling date information, like date formats, date hierarchies, and calculated data items based on time intervals.  In this blog, I’ll continue this discussion and share some tips and techniques for working with dates.

Problem: How do I calculate passage of time?

Example: Out of concern over the time taken for delivery of a certain product, we’d like to take a look at the time, from order to delivery of the product, for each order across several years.  The table in memory has data items Product ID, Order ID, Date Ordered, Date Delivered.

Date in Visual Analytics Designer01

Create a new calculated data item, Days from Order to Delivery, calculated as shown:

Date in Visual Analytics Designer02

(1 is added because  we count the order date as a day as well)

Date in Visual Analytics Designer03

Problem: Why does my year have a decimal?

Example: It’s often nice to have a year data item, in addition to the entire date.    The Year operator extracts the year from the date value.|

Date in Visual Analytics Designer04

But then the year displays as shown below, with the comma and decimal! That’s because the new calculated value is a measure and has a default format of COMMA with a width of 12 and 2 decimal places.

Date in Visual Analytics Designer05

To fix this, simply change the format for the year of date data item to Numeric with a width of 12(or less) and no decimals.  Now the year of date values display as below.

Date in Visual Analytics Designer06

And remember, as long as you have a date data item with an associated format that displays year, you don’t need an additional year data item in order to create a hierarchy. Visual Analytics Designer can create the appropriate hierarchy automatically that includes the year.

And if you want to be able to display just the year value, you can duplicate your date data item, and change the format to Year.

Date in Visual Analytics Designer07

Problem: Ugh!  Someone stored these dates as character values!

If dates are stored as character values, that means that when loaded to memory, the date column becomes a category data item with a $ format. With this treatment, you’re unable to take advantage of any of the nice data formats or the date hierarchy features. Plus, when the values are sorted as below, in ascending or descending order, they’re sorted according to character sorting rules, rather than dates.

Date in Visual Analytics Designer08

It’s easy to create a calculated value (we’ll call it RealDate) using the Parse operator, which interprets a string according to a specified informat.

Date in Visual Analytics Designer09

When you click on the format field, you get this selection, and the DATE format with a column width of 9 fits the need here.

Date in Visual Analytics Designer10

Now RealDate displays and sorts as a date should, and since it is now a calculated date value, you can use the different date formats, create date hierarchies, or use the data item with periodic operators.

Date in Visual Analytics Designer11

Problem: The opposite problem!  My date is stored as a date value, so internally, it’s a number, but I want to display the date value as part of a text string!

Text expanded date info below, is a calculated data item, whose expression uses the Concatenation and Format operators.

Date in Visual Analytics Designer12

Both the visual and text forms are shown below:

Date in Visual Analytics Designer13

Problem: How do I create a new calculated date or datetime data item whose values are a certain number of days from the value of another date or datetime data item.

Remember that a date value is stored internally as the number of days since Jan 1, 1960, and a datetime value is the number of seconds since Jan 1, 1960. So this is just a problem of showing passage of time in either days or seconds.

To create a calculated date data item that is X number of days later than the value of date1, create a formula as below, this example calculating a date 21 days later.

Date in Visual Analytics Designer14

To create a calculated datetime date item that is X number of days later than the value of date1, create a formula as below, this example again calculating a date 21 days later. Note that we are multiplying 21 times the number of seconds in a day (60*60*24).

Date in Visual Analytics Designer15

Problem: My date is stored as a number, but the numbers look like this!  YYYYMM (201603, for example)

There are operators available for creating a calculated value that will enable you to overcome most problems of incoming dates being stored in strange ways.

Here is a nice example that was posted in the Global Visual Analytics list for solving this particular problem by using the Mod and Floor operators. Since there was no day information stored with the original numeric date, the day was arbitrarily chosen to be Day 1 of the month:

Date in Visual Analytics Designer16

That's probably enough about ‘dealing with dates’ for one blog. Hopefully, this information has provided some ideas for solving some of the date problems/issues that you might encounter in your work.

tags: SAS Professional Services, SAS Programmers, SAS Visual Analytics

Dealing with Dates in SAS Visual Analytics Designer was published on SAS Users.

3月 232016
 

VisualAnalyticsHTML5You may have noticed that when using date information in SAS Visual Analytics, that the date data values can be displayed in a variety of ways. You may see your dates displayed like Jan1916, 03Jun1915, or 03/12/16, for example. In this blog I’ll help you understand SAS date and time values, and review some of the features of SAS Visual Analytics relating to date and time values.

To help illustrate some of the date features in Visual Analytics Designer, I’ve created a SAS table containing multiple date columns, each containing the same data values, but having different associated formats, along with a datetime column and a time column with associated formats. For analysis purposes, there is also an amount column.

The following figure shows how the data is stored and how the data values would be represented in a simple printed report, for example.

Date Formats in Visual Analytics Designer

How date and time formats are used in designer

When the table is loaded to memory and added to a designer report as a data source, the columns with date, datetime, and time formats are interpreted as date, datetime and time category data items, as shown in the next figure. Notice that the date column with no format, however, is interpreted as a numeric measure data item.  The formats in designer are as shown below.
Date Formats in Visual Analytics Designer2

This is a list table showing how the values display with the associated formats.
Date Formats in Visual Analytics Designer3

So what are those numbers in the date column all about?

The numbers in the date column represent a SAS data value with no format associated.  This means it is simply a numeric value representing ‘number of days’.  Date values are stored internally as numbers in order to enable calculations on dates, such as the number of days between two dates.
A SAS date value is a numeric value representing the number of days between January 1, 1960 and a specified date.

A SAS datetime value represents the number of seconds between January 1, 1960 and a specified date and time.

A SAS time value represents the number of seconds since midnight of the current day. These values are between 0 and 86400.

For SAS tables, however, date values can be displayed using a variety of different formats (almost 100!)  There are numerous formats available for displaying datetime and time values also.  You are able to take advantage of many of these formats in SAS Visual Analytics—because, after all, who really wants to sit around counting the number of days in order to interpret date values in a report?

Changing the date and time formats in designer

When changing the format for any of the date data items, you can choose from this list:
Date Formats in Visual Analytics Designer4

Changing the format for the datetime data item displays this list:
Date Formats in Visual Analytics Designer5

A data item with time information stored can use these formats:

Date Formats in Visual Analytics Designer6

Another problem to consider:  Suppose your date information was loaded to memory without an associated date format at all, in the case of the date column in the data above.  This is column is interpreted in designer as a numeric data value.  You can solve this problem easily by creating a new calculated data item using the TreatAs Numeric(advanced) operator.  The operator enables you to specify a calculation that treats a value as a different data type.  In this case, the expression below specifies that the date data item should be treated as a date value.  The values of the new date(converted) data item are shown below.  You can also then change the format of the new date(converted) data item if you wish.
Date Formats in Visual Analytics Designer7

Date hierarchies in designer

In designer you can create a default date hierarchy from any date or datetime data item whose format displays a year by simply right-clicking on the data item and selecting the Create Date Hierarchy option.  A date hierarchy consists of year, quarter, month, day.  To create a time hierarchy right-click on a time data item and select Create Time Hierarchy. The time data item results in a hierarchy of hour, minute, second.

These hierarchies are the default hierarchies created for the date1, date3, and time, and datetime data items from the example data above.
Date Formats in Visual Analytics Designer8

Note:  You may have noticed that, in explorer, for a datetime item, you have the option of creating a Date and Time Hierarchy for that data item, consisting of Year, Quarter, Month, Day, Hour, Minute, Second.  The Date and Time Hierarchy is not available in the designer. So the default hierarchy for the datetime data item above consists of only Year, Quarter, Month, Day—the same as for the date1 and date3 hierarchies.

If the format associated with a date only displays Month, (the MONTH. Format associated with date2, for example), then in designer, you won’t be able to create a hierarchy from the data item until you change the format to one that displays year also.   If a hierarchy is not supported for a data item, then the Create Hierarchy option is not displayed when you right-click on the data item.

Derived data items for measure based on data or time intervals

Having data items whose format displays a year value also enables you to create derived items for reports that are based on time intervals. When you click on one of the intervals below, a selection list of data items display.  The only data items that are supported for the intervals are those that contain a year—so that’s data1, data3, and datetime in this data.
Date Formats in Visual Analytics Designer9

The formulas for these derived items use periodic operators that aggregate values over time: RelativePeriod, ParallelPeriod, and CumulativePeriod.

The special derived data items feature creates the formulas for these aggregations for you, but the same Periodic operators are also available for creating your own New Aggregated Measures:
Date Formats in Visual Analytics Designer10

Some of these derived columns are illustrated here using data that contains a Date by Month data item and an Expenses data item.  The data spans 2010-2011.
Date Formats in Visual Analytics Designer11

Date Formats in Visual Analytics Designer12

You can edit each of the derived data items to view the calculation and to make changes, if necessary.

The calculation for Expenses(Difference from Previous Parallel Period) is shown below, as an example.

Date Formats in Visual Analytics Designer13

_Sum_ is the aggregation that is applied to the measure, Expenses.

The data item for the periodic calculation is Date by Month.

The inner interval (smaller time period) for the aggregation is _Inferred_ , which means it has been determined from the report object. You could change to specific values of _ByMonth_ , _ByQuarter_ , or _ByYear_ .

The outer interval (larger time period) is also _Inferred_ , with other possible values being _ByMonth_ , _ByQuarter_ , or _ByYear_ .

The number of outer intervals to offset from the current period is 0. This means that the period from the current outer interval (Year) is used.

The scope for the period is _Full_ , meaning that the values are aggregated for the entire period. Alternate values are _ToDate_ (up to a specific day in the current period), or _ToToday_ (aggregates only up to the equivalent of today’s position in the current interval).  If you change the scope to _ToDate_ , you would need to specify a specific date in the last field.

So considering the above descriptions, for the list table, the expression below would produce the same resulting values for Expenses(Difference from Previous Parallel Period).
Date Formats in Visual Analytics Designer14

Periodic operators can return missing values under certain scenarios.  These scenarios, along with complete details on all of the date operators are documented Appendix 5 of the SAS Visual Analyics 7.3: User’s Guide.

Hopefully, this blog provides you with a better understanding of the basics of dates and times, and enable you to use some of the great features for dates in SAS Visual Analytics.

 

 

 

 

 

 

 

tags: SAS Professional Services, SAS Programmers, SAS Visual Analytics

Making Best Use of Date Formats in Visual Analytics Designer was published on SAS Users.