topic SQL Union all issue in SAS Programming

SQL Union all issue

Rahul_SAS — Thu, 20 Sep 2018 07:45:20 GMT

Hi Experts,

I am appending 4 tables with same variable names using SQL UNION and SET statement in datastep but its not giving me all the employee_names its missing few employees.

What i need is, getting all the records (for employee_name, sponsor, projectcode and employee_region) from all 4 tables.

please help me to resolve the issue and correct me if i missed something.

proc sql noprint;
create table TEMP as
select employee_name, sponsor, projectcode, employee_region from table1
union all
select employee_name, sponsor, projectcode, employee_region from table2

union all

select employee_name, sponsor, projectcode, employee_region from table3

union all

select employee_name, sponsor, projectcode, employee_region from table4t;

quit;

data test (keep= employee_name sponsor projectcode employee_region);
set table1 table2 table3 table4;
run;

data test (keep= employee_name sponsor projectcode employee_region);

set table1

set table2

set table3

set table4;
run;

Re: SQL Union all issue

RW9 — Thu, 20 Sep 2018 08:39:41 GMT

No test data, can't do anything. As for your question are all three methods returning the same amount of observations which is implied with "using SQL UNION and SET statement in datastep but its not giving me all the employee_names its missing few employees.". If so then you have what you should expect from the set, each of the datasets appended together which is what this does:

data test (keep= employee_name sponsor projectcode employee_region);
set table1
    table2
    table3
    table4;
run;

Do note however that you should only have one set statement followed by the datasets followed by a semicolon, otherwise you will get an error.

Re: SQL Union all issue

ballardw — Thu, 20 Sep 2018 15:37:45 GMT

I would expect this

data test (keep= employee_name sponsor projectcode employee_region);

set table1 

set table2 

set table3 

set table4;
run;

to have multiple errors about data set SET not found, unless you have created a data set named SET previously. In which case it would have been included 3 times