topic Re: get only existing tables in SAS Programming

get only existing tables

Nasser_DRMCP — Tue, 12 Jan 2021 11:57:23 GMT

Hello,

I have a macro varaible that contains the texte : table1 table2 table3 table4 table5 table6

only table1 and table3 exists in the work librarie

So i would like to get in a macro variable the text table1 table3

thanks in advance for your help

Nasser

Re: get only existing tables

ballardw — Tue, 12 Jan 2021 12:06:13 GMT

Are those the exact names involved or just generic?

I ask because there are techniques that will work easier with an iterated name than with a list of explicit names.

You may need to show us how you are currently creating the macro variable.

One approach looks like this:

proc sql;
   select memname into: newlist separated by ' '
   from dictionary.tables
   where libname='WORK'
      and memname in ('TABLE1' 'TABLE2' 'TABLE3')
   ;
quit;

But that would require a quoted list of values. Which likely means doing something with your current value.

Which may require creating a helper macro to quote your existing value or make separately.

Re: get only existing tables

PeterClemmensen — Tue, 12 Jan 2021 12:10:23 GMT

This seems to be part of a bigger problem. What do you want to do with the new macro variable?

Re: get only existing tables

Kurt_Bremser — Tue, 12 Jan 2021 12:29:39 GMT

Store such data in datasets instead of macro variables, and you can use a simple join:

data tables;
input dname $32.;
datalines;
table1
table2
table3
table4
table5
;

proc sql;
create table exist as
  select dname
  from tables t, dictionary.tables d
  where upcase(t.dname) = d.memname and d.libname = "WORK"
;
quit;

Re: get only existing tables

RexDeus9 — Tue, 12 Jan 2021 14:15:27 GMT

Hi,

If I interpret Nasser's question right, I think a blend of the first two answers works just fine:

data table1;name='table1';run;
data table2;name='table2';run;
data table3;name='table3';run;
data table4;name='table4';run;
data table5;name='table5';run;

proc sql;
  select memname into: newlist separated by ' '
  from dictionary.tables
  where memname in ('TABLE1' 'TABLE2' 'TABLE3') and libname = "WORK"
;quit;