Business Intelligence

8月 222018
 

As a fun side project I recently looked into alternative visualization techniques in order to use computers to create art. An interesting approach is pointillism, which, according to Wikipedia is a "technique of painting in which small, distinct dots of color are applied in patterns to form an image." This [...]

Pointillistic art with SAS® Visual Analytics was published on SAS Voices by Falko Schulz

6月 302018
 

Introduced with SAS Visual Analytics 8.2 is a new object named: Key Value. The intent of this object is call attention to an aggregated value for a measure, a category, or both. For additional specifics,

I’ve mocked up several reports to show some of the combinations available to give you an idea of what the Key Value object can look like. Toward the end of the blog, I will add additional reports to provide design ideas for placement and action assignments. Click on any image to enlarge.

Text Style with Measure Value Highlight

Here you can see in the report, I am highlighting two measure values. Both are representing the Highest value but I selected one to show the aggregation and the other not. I was able to mimic similar headings by renaming my data item. Be sure to look at the Options and Roles pane for the assignments to understand how I accomplished this.

Infographic Style with Measure Value Highlight

Here is the same information represented using the Infographic style. Seeing the same report using the different styles allows you to quickly determine the most powerful and appropriate visualization to meet your needs. We cannot control the size of the circle, only the color. In this case, the circles are different thicknesses because of the number of characters used to represent the measure values inside the visual.

Text Style displaying both Measure Value and Category

In this report, I have shown how to use the Text style to display both a Category value and a Measure value. As you can see, only one can be Highlighted, i.e. given the largest font. Notice that in this report I used the object’s Title to help explain the key value being displayed, this is a recommended best practice.

Infographic Style displaying both Measure Value and Category

Below I am using the Infographic style and notice in the Options pane below that I had to use the Additional information attribute to better label the data. Make sure that when you are in the design phase and toggling between text and infographic to review and test the available Key Value Style attributes to better label the visual.

Text Style with Category Value Highlight

In this report, I show how to highlight the Category value using the Text style. Since I chose to not use any of the available label attributes it is critical that I use the object’s Title to better explain the key value displayed.

Infographic Style with Category Value Highlight

In this report, you can see how I changed the layout from the previous report to make the Key Value object side-by-side the other report objects. If you are interested in using the Infographic style with the circle enabled then you may have to adjust your report design to accommodate for the space the circle needs to display. Remember not to shy away from adding white space to your report, it can assist when adding emphasis to a particular visual, in this case a key value.

Summary

Some important things to remember about using the Key Value object in your reports:

  • Use the Key Value object’s Title to inform your users what the number or category value represents so there is no ambiguity as to if they are looking at the maximum or minimum value.
  • When determining which style you prefer, Text or Infographic, it may be easier to make a duplicate of the Page and then adjust the style attributes till you find the desired combination.
  • Take time to adjust the arrangement of objects on your report to get the most pleasing configuration. Don’t shy away from leaving white space in your report. You can also experiment with the Container object using the Precision container type to layer the Key Value object.
  • The Key Value object will be affected by Report and Page Prompts like any other report object and you can even define Actions to filter the Key Value object.

Here are some additional examples of using the Key Value object:

In this example, you can see from the Actions Diagram how the Key Value object is being filtered. First, by the two page prompts and second, there is a direct filter action defined from the List Control object

In this example, we can see from the Actions Diagram that I used the Container object. I then selected the Precision container type and overlaid the Key Value object on the Line Chart. The only filters applied to these report objects are the page prompts.

And in this last example, you can see how I have no Report or Page prompts or any other filters impacting the Key Value objects. Therefore, these values are representative for the entire data.

Key Value Object in SAS Visual Analytics was published on SAS Users.

6月 072018
 

Light lengthens the day and allows us more time to learn, socialize, contemplate and create. Exploring NASA nighttime satellite images shows how illumination patterns have changed over time. Increases and decreases in illumination show the effects of human civilization on earth. From population collapse and destruction in war zones to economic [...]

The dark side: analyzing global changes in nighttime illumination was published on SAS Voices by Falko Schulz

6月 012018
 

