topic Re: one file or more of union not there in SAS Programming

one file or more of union not there

Hoibai — Mon, 10 Feb 2025 07:28:24 GMT

How can I intercept when a file is not there? And then the processing union continues. Thanks.

proc sql;
create table union_test as
select * from Test_A
union all
select * from Test_B
union all
select * from Test_C;

quit;

Re: one file or more of union not there

Ksharp — Mon, 10 Feb 2025 07:45:47 GMT

You could use SET statement to repalce UNION ALL of SQL.

options nodsnferr;
data Test_A Test_B;
 set sashelp.class;
run;


data union_test;
 set  Test_A Test_B Test_C;
run;

Re: one file or more of union not there

Hoibai — Mon, 10 Feb 2025 08:24:40 GMT

How can I intercept the file if it is not there?

Re: one file or more of union not there

FreelanceReinh — Mon, 10 Feb 2025 08:54:08 GMT

Hello @Hoibai,

If you must use PROC SQL, check the existence of the datasets by querying DICTIONARY.TABLES:

proc sql noprint feedback;
select memname into :dsnames separated by ' union all select * from '
  from dictionary.tables
  where libname='WORK' & memname in ('TEST_A', 'TEST_B', 'TEST_C'); /* names in upper case! */
%if &sqlobs %then %do;
  create table union_test as
  select * from &dsnames;
%end;
quit;

The %IF condition (allowed in open code) prevents an error in the case that none of the datasets exists.

Re: one file or more of union not there

Ksharp — Mon, 10 Feb 2025 09:00:51 GMT

If you want to know which dataset is not exist, then check dictionary table as FreelanceReinh showed .

Re: one file or more of union not there

Tom — Mon, 10 Feb 2025 15:59:18 GMT

You can use the NODSNFERR setting to suppress errors when datasets referenced do not exist.

Will work if you just use normal SAS code to concatenate your datasets.

Example:

data test_a test_c;
  set sashelp.class;
run;

options nodsnferr;
data want ;
  set test_a test_b test_c;
run;
options dsnferr;