Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Union multiple Data Query in SAS VA

Reply
Contributor
Posts: 54

Union multiple Data Query in SAS VA

Hi,

I'm just new in SAS VA development. Anyone knows how to union multiple data query in SAS VA? Appreciated your quick response.

Occasional Contributor
Posts: 15

Re: Union multiple Data Query in SAS VA

Hello.  I basically have the same question but I want to know how to do a UNION (not a join) using the "Design" tab in the visual data query.  I believe that I found a way to do this using the "Code" tab by manually writing the code for a UNION.  Unfortunately, once edits are made on the "Code" tab, you lose the "Design" and "Results" tabs.  This is some code I used to UNION two tables on the "Code" tab.  The results appear to be correct for my situation.  NOTE: It appears that VA has an option to append tables for tables that are already in memory.  However, I’m not sure how that works if the tables don’t have the same number of columns.

  • Press the "New" Query button
  • Press the "+" button to add the first table only
  • Choose the appropriate columns from the first table
  • Make any necessary adjustments to the "Type" and "Format" columns.  From my trials and many errors, it appears that the "Type"s and "Format"s should be adjusted so they will also work with the tables that you will UNION
  • Make any necessary changes to the output table name, location, or library
  • Save the data query
  • Run the data query
  • Click on the "Code" tab
  • Press the lock icon to unlock the code for editing.  After unlocking the code, you will no longer be able to use or see the "Design" or "Results" tabs!
  • You should see a PROC SQL statement followed by CREATE VIEW, SELECT, and FROM statements.  The SELECT and FROM statements reflect the table added using the "Design" tab.  After this step, this is what I saw:

          proc sql noprint;

          create view TEMP_LASR_VIEW_476 as

        SELECT

          TABLE1.COL1 length=8 format=BEST12. AS COL1,

          TABLE1.COL2 length=8 format=BEST12. AS COL2,

          TABLE1.COL3 length=10 format=$10. AS COL3,

          TABLE1.COL4 length=26 format=$26. AS COL4

        FROM

          LASRLIB.TABLE1 TABLE1;    

To begin the UNION, you must delete the semicolon at the end of the table alias (TABLE1) in the line of code above as you will be continuing the statement.  I changed “create view” to “create table”.  For my particular situation, I created a UNION by using the UNION ALL command.  The new code with both tables is as follows:

     proc sql noprint;

          create table TEMP_LASR_VIEW_476 as

        SELECT

          TABLE1.COL1 length=8 format=BEST12. AS COL1,

          TABLE1.COL2 length=8 format=BEST12. AS COL2,

          TABLE1.COL3 length=10 format=$10. AS COL3,

          TABLE1.COL4 length=26 format=$26. AS COL4

        FROM

          LASRLIB.TABLE1 TABLE1

        UNION ALL

        SELECT

          TABLE2.COL1 length=8 format=BEST12.,

          TABLE2.COL2 length=8 format=BEST12.,

          TABLE2.COL3 length=10 format=$10.,

          TABLE2.COL4 length=26 format=$26.

        FROM

          LASRLIB.TABLE2;

Notice that the semicolon is now behind TABLE2 to indicate the new end of the CREATE VIEW statement.

I am very new to VA and I hope that there is an easier way to do this.

SAS Employee
Posts: 35

Re: Union multiple Data Query in SAS VA

Hi Carrot17,

As Coop mentioned, I think that appending in-memory tables could be the solution that you're looking for.  Here is some additional information about it as well as instructions on how to perform this task from the data builder:

SAS(R) Visual Analytics 7.2: User's Guide

Coop, in terms of your question about appending in-memory tables, this might be the answer that you’re looking for:

“If a source table has columns that are not present in the base table, then the columns are dropped and are not appended to the base table. The base table always maintains the same number of columns.”

Best regards,

Lorrie

Ask a Question
Discussion stats
  • 2 replies
  • 561 views
  • 0 likes
  • 3 in conversation