BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
user34567
Fluorite | Level 6

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?
1 ACCEPTED SOLUTION

Accepted Solutions
user34567
Fluorite | Level 6

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 🙂

View solution in original post

6 REPLIES 6
Sajid01
Meteorite | Level 14

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

Tom
Super User Tom
Super User

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
Fluorite | Level 6

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
Meteorite | Level 14

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
Fluorite | Level 6

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
PROC Star

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 655 views
  • 5 likes
  • 4 in conversation