Hi,
I'm just new in SAS VA development. Anyone knows how to union multiple data query in SAS VA? Appreciated your quick response.
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.
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.
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.