You will not find an object in SAS Visual Analytics named Dynamic Text. Instead, you will find a Text object that allows you to insert dynamically driven data items. By using the Text object’s dynamic capabilities you can build custom report titles, object titles, emphasize measures and even supply the last modified time of the data source in your SAS Visual Analytics Report. In this post, I will outline the ways how you can leverage the Text object’s dynamic capabilities.

In this example report below, I have used a red font color to indicate the dynamically driven text.
Dynamic Text in a SAS Visual Analytics Report

Let’s take a look the available dynamic roles in the Text object. You can see from the Objects pane that the Text object is grouped under Other.

From the Data pane we have the ability to add both Measure and Parameter data items. From the interactive editor of the Text object shown below, we also have the ability to insert the Table Modified Time and Interactive Filters.

The following sections will demonstrate how to configure each of these dynamically driven elements of the Text object.

Interactive Filters

The out of the box display for Interactive Filters includes the selected values for control objects added to either the Report or Page Prompt areas.

To edit, be sure you are in Edit mode of Explore and Visualize. Click on the Text object to make it the active window and double click inside, then the interactive editor will open. Next, click on the Interactive Filters button. Use your cursor to position where you would like to add static text. In this case, I added the qualifier Default filter information:.

Multiple control object values are separated by a comma and also accommodates multi-value control objects.

Parameters

While the Interactive Filter functionality is extremely useful, you may want to use prompt values more granularly to create custom report titles or even object titles. To do this, you must first create a parameter to hold the value selected in the control object, then use that parameter in the Text object.

In my example report, I have two prompts and two custom object titles leveraging parameters. Let’s look at each one individually.

First is the Report Prompt, which prompts for year.

1.     Create your prompt by using the Control object of your choice and assigning the desired data role.
2.     Create a parameter that corresponds to the data type and assign it to the Control object’s Parameter Role.
3.     For the Text object, assign the same parameter to the Text object’s Parameter Role.
4.     Double click on the Text object, use your cursor to add static text as you like.

The steps are similar for the Page Prompt, which prompts for region.

1.     Create your prompt by using the Control object of your choice and assigning the desired data role.
2.     Create a parameter that corresponds to the data type and assign it to the Control object’s Parameter Role.
3.     For the Text object, assign the same parameter to the Text object’s Parameter Role.
4.     Double click on the Text object, use your cursor to add static text as you like.

Even though I demonstrate how to do this for both Report and Page Prompts, this same technique can be used for report canvas prompts. You just have to be sure you store the selected value(s) in a parameter that you can then use in the Text object’s Parameter Role.

Measures

Very much the same way the Text object’s Roles are used to assign the Parameter values, we can do the same thing with a measure. This measure will be affected by any Report or Page Prompts automatically, but if you want to use a report canvas prompt you will need to create the Actions to the Text object appropriately.

Here you can see we are using the measure TotalExpense which is an aggregated measure of Expenses. Like in the previous examples, be sure to assign the measure to the Text object then double click to open the editor and use your cursor to add the static text.

The only applied filters for this aggregated measure are the selected year and region, therefore this Sum _ByGroup_ will return the Total Expenses for that Year and Region.

Table Modified Time

The last capability of dynamic text available in the Text object is the Table Modified Time.

The out of the box display uses the fully qualified datetime stamp and cannot be altered to a different format. To edit, double click inside the Text object and the editor will open. Then click on the Table Modified Time button. Next, use your cursor to position where you would like to add static text. In this case, I added the qualifier Data last updated:.

Conclusion

There are two main takeaways from this blog post. First is that you can easily build dynamic customizable titles, emphasize measures or parameter values.

Second, look to use the Text object for your dynamic text needs.

Here is a quick mapping as a review of what was detailed in the steps above.

 

Using Dynamic Text in a SAS Visual Analytics Report was published on SAS Users.

4月 272018
 

Analyzing ticket sales and customer data for large sports and entertainment events is a complex endeavor. But SAS Visual Analytics makes it easy, with location analytics, customer segmentation, predictive artificial intelligence (AI) capabilities – and more. This blog post covers a brief overview of these features by using a fictitious event company [...]

