PROC SQL UNION ALL is deleting duplicates

[user34567]

Hi,

I'm using the query below to combine three tables but in the output table the duplicates are deleted. Not sure why since I did use an UNION ALL.

UREN_2021 and UREN_2022 are a direct copy of UREN_2023 since I'm still testing.

SELECT * FROM "Table"."UREN_2021"

UNION ALL

SELECT * FROM "Table"."UREN_2022"

UNION ALL

SELECT * FROM "Table"."UREN_2023"

Does someone know what is causing this issue?

[Sajid01]

What does the log say?
As a reminder we don't what your data is.

[Tom]

Are you querying some foreign database?  Show the LOG for the full step.

Because it works fine with plain old SAS datasets.

710  proc sql ;
711  create table want as
712  select * from sashelp.class
713  union all
714  select * from sashelp.class
715  ;
NOTE: Table WORK.WANT created, with 38 rows and 5 columns.

716  quit;

[user34567]

Yes I'm connecting to Teradata. This is the full script:

PROC SQL;

CONNECT TO teradata AS td (&teradata_connect_string.);

EXECUTE

( 

INSERT INTO "Table"."UREN"

(    columnA,

    columnB,

    columnC,

    columnD,

    columnE,

    columnF,

    columnG,

    columnH,

    columnI,

    columnJ,

    columnK)

SELECT * FROM "Table"."UREN_2021"

UNION ALL

SELECT * FROM "Table"."UREN_2022"

UNION ALL

SELECT * FROM "Table"."UREN_2023"

) BY td;

QUIT:

The LOG:

27         PROC SQL;
28         CONNECT TO teradata AS td
SYMBOLGEN:  Macro variable TERADATA_CONNECT_STRING resolves to SERVER="td" AUTHDOMAIN=TeradataAuth  TPT=yes SQL_FUNCTIONS=all 
            CONNECTION=global BULKLOAD=yes mode=teradata
28       !                           (&teradata_connect_string.);
NOTE:  Credential obtained from SAS metadata server.
29         
30         EXECUTE
31         (
32         	
33          INSERT INTO "Table"."UREN"
34         	(
35             columnA,
36             columnB,
37             columnC,
38             columnD,
39             columnE,
40             columnF,
41             columnG,
42             columnH,
43             columnI,
44             columnJ,
45             columnK)
46         
47         SELECT * FROM "TABLE"."UREN_2021"
48         
49         UNION ALL
2                                                          The SAS System                      Monday, December  4, 2023 08:22:00 AM

50         
51         SELECT * FROM "TABLE"."UREN_2022"
52         
53         UNION ALL
54         
55         SELECT * FROM "TABLE"."UREN_2023"
56         
57         ) BY td;
 
TERADATA_629: Executed: on connection 0
INSERT INTO "Table"."UREN" ( columnA, columnB, columnC, columnD, columnE, columnF, columnG, 
columnH, columnI, columnJ, columnK) SELECT * FROM "Table"."UREN_2021" UNION ALL SELECT * FROM 
"Table"."UREN_2022" UNION ALL SELECT * FROM "Table"."UREN_2023"
 
TERADATA: 2032 row(s) inserted/updated/deleted.
 
Summary Statistics for TERADATA are:
Total SQL execution seconds were:                   0.095859
Total seconds used by the TERADATA ACCESS engine were     0.096102
 
58         QUIT:
59         
60         %LET _CLIENTTASKLABEL=;
61         %LET _CLIENTPROCESSFLOWNAME=;
62         %LET _CLIENTPROJECTPATH=;
63         %LET _CLIENTPROJECTPATHHOST=;
64         %LET _CLIENTPROJECTNAME=;
65         %LET _SASPROGRAMFILE=;
66         %LET _SASPROGRAMFILEHOST=;
67         
68         ;*';*";*/;quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.11 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              380.43k
      OS Memory           54408.00k
      Timestamp           12/04/2023 03:53:21 PM
      Step Count                        326  Switch Count  18
      Page Faults                       0
      Page Reclaims                     29
      Page Swaps                        0
      Voluntary Context Switches        131
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

68       !                run;
69         ODS _ALL_ CLOSE;
70         
71         
72         QUIT; RUN;
73         

In the log you can see that 2032 rows were inserted. I Expected 2032*3 = 6096 rows to be inserted since the tables are identical.

Also when I run an UNION ALL in Teradata directly, I do get a count of 6096 rows.

[Sajid01]

Hello @user34567 
You want to insert the result set of the union all into the table UREN.
Ensure that all the statements of the union all  return the same number of columns i.e, the column you want to insert. Sample insert statement is given below

insert into Result_table (A,B) 
select A, B from Table1
union all
select A, B from Table2
union all
select A, B from Table3

[user34567 · Accepted solution]

I found the issue. Apparently you can have a "SET" or "MULTISET" table in Teradata. A table defined as SET table doesn't store the duplicate records, whereas the MULTISET table can store duplicate records.

So I changed the table to MULTISET and now it is solved.

Thanks for your help @Tom and @Sajid01 🙂

[SASKiwi]

@user34567 - Please update your post as answered in that case.