Relationships are easy – for SAS objects!

Somebody once said that relationships are not easy. Well now they are—in SAS 9.4, at least!

SAS objects have inherent relationships and dependencies, and a change to one element can often have an impact on one or many related objects. For example, for SAS Visual Analytics reports to work as designed, they need to have access to the data that was used to create them. However, when looking at a report, it is often difficult to determine which data table is the source, what columns are used in the report, or what LASR library the data uses? Enter the batch relationship reporting tools.

The batch relationship reporting tools were introduced in SAS 9.4. These tools enable you to identify relationships among the content objects in the SAS Folder tree. For example, you can identify:

  • the data that a report depends on—down to the column level
  • the jobs that contain a given data table or transformation
  • the tables that that are associated with a given library

If you are familiar with the SAS BI Lineage plug-in in SAS Management Console, the SAS 9.4 relationship reporting provides similar functionality. It’s main advantaged is that these batch tools cover a much wider variety of objects.

Relationship reporting tools

The batch relationship reporting tools are located in the SAS install directory under SASHome in SASPlatformObjectFramework/9.4/tools.

Generating a report is a two-step process, using these tools:

  • Use  the Relationship Loader batch tool (sas-relationship-loader), located in the admin sub-directory of the tools directory, to scan folders and objects, retrieve their relationship information, and load the information into a database in the Web Infrastructure Platform Data Server.
  • Use the Relationship Reporter batch tool (sas-relationship-reporter) to read the database populated by the relationship loader and report on the relationship between objects.

As an alternative to using the relationship loader to load the relationships, you can configure a regularly scheduled job that scans and loads relationship data for all of the content objects in the SAS Folder tree. The instructions to setup a regular loads of relationships can be found under Configuring Automatic Relationship Loading in the SAS(R) 9.4 Intelligence Platform: System Administration Guide.

How to load relationships to the database

The first step in reporting is to load the relationships to the database. The sample command below will load all the relationships to the database. You must pass the web server connection properties, the host and the port, the unrestricted administrative user on the metadata server, and the unrestricted user’s password.

./sas-relationship-loader -host
-port 1234 -user abcdef@abcdef -password Xyz123 -loadAll

The –loadall option specifies that relationships are to be loaded for all content objects in the SAS Folders tree. An alternative to the –loadall option is to specify content filters to select the objects whose relationship data is to be loaded.

The Relationship Loader will provide feedback to the screen as it loads the relationship information. The information is stored loaded to a database in the Web Infrastructure Platform Data Server.


How to report on relationships among SAS content objects

To report on the relationships, choose from four standard reports or select your own options to produce more fine-tuned results. Reports can be written to the console or to a file in either comma-separated values (CSV) or text file format.

To select the objects whose relationships you want to report (referred to as subjects), you can specify one or more locations in the SAS Folder tree, or you can specify filtering criteria.

The standard reports are:

  • Lineage which reports the objects that each subject depends on. An object depends on another object if it cannot function or cannot be defined without the other object, for example, a report is dependent on a LASR table
  • Impact which reports objects that depend on or contain each subject. An object contains another object if it cannot exist out with the other object, for example, a table contains columns.
  • Direct dependencies which reports the objects that each subject directly depends on.
  • Indirect dependencies which reports objects that each subject depends on either directly or indirectly.

Example of a direct dependencies report

Let’s look at an example of a report, the command below uses the –report option to execute the standard report on direct dependencies for objects in the /Demos folder.

./sas-relationship-reporter -host -port=1234 
-user abcdef@abcdef -password Xyz123 -report directDependencies “/Demos” 

The output generated shows the Visual Analytic reports, and the objects that they depend on, in this case the LASR tables, LASR library and Load job. The relationship direction is noted with an arrow.
“/Sales/Reports/Dashboard” (SAS report (2G))
–> Is dependent on: “/Sales/LASR Data/DASHBOARD” (Table)
–> Is dependent on: “/Sales/LASR Data/LASR Library” (Library)
–> Is dependent on: “User Written” (JobTransformation) – in “/Sales/LASR Data/DASHBOARD 
– Load Job 0925140747AM” (Job) 

