topic Re: Check if a table exist in PROC SQL in SAS Procedures

Check if a table exist in PROC SQL

alisondu77 — Wed, 25 May 2016 12:06:43 GMT

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

Re: Check if a table exist in PROC SQL

RW9 — Wed, 25 May 2016 12:15:45 GMT

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.

Re: Check if a table exist in PROC SQL

umeshMahajan — Wed, 25 May 2016 14:53:46 GMT

%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)

Re: Check if a table exist in PROC SQL

alisondu77 — Mon, 30 May 2016 07:06:44 GMT

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

Re: Check if a table exist in PROC SQL

alisondu77 — Mon, 30 May 2016 07:09:27 GMT

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

Re: Check if a table exist in PROC SQL

mbsuther — Tue, 13 Sep 2016 12:37:56 GMT

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.

Re: Check if a table exist in PROC SQL

RW9 — Tue, 13 Sep 2016 12:44:26 GMT

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.