check if table exists & create if not

[me55]

okay, there is a small thing i am missing here and i think it has to do with the other directory.  it is not doing that...the code is wrong there.  so what i am trying to do is check if the table exists.  the table resides in another directory other than work, in this case, '/sas/dir_01/dir_02/table_&numshortdate' and the numshortdate is a variable that equates to YYYYMM.  so if a table with last months date does exist, it copies it to my work directory.  if it doesnt exist, it creates one with this months date.  simple but i am just missing something about how this is working...

%let dsname=/sas/dir_01/dir_02/table_&numshortdate;

%macro opends(name);

%if %sysfunc(exist(&name)) %then %do;

              libname LIB01 '/sas/dir_01/dir_02/';

              proc copy in=LIB01 out=WORK;

              select table_&numshortdate;

              run;

%end;

%else %do;

              proc sql;

              create table table_&lnumshortdate (

                             YR_MNTH char(6),

                             Col_01 decimal(10,2) format=10.2 informat=10.2,

                             Col_02 decimal(10,2) format=10.2 informat=10.2,

                             Col_03 decimal(10,2) format=10.2 informat=10.2,

                             Col_04 decimal(10,2) format=10.2 informat=10.2);

              quit;

%end;

%mend opends;

%opends(&dsname);

[PaigeMiller]

The EXIST function checks to see if a SAS data set exists, which doesn't seem to be what you are checking.

If you want to test to see if a file exists out on the server or hard disk somewhere, you would use FEXIST() or FILEEXIST()

[LeonidBatkhan · Accepted solution]

Hi me55,

I think you are mixing file name and table name. For files you use FILEEXIST function or FEXIST function,  for table names you use EXIST function.

You don't need your statement %let dsname=/sas/dir_01/dir_02/table_&numshortdate;

You need to define libname and have table name:

libname mylib '/sas/dir_01/dir_02';

Then you invoke your macro as

%opends(mylib.table_&numshortdate);

I assume your table is named table_&numshortdate. If not just use SAS table name (without file extension). Hope this helps.

[me55]

okay, that worked.  that was the issue.  i have used that first code before to check if a table in work existed and if not create it but not to check another directory.  i figured it was going to be that but i wasn't sure where to set the lib up for sure.  

[Quentin]

Can you describe what is going wrong? Do you get an error, or wrong result?  Is it that the EXIST() is always returning false?

It looks like you're passing a full file path and filename to exist, e.g.:

%if %sysfunc(exist(/sas/dir_01/dir_02/table_202005)) %then %do;

I don't think that will work.  Exist wants the name of a data set (or catalog or whatever) or in a defined sas library.  So if you have a library MyData pointing /sas/dir_01/dir_02/, then you could use exist like:

%if %sysfunc(exist(mydata.table_202005)) %then %do;

If that doesn't help, I would try debugging with a some %PUT statements in the macro, e.g.:

%put  Test whether &name exists returns: %sysfunc(exist(&name)) ;

[me55]

it never finds that the first condition is true even though the table indeed exists. so it always fails and creates the table. i used one of the above solutions, basically i wasn't using the directory right. i fixed it with the accepted solution and it worked...

[s_lassen]

Your macro needs a bit of cleaning up. 

First, the parameters: you give the macro a filename without extension as a parameter, but also refer to the NUMSHORTDATE global macro variable in the macro. As this is a very specific macro for a certain type of table, I think you would be better off just using the date as the parameter. Possibly, you will want to have the library path as a second parameter.

Second: as others have pointed out, the usage of the EXIST function against a filename, not a SAS table name. You could change the function to FILEEXIST, but then you would have to add an extension to the filename. Better stick with the EXIST function, but use it correctly.

Third: Just a spelling error (I think):

create table table_&lnumshortdate

here, you refer to the date as LNUMSHORTDATE, not NUMSHORTDATE.

I assume the macro you want is something like this:

%macro opends(numshortdate,dir=/sas/dir_01/dir_02/);
  libname lib01 "&dir";
  %if %sysfunc(exist(lib1.table_&numshortdate)) %then %do;
    proc copy in=LIB01 out=WORK;
      select table_&numshortdate;
    run;
    %end;
  %else %do;
    proc sql;
      create table table_&numshortdate (
            YR_MNTH char(6),
            Col_01 decimal(10,2) format=10.2 informat=10.2,
            Col_02 decimal(10,2) format=10.2 informat=10.2,
            Col_03 decimal(10,2) format=10.2 informat=10.2,
            Col_04 decimal(10,2) format=10.2 informat=10.2);
    quit;
    %end;
%mend opends;

%opends(&numshortdate);

[Tom]

Not sure why you are trying to get SAS to create a DECIMAL() type variable. SAS only has floating point numbers.  Also why would you attach an informat with a decimal width specified to a variable?  Are you planning to read values into it where the decimal point is implied and you want SAS to divide the value provide by 100?