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.