“/Sales/Reports/VAD – Monthly Sales and Business Profit” (SAS report (2G))
–> Is dependent on: “/Sales/LASR Data/PROFIT_COMPANY_PRODUCT_MONTH” (Table)
–> Is dependent on: “/Sales/LASR Data/LASR Library” (Library)
–> Is dependent on: “User Written” (JobTransformation) – in “/Sales/LASR Data/PROFIT_COMPANY_PRODUCT_MONTH – Load Job 0925140749AM” (Job)

Example of an impact report

What if you want to determine the impact of changing one LASR table? This example selects an individual LASR table and generates an impact report. The output shows that five Visual Analytics Reports would be impacted by any change to the PROFIT_COMPANY_PRODUCT_MONTH LASR table.

./sas-relationship-reporter -host -port=1234 -user abcdef@abcdef 
-password Xyz123 -report impact “/SharedData/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH(Table)”

The output generated shows the Visual Analytic reports, and the objects that they depend on. The relationship direction is noted with an arrow.

“/Shared Data/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH” (Table)
< – Is dependent on: “/Demos/VAD – Monthly Sales and Business Profit” (SAS report (2G))
< – Is dependent on: “/Demos/VAD – Sales and Profit Analysis” (SAS report (2G))
< – Is dependent on: “/Demos/VAD-Sales and Cost Trend – Profit by Product” (SAS report (2G))
< – Is dependent on: “/Demos/VAE – Profit and Sales Bubble_report” (SAS report (2G))
< – Is dependent on: “/Demos/VAE – Profit by Product Hierarchy_report” (SAS report (2G))
< – Is dependent on: “Table metadata update” (JobTransformation) – in “/Shared Data/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH – Load Job 11251           40817PM” (Job)
< – Contains: “/Shared Data/VisualStatistics/LASRTables/PROFIT_COMPANY_PRODUCT_MONTH – Load Job 1125140817PM” (Job)

Using relationship content filters to subset reports

There are many ways to use filters to subset the reporting. Relationship filters can subset the relationship types and scope to be included in the report output. Relationship content filters select the related objects that are to be included in the report output.

To the subset the subject of the reports you can use the object path, as we did in the first two examples, or you can use content filters to select the objects that you are interested in. Content filters can be defined on a variety of metadata attributes including created or modified date, attribute name and value, name, description, keyword or object type.

If you want to report on certain types of subject, for example SAS Visual Analytics reports, the first step is determining the public object types to include in the query. To do that open up the object type dictionary in SAS Management Console. In SAS Management Console select the Folders tab and expand System ? Types ? to browse the public type definitions.

Select SAS report (2G), right-click and select Properties then select the Advanced tab. Note the TypeName is Report.BI, this is the value that needs to be passed to the types option of the tool. It is the public type of the object. NOTE: the values must be passed in this exact case.


This example will create a lineage report for each SAS Visual Analytics report in the repository.

./sas-relationship-reporter -host -port=1234 -user abcdef@abcdef 
-password Xyz123 -report lineage -type Report.BI

For each report the output shows the table that the report is dependent on, the library that the table is dependent on and the columns that the table contains.

“/User Folders/Susan/My Folder/Cars Summary Report” (SAS report (2G))
–> Is dependent on: “/Shared Data/VisualStatistics/LASRTables/CARSSASHELP” (Table)
–> Is dependent on: “/Products/SAS Visual Analytics Administrator/Visual Analytics LASR” (Library)
–> Contains: “Cylinders” (Column)
–> Contains: “DriveTrain” (Column)
–> Contains: “EngineSize” (Column)
–> Contains: “Horsepower” (Column)
–> Contains: “Invoice” (Column)
–> Contains: “Length” (Column)
–> Contains: “Make” (Column)
–> Contains: “Model” (Column)
–> Contains: “MPG_City” (Column)
–> Contains: “MPG_Highway” (Column)
–> Contains: “MSRP” (Column)
–> Contains: “Origin” (Column)
–> Contains: “Type” (Column)
–> Contains: “Weight” (Column)
–> Contains: “Wheelbase” (Column)

This blog has only scratched the surface of the Batch Relationship Reporting Tools.  The tools are fully documented in the SAS 9.4 Intelligence Platform: System Administration Admin Guide.