Analyze ticket sales using location analytics and customer segmentation in SAS Visual Analytics was published on SAS Voices by Falko Schulz

4月 192018
 

In SAS Visual Analytics 7.4 on 9.4M5 and SAS Visual Analytics 8.2 on SAS Viya, the periodic operators have a new additional parameter that controls how filtering on the date data item used in the calculation affects the calculated values.

The new parameter values are:

SAS Visual Analytics filters

These parameter values enable you to improve the appearance of reports based on calculations that use periodic operators. You can have periods that produce missing values for periodic calculations removed from the report, but still available for use in the calculations for later periods. These parameter settings also enable you to provide users with a prompt for choosing the data to display in a report, without having any effect on the calculations themselves.

The following will illustrate the points above, using periodic Revenue calculations based on monthly data from the MEGA_CORP table. New aggregated measures representing Previous Month Revenue (RelativePeriod) and Same Month Last Year (ParallelPeriod) will be displayed as measures in a crosstab. The default _ApplyAllFilters_ is in effect for both, as shown below, but there are no current filters on report or objects.

The Change from Previous Month and Change From Same Month Last Year calculations, respectively, are below:

The resulting report is a crosstab with Date by Month and Product Line in the Row roles, and Revenue, along with the 4 aggregations, in the Column roles.  All calculations are accurate, but of course, the calculations result in missing values for the first month (Jan2009) and for the first year (2009).

An improvement to the appearance of the report might be to only show Date by Month values beginning with Jan2010, where there are no non-missing values.  Why not apply a filter to the crosstab (shown below), so that the interval shown is Jan2010 to the most recent date?

With the above filter applied to the crosstab, the result is shown below—same problem, different year!

This is where the new parameter on our periodic operators is useful. We would like to have all months used in the calculations, but only the months with non-missing values for both of the periodic calculations shown in the crosstab. So, edit both periodic calculations to change the default _ApplyAllFilters_ to _IgnoreAllTimeFrameFilters_, so that the filters will filter the data in the crosstab, but not for the calculations. When the report is refreshed, only the months with non-missing values are shown:

This periodic operator parameter is also useful if you want to enable users to select a specific month, for viewing only a subset of the crosstab results.

For a selection prompt, add a Drop-Down list to select a MONYY value and define a filter action from the Drop-Down list to the Crosstab. To prevent selection of a month value with missing calculation values, you will also want to apply a filter to the Drop-Down list as you did for the crosstab, displaying months Jan2010 and after in the list.

Now the user can select a month, with all calculations relative to that month displayed, shown in the examples below:

Note that, at this point, since you’ve added the action from the drop-down list to the crosstab, you actually no longer need the filter on the crosstab itself.  In addition, if you remove the crosstab filter, then all of your filters will now be from prompts or actions, so you could use the _IgnoreInteractiveTimeFrameFilters_ parameter on your periodic calculations instead of the _IgnoreTimeFrameFilters_ parameter.

You will also notice that, in release 8.2 of SAS Visual Analytics that the performance of the periodic calculations has been greatly improved, with more of the work done in CAS.

Be sure to check out all of the periodic operators, documented here for SAS Visual Analytics 7.4 and SAS Visual Analytics filters on periodic calculations: Apply them or ignore them! was published on SAS Users.

3月 132018
 

SAS Visual Analytics 8.2 introduces the Hidden Data Role. This role can accept one or more category or date data items which will be included in the query results but will not be displayed with the object. You can use this Hidden Data Role in:

  • Mapping Data Sources.
  • Color-Mapped Display Rules.
  • External Links.

Note that this Hidden Data Role is not available for all Objects and cannot be used as both a Hidden Data Role and Data tip value, it can only be assigned to one role.

In this example, we will look at how to use the Hidden Data Role for an External Link.

Here are a few applications of this example:

  • You want to show an index of available assets, and you have a URL to point directly to that asset.
  • Your company sells products, you want to show a table summary of product profit but have a URL that points to each Product’s development page.
  • As the travel department, you want to see individual travel reports rolled up to owner, but have a URL that can link out to each individual report.

