BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alisondu77
Obsidian | Level 7

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

alisondu77
Obsidian | Level 7

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

mbsuther
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

umeshMahajan
Fluorite | Level 6
%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)

 

alisondu77
Obsidian | Level 7

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

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 33260 views
  • 4 likes
  • 4 in conversation