Post a Comment

SAS Global Forum 2015: three exciting changes that make it the best ever

This year I am expecting SAS Global Forum to be one of the biggest and best ever!

The SAS Global Users Group makes changes and enhancements each year at SAS Global Forum based on the feedback we receive from SAS users. And I am pleased to be the first to say “We heard you!” and to have a role in making some of them happen. Here are just a few things I am excited to share with you for this year’s conference in Dallas from April 26-29.

First-Timers’ Session. I am especially excited about the First-Timers’ Session being held on Sunday at 4:30pm (and not only because I am one of the presenters!)  We heard you last year when you told us you wanted more information on how to make the most out of your conference experience. This year, we will use the First-Timers Session to share tips to help navigate the 2-1/2 days.

How-To Videos. Sharing this information at the First-Timers Session should be a breeze for organizers to present since all attendees will have access to ‘How To’ videos before the conference. For the first time, a series of brief videos will be available to attendees in the weeks before the conference. You’ll be able to view them in various locations throughout SAS Global Forum too. The videos will demonstrate how to build your agenda, how to use the ‘mobile app’ to make the most of your time at the conference, how to navigate the conference and how to navigate the Quad.

The Quad. Now you’re thinking: ‘What is the Quad’? The area formerly named as the SAS Support and Demo Area has changed over time to include a lot more than SAS Demos. This year, we’re introducing the Quad, a newly designed space for you to gather, learn and network with other users and SAS experts.

The Quad will still have the popular SAS Demos, Code Doctors, ePosters and so much more. It also boasts exciting new features such as Table Talks, theater sessions, games and food. You’ll meet other SAS enthusiasts, have direct access to developers, and find time to talk through business strategies.

As a member and Chairman of the SAS Global Users Group Executive Board, I invite you to join us for this great conference. I am looking forward to seeing you in Dallas!

Questions or comments about the upcoming conference or these exciting changes?  Please contact me through my LinkedIn address.

- Nancy K. Wilson

Post a Comment

SAS Visual Analytics: creating a custom sort order

Does your week start on Sunday or Monday? Do you have a promotional product you would like to see at the top of your list? Do you have a particular order in which you like to view your regions?

Custom sorting is now possible with SAS Visual Analytics 7.1. The ability to specify a custom sort order is available for category data items only. This includes any calculated data items that are categories and custom categories.

There is a limit, however.  You can only specify a custom sort order for up to 25 values.  This restriction does not mean that your category data item has to have a cardinality of 25 or less. It means, for example, that if you have 100 unique values for a category data item, you would only be able to specify the sort order for 25 of those 100 values.

Let’s look at how to create a custom sort order in SAS Visual Analytics Designer:

Selecting data values. Simply right-click on your category data item from the Data tab, and select New Custom Sort….  In the New Custom Sort dialogue, move the values over to the Sorted Items column in the order you want them sorted.


Handling larger numbers of values. You can do the same thing for a category data item with a larger number of values. In this example, I specify a sort order for the Product data item. You can see in the background I created a crosstab with Product Line then Product on the rows. I wanted to sort the Promotional Products to the top of the list, and I used the crosstab to help me quickly identify those values.

Notice for both examples that I only selected the values I wish to be sorted. The rest of the values will be sorted either ascending or descending depending on what is selected for your visualization.


Producing final output. Here you can see in the final screenshot that the Promotional Product Line is the first gauge and that the Promotional Products are sorted to the top of the bar chart and then the rest of the values are sorted ascending.


I hope this new SAS Visual Analytics feature will help you fine-tune those report details.


Post a Comment

Configuring storage for SAS--additional guidelines

My Performance Validation team in SAS R&D is constantly working with our partners to test how their storage arrays work with SAS.  In late 2014, we finalized several papers that discuss how a mixed analytics workload performs on several storage arrays.  While doing this testing, we also listed lessons-learned in the tuning guidelines of each paper.

Please review the papers listed below:

These papers, along with lots of other papers for other storage, can be found in Usage Note 53874: Troubleshooting system performance problems: I/O subsystem and storage papers.  Please bookmark this SAS Usage note as we update this list of papers regularly.