The applications are endless when applied to our customer needs.

In my blog example, I have NFL data for Super Bowl wins. I have attached two columns of URLs for demonstration purposes:

  • One URL is for each Super Bowl event, so I have 52 URLs, one for each row of data.
  • The second URL is for each winning team. There have been 20 unique Super Bowl winning teams, so I have 20 unique URLs.

Hidden Data Role in SAS Visual Analytics

In previous versions of SAS Visual Analytics, if you wanted to link out to one of these URLs, you would have to include it in the visualization like in the List Table shown above. But now, using SAS Visual Analytics 8.2, you can assign a column containing these URLs to the Hidden Data Role and it will be available as an External URL.

Here is our target report. We want to be able to link to the Winning Team’s website.

In Visual Analytics 8.2, for the List Table, assign the Winning Team URL column to the Hidden Data Role.

Then, for the List Table, create a new URL Link Action. Give the Action a name and leave the URL section blank. This is because my data column contains a fully qualified URL. If you were linking to a destination and only needed to append a name value pair, then you could put in the partial URL and pass the parameter value, but that’s a different example.

That is using the column which has 20 URLs that matches the winning team in the Hidden Data Role. Now, what if we use the column that has the 52 URLs that link out to the individual Super Bowl events?

That’s right, the cardinality of the Hidden Data Role item does impact the object. Even though the Hidden data item is not visible on the Object, remember it is included in the results query; and therefore, the cardinality of the Hidden data item impacts the aggregation of the data.

Notice that some objects will just present an information warning that a duplicate classification of the data has caused a conflict.

In conclusion, the Hidden Data Role is an exciting addition to the SAS Visual Analytics 8.2 release. I know you'll enjoy and benefit from it.

The power behind a Hidden Data Role in SAS Visual Analytics was published on SAS Users.

3月 012018
 

Let’s say that you are administering a SAS 9.4 environment that is working just fine. You’ve checked that your full backups are indeed happening and you’ve even tried restoring from one of your backups. You are prepared for anything, right? Well, I’d like to propose a scenario to you. You probably have users responsible for creating reports, maybe even very important reports. What if something happened to one of these reports? Perhaps the user wants to revert to an earlier version. Perhaps the report was accidentally deleted or even corrupted, what then? Restoring a full backup in this situation might help this one user but would likely inconvenience most other users. With a little more preparation, you could “magically” restore a single report if needed. Here’s what you need to do: create a backup of only these critical reports using the promotion tools.

The promotion tools include:

  • the Export SAS Package Wizard and the Import SAS Package Wizard available in SAS Management Console, SAS Data Integration Studio, and SAS OLAP Cube Studio.
  • the batch export tool and the batch import tool.

Note: Starting with the third maintenance of SAS 9.4, you can use the -disableX11 option to run the batch import and batch export tools on UNIX without setting the DISPLAY variable.

You can use the promotion tools on almost anything found in the SAS Folder tree, especially if you use SAS Management Console. If you use the wizards in SAS Data Integration Studio or SAS OLAP Cube Studio, those applications only allow you to access and export/import objects that pertain to that application, a subset of what is available in SAS Management Console.

You may be thinking that using an interactive wizard is not really the answer you are looking for and you may be right. The batch tools are a great solution if you want to schedule the exporting of some objects on a regular basis. If you are unfamiliar with the promotion tools, I would suggest you start with the interactive wizards. You will find that the log produced by the wizard includes the equivalent command line you would use. It’s a nice way to explore how to invoke the batch tools.

Creating the Export Package

How to invoke the Export SAS Package Wizard:

1.  Right-click on a folder or object in the SAS Folders tree and select Export SAS Package.

Selectively backing up metadata

2.  Enter the location and name of the package file to be created and set options as appropriate.

You can opt to Include dependent objects when retrieving initial collection of objects here or you can select specific dependent objects on the next screen.

Filtering offers some very interesting ways of selecting objects including:

  • By object name
  • By object type
  • By when objects were created
  • By when objects were last modified

3.  Select the objects to export. If you started the process with a folder, you will be presented with the folder and all of its contents selected by default. You can deselect specific objects as you like.

