Edit

SAS: PROC SQL (Union and UNION Corresponding)

｜2017/01/18
  This is the article for 'UNION' clause which is for 'SET' operation.  In this article, two sample data are described followed by being described two sample program of 'SQL only' and 'SQL Corresponding.'  One note is that next article will be also for 'UNION' clause with other options.

Sample Data (Program and Results) 
Two sample source data for 'UNION' clause, DEMOG1 and DEMOG2.   
-------------------------------------- 
/* The source table was read from LINK*/ 

DATA DEMOG1 ; 
  SET DEMOG ;  
  WHERE DOSE=1 ; 
  KEEP SUBJECT AGE ; /*--PICK UP AGE--*/  
RUN ;















DATA DEMOG2 ;
  SET DEMOG ; 
  WHERE DOSE=2 ; 
  KEEP SUBJECT WT  ;/*--PICK UP AGE--*/
RUN ;















--------------------------------------


SQL Sample Program and the Results (UNION Only) 

  The following example is 'UNION' clause with no option.  The 'UNION' clause sets two tables based on order regardless of variable name.  In the following example, DEMOG1 has SUBJECT and AGE, and DEMOG2 has SUBJECT and WT.  The SUBJECT is common for two tables, but AGE and WT are different variable for these two tables.  However, this 'UNION' clause sets these two variables because these two are second variable in each table.       

/*--------------------------------------------------
* UNION ONLY                                        *
* SET THE VARIABLES BY ORDER REGARDLESS OF VARIABLE *
* 1ST: SUBJECT (PICK UP COMMON),                    *
* 2ND: AGE AND WT.                                  *
*---------------------------------------------------*/
PROC SQL ;
 CREATE TABLE DEMOG3 AS
 SELECT * FROM DEMOG1
  UNION ALL
 SELECT * FROM DEMOG2
 ;
QUIT ;





















 

SQL Sample Program and the Results (UNION CORR) 
  The following example is 'UNION CORRESPONDING' clause.  This clause extract only common variables for both tables.  In this example, common variable is SUBJECT only, and AGE in DEMOG1 and WT in DEMOG2 will be removed from created table.  

/*------------------------
* UNION CORRESPONDING   *
* KEEP COMMON VARIABLES *
*------------------------*/
PROC SQL ;
 CREATE TABLE DEMOG3 AS
 SELECT * FROM DEMOG1
  UNION CORRESPONDING ALL
 SELECT * FROM DEMOG2
 ;
QUIT ;





















NOTE:  If we removed 'ALL' prior to 'UNION' or 'UNION CORRESPONDING,' then duplicated record will be removed from a created table.


REFERENCE
- SAS 備忘録
- Schreier H.  SQL SET OPRATIONS: SO HANDY VENN YOU NEED THEM.  SUGI31 Tutorials.

Search This Blog