Let me know if you have questions about these papers or if there are other new storage systems that you would like SAS to test.

Post a Comment

SAS Global Forum 2015—arrive early, stay late, learn and save

I’m not a seasoned business traveler so I generally plan to arrive extra early and leave a little late to avoid any last-minute stress. The problem with all that stress-avoidance is that I often have extra time on my hands and am stuck with finding ways to entertain myself.

What about you? How do you plan to occupy your extra time while in Dallas for SAS Global Forum 2015?

I was checking out the February issue of the SAS Training and Book Report Extra, and the headline SAS Training Discounts at SAS Global Forum caught my eye. There are advantages to arriving early or staying late at SAS Global Forum—you can use that time to improve your SAS skills and save!

SAS Education is offering training courses in conjunction with the conference:  six of them at the Kay Bailey Hutchison Convention Center and two at the Dallas SAS Training Center. What’s more, SAS Global Forum attendees are eligible for rates as low as $399 a day. You can save hundreds for courses like “Introduction to SAS and Hadoop” or Chris Hemedinger’s “Developing Custom Tasks for SAS Enterprise Guide”.

Conference attendees can also receive a 50% discount on popular SAS Certification exams if they sign up through the SAS Global Forum registration site.

Good news, right! But you don’t have time to stay for a full SAS course?

You can still make good use of that extra time with more than a dozen pre- and post-conference tutorials. These expert-led sessions provide training you may not find elsewhere, cost less than $200 and include relevant handouts. Topics range from beginner to advanced. A few that intrigue me:

Of course, there’s always the option of sharing food, drinks and ideas while networking with fellow attendees!

But if you really want to maximize your SAS Global Forum discounts, register this week. Saturday, February 28 is the last day you can receive the early-bird discount rate.

Post a Comment

SAS Environment Manager:  compatible groups save time

Many larger SAS deployments have multiple instances of similar SAS-related servers. For example, a distributed SAS Enterprise BI environment may have several machines running instances of the object spawner or the OLAP server. Similarly, all of your distributed SAS Visual Analytics deployments have worker nodes that are typically dedicated Linux machines that serve only the needs of SAS Visual Analytics users. As a SAS administrator, it is often useful to understand metrics across a collection of these similar resources to keep tabs on the performance of the system as a whole. Fortunately, SAS Environment Manager provides compatible groups as a way to summarize metrics across a collection of similar resources.

To illustrate the usefulness of this feature, let’s suppose that our organization has a distributed SAS Visual Analytics environment with three worker nodes that host all of our in-memory data. Maybe the CEO relies this data to run the company, so we want to keep an eye on these machines as a unit (and make sure the CEO stays happy!). We could, and should, of course, monitor each machine individually for thoroughness, but it is also useful to visualize trends across a collection of similar resources to help spot potential problem areas. Additionally, it saves me time if I can check up on resources in groups of instead of having to dig into each one individually.

Finding a list of compatible groups. So, let’s start by opening SAS Environment Manager. On the Resources page, we can see that there are several predefined compatible groups in our inventory.


Defining a new group. We then select New Group from the Tools Menu and give our group a name, description, and identify what type of resource we intend to group together. In this case, we are going to group three Linux machines that serve as our SAS Visual Analytics nodes.


Selecting specific platforms. The final step is to select the specific Linux platforms from our available inventory. In this case, I have chosen to group these three machines together because I expect the workload for each machine to be relatively uniform.


Monitoring group performance. That’s it. Now we can select our compatible group named VA Nodes and view metrics and performance over time for these three machines as a unit. Because every member of a compatible group is uniform, the metrics collected across the group can be aggregated for reporting purposes. For example, here is a look at the file system read and write operations and the amount of free memory across our three SAS Visual Analytics nodes:


Monitoring individual machines. Examining metrics across the three machines in our group is easy to do as well. Just select one of the metric charts from the Monitoring view, and you can compare the performance of each individual machines across time. In this case, we expect all three machines to display similar performance characteristics and that is confirmed in this graph.


So there you go. Compatible groups can come in handy to investigate and report on the performance of a set of similar resources.

Happy monitoring.