In this example, we only want the Marketing folder and its contents. Deselect the other folders. You want to be careful to not create a package file that is too big.

You can click on individual objects and explore what dependencies the object has, what other metadata objects use the current object, options and properties for the object.

In this example, the Marketing Unit Report is dependent on the MEGACORP table whose metadata is found in the /Shared Data/LASR Data folder. When you import this report, you will need to associate the report with the same or similar table in order for the report to be fully functional.

If you had selected Include dependent objects when retrieving initial collection of objects on the previous screen, all of the dependent objects would be listed and be selected for export by default.

Bonus things you get by default in the export package include:

  • Permissions set directly on the objects
  • For most object types, the export tools include both metadata and the associated physical content. For example, with reports you get both the metadata and associated report XML. For a complete list of physical content promoted with metadata objects, refer to:

    5.  When the export process is complete (hopefully without errors) review the log.

    At the top of the log, you can see the location of the log file in case you want to refer to it later.

    If you scroll to the end of the log, you’ll find the command line to invoke the batch export tool to create the same package.

    Considerations for Exporting

    Importing to the Rescue

    Let’s talk about what happens if and when you actually need to import some or all of the objects in a package file.
    Let’s take a look at what we would need to do to replace an accidentally deleted report, Marketing Unit Report.

    How to invoke the Import SAS Package Wizard:

    5.  Right-click on the same folder you started the export, SAS Folders folder in our example, and select Import SAS Package. It is important to initiate the import from the same folder you started the export if you want to end up with the same folder structure.

    6.  If needed, use the Browse functionality to locate the correct package file.

    Include access controls

    By default, Include access controls is not selected. This option will import permission settings directly applied to the objects in the package. It will not import any permissions if there were only inherited permissions on the object in the source environment.

    Since we are bringing the report back into the folder it originally came from, it makes sense to also include direct permissions, if there were any.

    If you do not check the Include access controls box and there are in face some direct permissions on objects being imported, you will get this warning later in the wizard:

    Select objects to import

    If you’re not sure whether to select to import All objects or New objects only, you can always start with all objects. You can use the Back buttons in the wizard to go back to previous prompts and change selections, at least before you kick off the actual import process.

    7.  If you selected import all objects on the first screen, you will see a listing of all objects. Each object will have an icon indicating if the object currently exists where you are doing the import or not. The red exclamation mark indicates the object currently exists and doing the import of this object will overwrite the current object with the copy from the package. The asterisk icon indicates that the object does not currently exist and will be created by the import process.

    In our example, the Marketing Unit Report does not currently exist in the Marketing folder but is in the package file so it is labeled with an asterisk. The other two reports are both in the folder and the package file so they are labeled with red exclamation marks.

    You’ll want to make the appropriate selections here. If you want all of the contents of the package to be written to the Marketing folder, overwriting the first two reports and adding the Marketing Unit Report, leave all objects selected. If one of the reports had become corrupted, you could use this method to overwrite the current copy with the version stored in the package file.

    If you just want to replace the missing Marketing Unit Report, make sure only that object is selected as below:

    By default, objects are imported into the same folder structure they were in when the export package was created.

    8.  Part of the import process is to establish associations between the objects you are importing and metadata not included in the package. You are first presented with a list of the metadata values you will need to select.

    9.  Set the target value(s) as needed.

    In our example, we definitely want the report to use the same table it used originally.
    If we were moving objects to a new folder or a new environment, you might want to associate the report with a different table.

    If you use the batch import tool, changing these associations would be done in a substitution properties file.

    10.  Review the import summary and initiate the import process.

    11.  Hopefully, the process completes without errors and you can review the log.

    12.  Finish things off by testing the content you imported. In this case, we would log in to SAS Visual Analytics and view the Marketing Unit Report.

    Considerations for Importing

    • If you initiated the export from the SAS Folders folder and try to import the package from another folder, Marketing for example, the wizard will recreate everything in the package, including a new Marketing subfolder which is probably not what you intended.

    Notice the new Marketing folder inside the current Marketing folder. In addition, all three reports are considered new since the new Marketing subfolder does not currently exist.

    • The account you use to do the import should have enough access to metadata and the operating system.

    Next Steps

    • Decide what you want to export, how often, and how long you want to keep a specific package file.
    • Once you’ve gotten comfortable with the wizards and you want to schedule an export (or several), you should try out the batch export and import tools. When you name the export package, you can consider customizing the package name to include the date to avoid overwriting the same package file each time.

    Review the documentation on both the wizards and batch tools in the Admin Notebook: Making the case for selectively backing up metadata was published on SAS Users.

