Help using Base SAS procedures

Check if a table exist in PROC SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Check if a table exist in PROC SQL

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

 


Accepted Solutions
Solution
‎05-30-2016 03:06 AM
Super User
Super User
Posts: 7,399

Re: Check if a table exist in PROC SQL

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.

View solution in original post


All Replies
Solution
‎05-30-2016 03:06 AM
Super User
Super User
Posts: 7,399

Re: Check if a table exist in PROC SQL

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.

Occasional Contributor
Posts: 15

Re: Check if a table exist in PROC SQL

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

Occasional Contributor
Posts: 13

Re: Check if a table exist in PROC SQL

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.

Super User
Super User
Posts: 7,399

Re: Check if a table exist in PROC SQL

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.

 

Occasional Contributor
Posts: 7

Re: Check if a table exist in PROC SQL

[ Edited ]
%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)

 

Occasional Contributor
Posts: 15

Re: Check if a table exist in PROC SQL

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

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 3732 views
  • 3 likes
  • 4 in conversation