Post a Comment

SAS macro variables: how to determine scope

Have you ever created a SAS macro variable and at resolution time received a warning that it did not exist? Many times this warning is because your program referenced the macro variable outside the scope it was created in.

Every macro variable created is stored in one of two symbol tables. The symbol table lists the macro variable name and its value and determines its scope. Global macro variables, or those stored in the global symbol table, exist for the duration of the SAS session and can be referenced anywhere except in the CARDS and DATALINES statements. Local macro variables, or those stored in a local symbol table, exist only during the execution of the macro in which the variable is created.

This post will help you determine which scope a macro variable will be defined in. I will also show a nice feature of CALL SYMPUTX for assigning the scope for macro variables. Lastly, I will discuss some SAS functions that help determine if a macro variable exists in one of the two scopes.

More about the two types of scope

Global macro variables include the following:

  • all automatic macro variables except SYSPBUFF
  • macro variables created outside of any macro definition
  • macro variables created on a %GLOBAL statement
  • most macro variables created by CALL SYMPUT/CALL SYMPUTX, except in special cases

Local macro variables include the following:

  • macro parameters
  • macro variables created on a %LOCAL statement
  • macro statements that define macro variables within a macro definition, such as %LET and the iterative %DO statement (if the variable does not already exist globally or a %GLOBAL statement is not used)

The following is a nice diagram that illustrates what happens when creating a macro variable. This diagram does not apply when a %LOCAL statement is used to create the variable.


Difference in scope between CALL SYMPUT and %LET

CALL SYMPUT and %LET are the two most popular ways of creating a macro variable so we will focus on those two methods.

In the first example, notice that CALL SYMPUT placed the macro variable on the global symbol table because once it determines the macro variable does not exist, it places it in the first non-empty symbol table it finds, starting with the most local scope. In this case, this is the global table (global table is never empty as it contains the SAS automatic variables).

%macro test;                                                                                                                       
 data one;                                                                                                                          
    call symput('bbb',100);                                                                                                          
%put _user_;                                                                                                                    
%mend test;                                                                                                                       

SAS Log results:


In this example, %LET placed the macro variable CCC in the local symbol table because once it determines the macro variable does not exist it creates the variable in the scope for the current macro. In this case, this is the local table since we are within the local scope of the macro test2. A macro's local symbol table is empty until the macro creates at least one macro variable.

%macro test2;                                                                                                                           
 %let ccc=200;                                                                                                                          
 %put _user_;                                                                                                                           
%mend test2;                                                                                                                            

SAS Log results:


Problems with nesting local variables

Local symbol tables can also be nested within each other. In this example we have one global macro variable called OUTER. One local macro variable, AA, local to the macro TEST and one local macro variable, BB, local to the macro test2.  Since the macro TEST2 is nested within the TEST macro, the local table for TEST2 is also nested within the local table for TEST. The diagram below shows the intended scope for local variables AA and BB:


Processing the following code reveals problems with referencing:

%let outer=500;                                                                                                                         
%macro test2;                                                                                                                           
 %let bb=200;                                                                                                                           
 %put &aa;                                                                                                                              
%mend test2;                                                                                                                            
%macro test;                                                                                                                            
 %let aa=100;                                                                                                                           
 %put &bb;                                                                                                                              
%mend test;                                                                                                                             

SAS Log results:

WARNING: Apparent symbolic reference BB not resolved.

Why did this happen? The first %PUT encountered (%PUT &AA ) resolves to 100 even though we are within the TEST2 macro, but this is because TEST2 is nested within TEST. The second %PUT encountered (%PUT &BB) does not resolve because BB is local to the TEST2 macro and is not known to macro TEST.

 Using CALL SYMPUTX to assign scope

To force macro variables created by CALL SYMPUT to be global when the local table is not empty, use a %GLOBAL statement, listing all the variables. This would be difficult if creating a list of macro variables.  Now with the addition of CALL SYMPUTX this task is much easier.  CALL SYMPUTX contains an argument that specifies the scope in which to place the macro variable. The following values are valid as the first non-blank character in symbol-table:

G specifies the global symbol table, even if the local symbol table exists.
L specifies the most local symbol table that exists, which will be the global symbol table, if used outside a macro.
F specifies that if the macro variable exists in any symbol table, CALL SYMPUTX uses the version in the most local symbol table in which it exists. If the macro variable does not exist, CALL SYMPUTX stores the variable in the most local symbol table.

Suppose you are inside a macro and want to create a macro variable for each observation from a data set. The example below would fail because the macro variables val1-val3 are local to the macro TEST and only exist within the TEST macro.  Once TEST has finished executing those macro variables are deleted.

data one;                                                                                                                               
input name $;                                                                                                                           
%macro test(dsn);                                                                                                                       
data _null_;                                                                                                                            
 set one;                                                                                                                               
   call symput('val'||strip(_n_),name);                                                                                            
%mend test;                                                                                                                             
%put &val1 &val2 &val3;

In the past you would have to add something like the following before the CALL SYMPUT statement to make the macro variables global:

call execute('%global val'||strip(_n_)||';');

Now with CALL SYMPUTX this is easy. Just change the CALL SYMPUT above to the following. The ‘g’ argument makes all the macro variables created by this call routine global.

call symputx('val'||strip(_n_),name,’g’);

How to determine where macro variables are stored

Here are a few statements that are very helpful in determining which symbol table the macro variables are stored in:

  • %PUT _USER_;
  • %PUT _LOCAL_;
  • %PUT _GLOBAL_;

There are times you may want to find out if a macro variable exists in a certain scope. There are three functions that might help in this situation.

  • %SYMEXIST(mac_var) – returns 1 if macro variable exist, otherwise 0
  • %SYMGLOBL(mac_var) – returns 1 if macro variable exist in global scope, otherwise 0
  • %SYMLOCAL(mac_var) – returns 1 if macro variable exists in local scope, otherwise 0

I hope this blog post has been helpful. If you have other SAS macro questions you’d like me to cover in future blogs, please comment below.

Post a Comment

SAS love is in the air!

HeartLogoLove is in the air and things are starting to heat up for Valentine’s Day. OK—maybe not for Connecticut and the rest of the northeast buried in another foot of snow and more on the way!

Perhaps some stories of love could help melt the ice away. They’re happening every day for SAS users—those stories of love about a product and company bringing people together to help move the world.

Have you heard the story about the love of an unborn child and how SAS the company and SAS the software changed the outcome for one family?

Or maybe about how studying data can save lives?

Or a common story about a story of two people (both users of SAS software) who fell in love, married and had a family. Now they work together with a team of users to put on the biggest gathering of SAS users in the world—SAS Global Forum, a place where you can share your tips and tricks or stories of how SAS has touched your life.

Share your SAS love story here!  And plan come to SAS Global Forum 2015 in Dallas where it’s already 75° F., sunny and only going to get hotter April 26-29.

Post a Comment

SAS Global Forum 2015: Connect with the experts through Table Talks

I stated in my previous blog about the value and benefits of volunteering that SAS Global Forum is designed to bring users with questions together with users with know-how. This goal is accomplished primarily in breakout and ePoster presentations. During his keynote address at SAS Global Forum 2014, Futurist Thornton May described and demonstrated how to make presentations more interesting and engaging by interacting with the audience.

Beginning in Dallas this April, SAS Global Forum will act on Thornton May’s advice by organizing additional opportunities for users with questions to query users with know-how. The all new Table Talk sessions are intimate, round-table discussions among a dozen or so participants. Table Talk presenters are subject matter experts who will open and facilitate discussions important to all those attending. Read More »

Post a Comment

SAS Global Forum: the skinny on ePosters

I love to teach, but it took several years of teaching before I felt comfortable being in front of a class. And having taught for over 20 years, the fear of presenting in the classroom has passed, but what about presenting at professional meetings or in front of my peers? I still get nervous for presentations outside of the classroom, but the 20 years of teaching has helped me control my nervousness.

Like me, some of this year’s SAS Global Forum presenters have a fear of public speaking.  Others have topics that lend themselves to more visual treatment. Others simply prefer the give-and-take of an informal discussion. Rather than giving a paper, they have chosen to present their ideas as an ePoster. Read More »

Post a Comment