2月 082018
 

By default, SAS Visual Analytics 7.4 supports country and state level polygons for regional geomaps. In SAS Visual Analytics 7.4, custom shape files are now supported, as well. This means that if a site has their own custom polygon data that defines custom regions, it’s possible to create a region geomap that displays those regions.

Implementing the process requires completing some preparatory steps, explicitly execution of some SAS code, but the steps are explained in Appendix 2 of the SAS Visual Analytics 7.4: Administration Guide. The SAS program that completes the steps is provided for download at http://support.sas.com/rnd/datavisualization/vageo/va74polygons.sas.

Two examples using the program are provided in Appendix 2 for US counties and German provinces. The instructions in Appendix 2 assume that the custom polygon data is provided in ESRI shape file format, which is likely the most common use-case. The site will need access to a SAS programming environment and SAS/GRAPH software, and whoever completes the process will need access to the SAS Visual Analytics configuration directory and the ability to restart services—so an administrator-type person will be required.

One common request is to provide a regional geomap, where the regions are site-defined groups of states or provinces of a country. In this example problem, the site has sales data for each sales region in the US and would like to display a geo map of the regions.

Custom regional map in SAS Visual AnalyticsFor this type of region/province example, you will likely be able to use one of the maps already provided by SAS in the MAPSGFK library to produce your region boundaries. For more information on the datasets in the MAPSGFK library, see this paper. 

The MAPSGFK.US_STATES dataset contains the data required to overlay all states of the US on a VA region geomap and has these columns:

The highlighted columns, STATECODE, LONG, and LAT will be particularly useful, but first, the sales region (REGION) column and values must be added using simple data step code. The unnecessary FIPS code (STATE) can be dropped in the same DATA step.  Note that the region values are assigned in upper case, as these will later be converted to ID values, which VA expects to be in upper case.

data regions;
   length region $ 12;
   drop state;
   set mapsgfk.us_states;
      if statecode in ('AK','HI','PR') then delete;
      else if statecode in ('WA','MT','OR','ID','WY')
         then region='NORTHWEST';
      else if statecode in ('CA','NV','UT','AZ','CO','NM')
         then region='SOUTHWEST'; 
      else if statecode in ('ND','SD','NE','MN','WI','MI','IA','IL','IN')
         then region='NORTHCENTRAL'; 
      else if statecode in ('KS','OK','TX','MO','AR')
         then region='SOUTHCENTRAL'; 
      else if statecode in ('ME','NH','VT','MA','RI','CT','NY','PA','NJ','OH','DE',
'MD','DC')then region='NORTHEAST';
      else if statecode in ('KY','WV','VA','TN','NC','MS','AL','LA','GA','SC','FL')
         then region='SOUTHEAST';
      run;

The data is then sorted by the REGION values, a requirement of the SAS/GRAPH GREMOVE procedure, which is used to remove the internal state boundary data points, leaving the region boundary points only.

proc sort data=regions;
   by region;
 proc gremove data=regions out=mapscstm.regions1;
    by region;
    id statecode;
    run;

To complete the process, since the LAT and LONG values are already in the form that VA needs (unprojected) and we are using a SAS dataset rather than the ESRI shape file format, we’ll only use a part of the code from the downloadable program mentioned at the beginning of the blog.

First, create a mapscstm directory under /SASHome/SASFoundation/9.4 to store the custom polygon dataset.  Make sure that the library is accessible to the SAS session by including a libname statement in the appserver_autoexec_usermods.sas file, found in config/Lev1/SASApp, and then restarting the Object Spawner.

Example:

