6 Replies Latest reply on Mar 23, 2012 8:15 PM by Robert Morton

    Reading SAS dataset in Tableau 7 without sorting

      Hi,

      I have recently used SAS ODBC to read data from SAS dataset. I have picked the option to create a copy of data in Tableau. I am importing 662 variables and one challenge I am facing is that Tableau sorts the data alphabetically when it opens it in Tableau. I maintain a certain order in the SAS Dataset so that we can report in a certain order.

       

      Is there anyway I can bring in the data as is?

       

      Thanks for your help.

      Rahman

        • 1. Re: Reading SAS dataset in Tableau 7 without sorting
          Robert Morton

          Hi Rahman,

           

          According to the SQL standard, there's no such thing as an implicit ordering between records in a table. Since our ODBC connector follows the standard, it cannot make use of implicit ordering present in the SAS data set. If you wish to maintain a specific ordering you must expose that ordering as a field in the table, e.g. an ID field or some sort of numeric, ascending field. You can then use that field to sort the data displayed in your visualization.

           

          -Robert

          • 2. Re: Reading SAS dataset in Tableau 7 without sorting

            Hi Robert,

            Thanks for your reply.

            I realize that the SAS ODBC connector to Tableau follows the standard (what I see in my case is sorting the data in alphabetical ascending order). Is there any way we can change this default to no sorting ... i.e. can I bring in the data without any sorting?

             

            Thanks,

            Rahman

            • 3. Re: Reading SAS dataset in Tableau 7 without sorting
              Tim Latendress

              Rahman,

               

              I think I might understand your issue. It sounds like you might be referring to the order of the variables as they are brought into the Dimensions/Measures windows. So it's not the "data order" but the "variable order" in your SAS datasets (and I know that can be a lot of work in SAS to get the variables ordered the way you want them -- especially when there are 600+!).

               

              If that is the case, on the Dimensions header click on the drop-down arrow, select Sort By and then Data Source Order (the default is Name order). Is that what you were looking for?

               

              Tim

              1 of 1 people found this helpful
              • 4. Re: Reading SAS dataset in Tableau 7 without sorting

                Hi Tim,

                 

                Thank you very much for your reply. You have got my issue right.

                 

                Your suggestion works perfect if I Connect Live' to the SAS data source. However, Tableau suggests copying the data into Tableau in order to use all of their functionalities. And if I bring in the data into Tableau, when I select Sort By, the 'Data Source Order' is grayed out.

                 

                Do we have any solution to that issue?

                 

                Thanks for your help.

                Rahman

                • 5. Re: Reading SAS dataset in Tableau 7 without sorting
                  Tim Latendress

                  Hi Rahman,

                   

                  Apparently the grayed out Data Source Order option is not a new issue. Here is another unanswered thread on it (not ODBC-related):

                   

                  http://community.tableau.com/thread/114087

                   

                  Someone posted a solution which was to preface their column names with a number that represented the order they wanted the variables to appear in the Dimensions/Measures window. Of course SAS won't allow numbers as a prefix of a variable name so you would need to preface with a character, then the sequence number and then some sort of separator. Not very practical.

                   

                  ODBC has many limitations and the SAS ODBC driver in particular isn't necessarily the most friendly. So I was about to write it off as another limitation of the driver. But after digging a little deeper into the SAS ODBC documentation I see they support the Core ODBC function "SQLColumns" for which SAS uses a specially formatted query of DICTIONARY.COLUMNS. That specialty dataset is essentially the SAS metadata file that describes the columns in any given SAS dataset. That particular query returns the following columns:

                   

                  # Variable Type Len Label

                   

                  1 libname Char 8 Library Name

                  2 memname Char 32 Member Name

                  3 memtype Char 8 Member Type

                  4 name Char 32 Column Name

                  5 type Char 4 Column Type

                  6 length Num 8 Column Length

                  7 npos Num 8 Column Position

                  8 varnum Num 8 Column Number in Table

                  9 label Char 256 Column Label

                  10 format Char 49 Column Format

                  11 informat Char 49 Column Informat

                  12 idxusage Char 9 Column Index Type

                  13 sortedby Num 8 Order in Key Sequence

                  14 xtype Char 12 Extended Type

                  15 notnull Char 3 Not NULL?

                  16 precision Num 8 Precision

                  17 scale Num 8 Scale

                  18 transcode Char 3 Transcoded?

                   

                   

                  Item # 8 on that list is VARNUM which is the Order of the variables in a SAS dataset. In other words, that is the field we would want Tableau to use to order the Dimensions/Measures (Data Source Order). But since that option is grayed out there may be a limitation in Tableau when using ODBC. Maybe we can modify the Tableau ODBC connection query to take advantage of the availability of VARNUM?  It might be doable since (miraculously) SAS seems to support it.

                   

                  I am willing to test this out if we can get that far.

                  • 6. Re: Reading SAS dataset in Tableau 7 without sorting
                    Robert Morton

                    Tim, thanks for clarifying. I definitely missed the point in Rahman's original post. I don't know that Tableau can make use of the API you describe, because it appears completely inconsistent with the ODBC 3.x SQLColumns API documented here: http://msdn.microsoft.com/en-us/library/ms711683%28VS.85%29.aspx

                     

                    SQLColumns does provide the ordinal position of each column, so we could look into using that information. If I recall correctly, the problem I discovered early on is that some data sources were not consistent in providing unique, densely ordered column ordinals. A possible improvement for Tableau's ODBC connector is to attempt to honor the ordinal data if it is safe to do so. I'll add that to the wishlist, but I cannot commit to any timeframe or priority for this work.

                     

                    -Robert