Check in a data step if a table exists to create another from it

[Abelp9]

Hello, I have two different tables, one is called ST.TABLE1 and the other is called ST.TABLE2, I have executed this code so that when the ST.TABLE1 does not exist, it creates the OUTPUT table based on the ST.TABLE2 table.

However, it gives me an error because it keeps looking for ST.TABLE1, what is wrong with my code? Can someone help me or show me another way to do it?

Thank you!

%let existe_tabla = 0;



data output;
if (&existe_tabla. = 0) then do;
set ST.TABLE1;
end;
else do;
set ST.TABLE2;
end;
run;

I have also tried this and it returns the same error

data output;
   if exist('ST.TABLE1') then do;
      set ST.TABLE1;
   end;
   else do;
      set ST.TABLE2;
   end;
run;

The error:

ERROR: The file ST.TABLE1.DATA does not exist.

[PaigeMiller]

You are correct to use the EXIST function. However, your logic is wrong.

See Example 1 and Example 2 in the documentation.

%if %sysfunc(exist(st.table1)) %then %do;
    data output;
        set st.table1;
    run;
%end;
%else %do;
    data output;
        set st.table2;
    run;
%end;

[Abelp9]

That example does not work for me, I have simplified what I want to do but I really want to check if there are 5 tables to create the output table. It would be something like checking the existence of the tables tableA1, tableB1, tableC1, tableD1 and tableE1 and if any of them does not exist, take tableA2, B2, C2, D2 or E2, but give priority to if there is 1, which create the output with the existing ones ending in 1.

So, I don't see the way to achieve this within a macro, I would have to do all the possible combinations and it would be a very long code, don't you think?

Thank you very much for your answer

[PaigeMiller]

---

@Abelp9 wrote:

That example does not work for me

---

Please don't every say something doesn't work, and then not explain further. Show us the code you tried. Show us the log. Never say "it doesn't work" without showing us code and log.

Please don't simplify the example so much that we give an answer that doesn't apply to the real problem.

[Abelp9]

Ok, sorry, I start from this idea in which I join 5 tables in another called output, they are 5 different tables but they all correspond to the month of February:

data OUTPUT;
	set ST.TABLEA1
	 ST.TABLEB1
	 ST.TABLEC1
	 ST.TABLED1
    ST.TABLEF1; 
run;

So, I want to add a condition that checks if these tables exist, and if any of them don't exist, it takes the table from the previous month (from the one that doesn't exist ending in 1 that it takes the one that ends in 2)

And I've started testing with this:

data OUTPUT;
   if exist('ST.TABLEA1') then do;
      set ST.TABLEA1;
   end;
   else do;
      set ST.TABLEA2;
   end;
run;

And it returns this error "ERROR: The file ST.TABLEA1.DATA does not exist."

I would like to leave it something like this, but these conditionals inside a data step don't work for me

data OUTPUT;
   if exist('ST.TABLEA1') then do;set ST.TABLEA1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEB1') then do;set ST.TABLEB1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEC1') then do;set ST.TABLEC1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLED1') then do;set ST.TABLED1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEF1') then do;set ST.TABLEF1;end;else do;set ST.TABLEA2;end;
run;

[Quentin]

In your real problem, do you only have two versions (months) of each table, or could you need to check three or four?  

That is, can you assume that TableA2 always exists, or do you need to check for TableA3 if Table1 and TableA2 don't exist, etc.?

All of these datasets have the same variables, right?  

While you could do this by using the macro language to build the list of datasets for the SET statement, you might be better off just concatenating all of your datasets into one dataset, and then subset the data.  

[Quentin]

This code:

---

I would like to leave it something like this, but these conditionals inside a data step don't work for me

data OUTPUT;
   if exist('ST.TABLEA1') then do;set ST.TABLEA1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEB1') then do;set ST.TABLEB1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEC1') then do;set ST.TABLEC1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLED1') then do;set ST.TABLED1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEF1') then do;set ST.TABLEF1;end;else do;set ST.TABLEA2;end;
run;

---

doesn't work because of a DATA step timing issue.  When the DATA step compiles, the SET statement does some of its work, in order to create the program data vector.  You can't use an IF statement (which works at execution time), to control the SET statement (which works at compile time).

One way to approach this is with the macro language. The macro language executes before the DATA step is compiled.  So the macro language can be used to generate DATA step code, like a SET statement.

Here is an example of a macro that could could be used for your problem.  It is a function-style macro.  It takes a list of datasets as an argument, and returns the name of the first dataset that exists.

%macro FirstExistingDataset(data=) ;
  %*Iterate through a space-delimited list of dataset names ;
  %*Return the name of the first dataset that exists ;

  %local i data_i ;                         
  %do i=1 %to %sysfunc(countw(&data,%str( ))) ;
    %let data_i=%scan(&data,&i,%str( )) ;

    %if %sysfunc(exist(&data_i)) %then %do ;
      &data_i /*return the name of the first dataset in the list that exists*/
      %return ;
    %end ;

  %end ;
%mend ;

You can test this macro with code like:

%put %FirstExistingDataset(data=nope1 sashelp.class sashelp.shoes ) ; %*returns sashelp.class ;
%put %FirstExistingDataset(data=nope1 nope2 sashelp.shoes ) ;         %*returns sashelp.shoes ;
%put %FirstExistingDataset(data=nope1 nope2) ;                        %*returns nothing ;

Note in order to concatenate datasets, you want one SET statement, with a list of datasets.  You could generated your desired dataset like:

data want;
  set 
    %FirstExistingDataset(data=ST.TABLEA1 ST.TABLEA2)  
    %FirstExistingDataset(data=ST.TABLEB1 ST.TABLEB2)  
    %FirstExistingDataset(data=ST.TABLEC1 ST.TABLEC2)  
  ;
run;

[ballardw]

---

@Abelp9 wrote:

Ok, sorry, I start from this idea in which I join 5 tables in another called output, they are 5 different tables but they all correspond to the month of February:

data OUTPUT;
	set ST.TABLEA1
	 ST.TABLEB1
	 ST.TABLEC1
	 ST.TABLED1
    ST.TABLEF1; 
run;

So, I want to add a condition that checks if these tables exist, and if any of them don't exist, it takes the table from the previous month (from the one that doesn't exist ending in 1 that it takes the one that ends in 2)

And I've started testing with this:

data OUTPUT;
   if exist('ST.TABLEA1') then do;
      set ST.TABLEA1;
   end;
   else do;
      set ST.TABLEA2;
   end;
run;

And it returns this error "ERROR: The file ST.TABLEA1.DATA does not exist."

I would like to leave it something like this, but these conditionals inside a data step don't work for me

data OUTPUT;
   if exist('ST.TABLEA1') then do;set ST.TABLEA1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEB1') then do;set ST.TABLEB1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEC1') then do;set ST.TABLEC1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLED1') then do;set ST.TABLED1;end;else do;set ST.TABLEA2;end;
   if exist('ST.TABLEF1') then do;set ST.TABLEF1;end;else do;set ST.TABLEA2;end;
run;

---

If your data sets may have differing numbers of observations then you want to be very careful about involving multiple SET statements as they may behave quite differently than you expect. A brief example;

data one;
   input x;
datalines;
1
2
3
4
;

data two;
   input y;
datalines;
11
22
33
;

data example;
  set one;
  set two;
run;

Set One above has 4 observations, set Two has 3 observations. Before running code guess how many observations will be in the output?

If you guess anything other than 3 you have a problem. And if ANY of those data sets have the same variables you may have problems with the actual values in the result compared with what you expect/need.