libname MAPSCSTM “SASHome/SASFoundation/9.4/mapscstm”;

Tip:  Be sure to back up the original ATTRLOOKUP and CENTLOOKUP datasets before running any additional code, as you will be modifying the originals.

To complete creation of the polygon dataset, you will need to execute only a part of the downloadable program to:
• Make sure that your polygon dataset has all of the columns expected by SAS Visual Analytics.
• Add the region attributes to the ATTRLOOKUP.
• Add the region center point locations to the CENTLOOKUP dataset.

%let REGION_LABEL=USRegions;   /* The label for the custom region */
 %let REGION_PREFIX=R1; /* unique ISO 2-Letter Code  */
 %let REGION_ISO=000; /* unique ISO Code  */
 %let REGION_DATASET=MAPSCSTM.REGIONS1;  /* Polygon data set to be 
              created - be sure to use suffix "1" */

Note that the downloadable program includes additional macro assignments and additional code, but since our data is already in the form of a SAS dataset, rather than ESRI shape file format, we won’t be using all of the code.

The following datastep adds the necessary columns/values to the polygon dataset so that the form of the data is what is expected by VA.  Note that the LAT and LONG columns are already in unprojected form, so we just assign the same values to X and Y.  (VA doesn’t actually use the X,Y columns from the polygon dataset.)

data &REGION_DATASET.;
   set &REGION_DATASET.;
   where density <= 3; 
   id=region;
   idname=region;
   x=long;  
   y=lat;
   ISO = "&REGION_ISO.";
   RESOLUTION = 1;
   LAKE = 0;
   ISOALPHA2 = "&REGION_PREFIX.";
   AdminType = "regions";
   keep ID SEGMENT IDNAME LONG LAT X Y ISO DENSITY RESOLUTION LAKE ISOALPHA2 AdminType;
   run;

Then PROC SQL steps are executed to add rows relative to the custom polygons to the ATTRLOOKUP and CENTLOOKUP datasets:

This step adds the USRegions row to ATTRLOOKUP:

proc sql;
   insert into valib.attrlookup
      values ( 
         "&REGION_LABEL.",         /* IDLABEL=State/Province Label */
         "&REGION_PREFIX.",        /* ID=SAS Map ID Value */
         "&REGION_LABEL.",         /* IDNAME=State/Province Name */
         "",                       /* ID1NAME=Country Name */
         "",                       /* ID2NAME */
         "&REGION_ISO.",           /* ISO=Country ISO Numeric Code */
         "&REGION_LABEL.",         /* ISONAME */
         "&REGION_LABEL.",         /* KEY */
         "",                       /* ID1=Country ISO 2-Letter Code */
         "",                       /* ID2 */
         "",                       /* ID3 */
         "",                       /* ID3NAME */
         0                         /* LEVEL (0=country level, 1=state level) */
         );
quit;

This step adds a row to ATTRLOOKUP for each individual region:

proc sql;
   insert into valib.attrlookup
      select distinct 
         IDNAME,            /* IDLABEL=State/Province Label */
         ID,                /* ID=SAS Map ID Value */
         IDNAME,            /* IDNAME=State/Province Name */
 
         "&REGION_LABEL.",  /* ID1NAME=Country Name */
         "",                /* ID2NAME */
         "&REGION_ISO.",    /* ISO=Country ISO Numeric Code */
         "&REGION_LABEL.",  /* ISONAME */
         trim(IDNAME) || "|&REGION_LABEL.",  /* KEY */
         "&REGION_PREFIX.",   /* ID1=Country ISO 2-Letter Code */
         "",                  /* ID2 */
         "",                  /* ID3 */
         "",                  /* ID3NAME */
         1                    /* LEVEL (1=state level) */
   from &REGION_DATASET.;
quit;

This step calculates and adds the central location point for each of the regions to the CENTLOOKUP dataset.   The site data contains only the 48 contiguous states (no Alaska or Hawaii). If Alaska and Hawaii had been included, a different algorithm would need to be used to calculate the central location.

