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;
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;
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.
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)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Register now!
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
Arlington, VA
Cary, NC
View all other training opportunities.
