Union multiple Data Query in SAS VA

Carrot17 — Thu, 11 Jun 2015 03:12:31 GMT

Hi,

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

Re: Union multiple Data Query in SAS VA

Coop — Thu, 11 Jun 2015 15:10:26 GMT

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.

Re: Union multiple Data Query in SAS VA

Lorrie_SAS — Thu, 11 Jun 2015 21:17:22 GMT

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

topic Re: Union multiple Data Query in SAS VA in SAS Visual Analytics

Union multiple Data Query in SAS VA

Re: Union multiple Data Query in SAS VA

Re: Union multiple Data Query in SAS VA