proc sql;
   /* Add custom region */
   insert into valib.centlookup
      select distinct
         "&REGION_DATASET." as mapname,
         "&REGION_PREFIX." as ID,
         avg(x) as x,
         avg(y) as y
      from &REGION_DATASET.;
 
   /* Add custom provinces */
   insert into valib.centlookup
      select distinct
         "&REGION_DATASET." as mapname,
         ID as ID,
         avg(x) as x,
         avg(y) as y
      from &REGION_DATASET.
         group by id;
quit;

After executing the code above, you will need to restart the Web Application server, so that SAS Visual Analytics has access to the new polygons.

Code is also included in the downloadable program to create a dataset for validating your results. The validate dataset includes a column for the ID and IDNAME of the regions, in addition to two randomly calculated measures.  In our case, we will instead just use our original REGIONSALES dataset containing the regional sales data.

1. Sign into SAS Visual Analytics and create a new exploration with data source REGIONSALES.
2. Create a Geo data item from State: Right-click Regions, select Geography?Subdivision(State, Province) Names. From the Country or Region drop-down list, select the USRegions region label.
3. Create a geo map visualization. Select Regions for the map style, Regions for the Geography role, and salesamt for the Color role.

Your regions should display, similar to this:

You can also include the region data item in a hierarchy with the state data item to produce a drill-down region map:

Or a bubble or coordinate map:

I hope this example has been helpful to users of SAS Visual Analytics 7.4.  In my next blog, you will see that this process is tremendously simplified by new mapping features in SAS Visual Analytics 8.2.

Creating a custom regional map in SAS Visual Analytics 7.4 was published on SAS Users.

12月 222017
 

Another report requirement came my way and I wanted to share how to use our Visual Analytics’ out-of-the-box relative period calculations to solve it.

Essentially, we had a customer who wanted to see a metric for every month, the previous month’s value next to it, and lastly the difference between the two.

Relative Period Report in SAS Visual Analytics

To do this in SAS Visual Analytics, which is available in versions 7.3 and above, use the relative periodic operators. I am going to use the Mega_Corp data which has a date data item called Date by Month using the format: MMMYYYY. SAS Visual Analytics supports relative period calculations for month, quarter and year.
The first two columns, circled in red, are straight from the data. The metric we are interested in for this report is Profit.

Next, we will create the last column, Profit (Difference from Previous Period), which is an aggregated measure that uses the periodic operators.

From the Data pane, select the metric used in the list table, Profit. Then right-click on Profit and navigate the menus: Create / Difference from Previous Period / Using: Date by Month.

A new aggregated measure will be created for you:

If you right-click on the aggregated measure and select Edit Aggregated Measure…, you will see this relative period calculation, where it is taking the current period (notice the 0) minus the value for the previous period (notice the -1).

Okay – that’s it. This out-of-the-box relative period calculation is ready to be added to the list table. Notice the other Period Operators available in the list. These support SAS Visual Analytics’ additional out-of-the-box aggregated measure calculations such as the Difference between Parallel Periods, Year to Date cumulative calculations, etc.

Now we have to create the final column to meet our report requirement: the Previous Period column.

To do this we are going to leverage the out-of-the-box functionality of the relative period calculation. Since this aggregated measure calculates the previous period for the subtraction – let’s use this to our advantage.

Duplicate the out-of-the-box relative period calculation by right-clicking on Profit (Difference from Previous Period) and select Duplicate Data Item.

Then right-click on the new data item, and select Edit Aggregated Measure….

Now delete everything highlighted in yellow below, remember to also delete the minus sign. And give the data item a new name. Click OK. This will create an aggregated measure that will calculate the previous period.

The final result should look like this from either the Visual tab or Text tab:

Now we have all the columns to meet our report requirement:

Now that I’ve piqued your interest, I’m sure you are wondering if you could use this technique to create aggregated data items to represent the Period -1, -2, -3 offset? YES! This is absolutely possible.
Also, I went ahead and plotted the Difference from Previous Period on a line chart. This is an extremely useful visualization to gage if the variance between periods is acceptable. You can easily assign display rules to this visualization to flag any periods that may need further investigation.

Relative Period Report in SAS Visual Analytics was published on SAS Users.