Hello everyone,
In a proc sql, I would like to test the existence of a table. This is my code (which doesn't work and i have no idea how to do it) :
proc sql ;
create table union as
if exist(table1) then (
select id1, . as id2, number
from table1
)
union
if exist(table2) then (
select '' as id1, id2, number
from table2
)
; quit ;
I have seen on the internet something like this, but i don't know how to put it in my case :
%macro test(name);
%if %sysfunc(exist(&name)) %then
/* union of the 2 tables */
;
%mend test;
Thank you in advance, and sorry for my poor english 😕 !
Have a nice afternoon !
Alison
Yes, what you have posted is not SQL at all. SQL is a language which assumes you know what data you are dealing with and so does not have any existence checking. Why do you not know if your datasets will exist or not, it doesn't make much sense. At worst case scenario create an empty dataset, then append your data to that:
proc sql; create table FINAL (VAR1 char(200),VAR2 num); quit; data final; set final your_data; run;
You know then that the dataset will always exist even if there is no data. Sounds to me like your process before that is not optimal hence you have this issue.
Note, you could create some macro language code to do such a thing, but the question remains why, fix the source.
%macro sql; proc sql; create table UNION as %if %sysfunc(exists(table1)) %then %do; select ID1,. as ID2 from TABLE1 %end; %if %sysfunc(exists(table1)) and %sysfunc(exists(table2)) %then %do; union all %end; %if %sysfunc(exists(table2)) %then %do; select ID1,ID2 from TABLE2 %end; ;quit; %mend sql; %sql;
You can see from the above what a mess that looks like.
Yes, what you have posted is not SQL at all. SQL is a language which assumes you know what data you are dealing with and so does not have any existence checking. Why do you not know if your datasets will exist or not, it doesn't make much sense. At worst case scenario create an empty dataset, then append your data to that:
proc sql; create table FINAL (VAR1 char(200),VAR2 num); quit; data final; set final your_data; run;
You know then that the dataset will always exist even if there is no data. Sounds to me like your process before that is not optimal hence you have this issue.
Note, you could create some macro language code to do such a thing, but the question remains why, fix the source.
%macro sql; proc sql; create table UNION as %if %sysfunc(exists(table1)) %then %do; select ID1,. as ID2 from TABLE1 %end; %if %sysfunc(exists(table1)) and %sysfunc(exists(table2)) %then %do; union all %end; %if %sysfunc(exists(table2)) %then %do; select ID1,ID2 from TABLE2 %end; ;quit; %mend sql; %sql;
You can see from the above what a mess that looks like.
Thanks you for your quick answer ! That works very well, it's exactly what i want, even if it's " a mess" like you said ! My team wanted an automatization of a script, which work even if table1 or table2 doesn't exist.
Thanks you !
Have a nice day,
Alison
I also need this question answered as we set up tables on a quarterley basis that have data added to them. I would to verify if the table exists at the start of a new quarter and, if not, to create it.
This question was answered, 4 months ago. If your problem is different, start a new topic, provide full information as to the problem (i.e. where the data is), example data/output if useful.
Personally I avoid using this scenario where "data" - in this case quarters, is used in names of datasets and one of the resons behind that is to avoid this whole need to know issues. If you have a master data table, and update that, and it has quarter information, it is simple to code with, and you can pull out any required information.
%macro union_tables(tab_list= sashelp.class sashelp.class);
* get number of table in list;
%let Tab_cnt=%eval(%sysfunc(countc(strip(&tab_list), %str( )))+1);
* set first table flag to 0, we are setting it to 1 where table exist first time;
%let first_tab=0;
* Proc sql begins;
proc sql;
/*Loop throught list of tables*/
%do i=1 %to &Tab_cnt;
%let tab=%sysfunc(scan(&tab_list,&i,%str( )));
%if %sysfunc(exist(&tab)) %then %do; /*Check if table exist*/
%if &first_tab=0 %then %do; /*Check if this is first table that exist*/
%let first_tab=1; /*Set value flag to 1*/
create table out_tab as /*Add create tastemet for first table which exist*/
%end;
%else %do; union all %end; /*if this is second or other table that exists*/
select * from &tab /*Select statment*/
%end;
%end;
;
quit ;
%mend;
options mlogic symbolgen mprint;
%union_tables(tab_list= sashelp.class sashelp.class)
Thanks you for your answer ! In my case it doesn't fit really well because of my "select" which is different given the table, but i'm going to use it in an other work. Thanks you very much !
Have a nice day !
Alison
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.