topic Re: how do i concatenate multiple tables in proc sql in SAS Programming

how do i concatenate multiple tables in proc sql

ajb — Fri, 05 Apr 2019 13:19:43 GMT

Hello,

I am very new to proc sql and currently trying to concatenate several very large tables.

I used the code:

proc sql;
create table inptadm as
select * from raw.inptadm2002
union
select * from raw.inptadm2003
union
select * from raw.inptadm2004
union
select * from raw.inptadm2005
union
select * from raw.inptadm2006
union
select * from raw.inptadm2007
union
select * from raw.inptadm2008
union
select * from raw.inptadm2009
union
select * from raw.inptadm2010
union
select * from raw.inptadm2011
union
select * from raw.inptadm2012
union
select * from raw.inptadm2013
union
select * from raw.inptadm2014
;
quit;

and examples of my tables look similar to these below

raw.inptadm2002
var1	var2	var3	var4
a	c	d	u
s	u	d	l
e	g	w	b

raw.inptadm2003
var1	var2	var3	var4
d	g	i	k
w	f	h	k
w	g	r	b

…

raw.inptadm2014
var1	var2	var3	var4
e	h	l	u
g	u	r	l
e	w	w	k

I want a new table like this:

var1	var2	var3	var4
a	c	d	u
s	u	d	l
e	g	w	b
d	g	i	k
w	f	h	k
w	g	r	b
…
e	h	l	u
g	u	r	l
e	w	w	k

I keep getting this error codes in my log similar to

WARNING: A table has been extended with null columns to perform the UNION set operation.

ERROR: Column 13 from the first contributor of UNION is not the same type as its counterpart from the
second.
ERROR: Column 15 from the first contributor of UNION is not the same type as its counterpart from the
second.
ERROR: Column 17 from the first contributor of UNION is not the same type as its counterpart from the
second.

and I do not know what I am doing wrong.

I ran code similar to this in the past with no issue so I am not sure how to troubleshoot this problem.

______________________

Also, when I try to list the specific variables that I want in each table using the following code

proc sql;
create table enrl as
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2002
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2003
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2004
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2005
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2006
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2007
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2008
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2009
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2010
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2011
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2012
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2013
union
select enrolid, dtstart, dtend, AGE, AGEGRP, DOBYR, ENRMON, MSA, REGION, SEX, state from enroll2014
;
quit;

I also get these error messages:

ERROR: Ambiguous reference, column AGE is in more than one table.
ERROR: Ambiguous reference, column AGEGRP is in more than one table.
ERROR: Ambiguous reference, column DOBYR is in more than one table.
ERROR: Ambiguous reference, column ENRMON is in more than one table.
ERROR: Ambiguous reference, column MSA is in more than one table.
ERROR: Ambiguous reference, column REGION is in more than one table.
ERROR: Ambiguous reference, column SEX is in more than one table.
ERROR: Ambiguous reference, column state is in more than one table.

Re: how do i concatenate multiple tables in proc sql

PeterClemmensen — Fri, 05 Apr 2019 13:18:59 GMT

Does it have to be PROC SQL? Why not simplify with the data step like this

data inptadm2002;
input (var1-var4)($);
datalines;
a	c	d	u
s	u	d	l
e	g	w	b
;

data inptadm2003;
input (var1-var4)($);
datalines;
d	g	i	k
w	f	h	k
w	g	r	b
;

data inptadm2014;
input (var1-var4)($);
datalines;
e	h	l	u
g	u	r	l
e	w	w	k
;

data want;
   set inptadm:;
run;

Re: how do i concatenate multiple tables in proc sql

Tom — Fri, 05 Apr 2019 13:36:59 GMT

You need to fix the variable matching issue.

If it is just that the columns are in different orders then add the CORRESPONDING keyword after the UNION keyword.

1939  proc sql;
1940  create table x as
1941  select name,age from sashelp.class
1942  union
1943  select age,name from sashelp.class
1944  ;
ERROR: Column 1 from the first contributor of UNION is not the same type as its counterpart from the second.
ERROR: Column 2 from the first contributor of UNION is not the same type as its counterpart from the second.
1945  create table x as
1946  select name,age from sashelp.class
1947  union corresponding
1948  select age,name from sashelp.class
1949  ;
NOTE: Table WORK.X created, with 19 rows and 2 columns.

Re: how do i concatenate multiple tables in proc sql

Kurt_Bremser — Fri, 05 Apr 2019 13:58:19 GMT

Use a data step. SQL is probably the worst tool for such actions.

Re: how do i concatenate multiple tables in proc sql

ajb — Tue, 09 Apr 2019 05:35:40 GMT

Thank you so much!! This fixed the problem

Re: how do i concatenate multiple tables in proc sql

ajb — Tue, 09 Apr 2019 05:38:46 GMT

I know. Doing it in a data step would be so much easier but I have to learn to do it in sql as well 😞

Re: how do i concatenate multiple tables in proc sql

Kurt_Bremser — Tue, 09 Apr 2019 05:51:43 GMT

Think of automating your code creation:

data _null_;
length
  selectstring $1000
  single_select $50
;
do year = 2002 to 2014;
  single_select = 'select * from raw.inptadm' || put(year,z4.);
  selectstring = catx(' union corr ',selectstring,single_select);
end;
call symputx('selectstring',selectstring);
run;

proc sql;
create table inptadm as
&selectstring
;
quit;