options nonotes; ... View more

In addition to using CPORT/CIMPORT or PROC DOWNLOAD/UPLOAD I usually recommend that you avoid this problem by always storing the source PROC FORMAT code with the format catalog. If you do not have the source code then you should use PROC FORMAT CNTLOUT= option to create a SAS dataset in addtion to the format catalog.  SAS datasets are much more portable than format catalogs. proc format lib=MYLIB.FORMATS cntlout=MYLIB.FORMATS noprint; run; ... View more

You can query metadata to determine the number of observations in a table.  You might want to watch out for deleted observations. select nobs-delobs into :nobs from dictionary.tables where libname='WORK' and memname='ONE' ; If you are creating a macro then you can use a %IF to generate the code. %if &nobs %then %do;   drop table work.one ; %end; If you are not then you could generate the code into a macro variable. %let drop=; select 'drop table ' || catx('.',libname,memname) into :drop separated by ';'   from dictionary.tables     where nobs-delobs=0      and libname='WORK' and memname in ('ONE','TWO') ; &drop; ... View more

Try changing the LIBNAME statement that you use when using the macros to include the ACCESS=READONLY message. Then if for some reason one of the macros was attempting to redefine a compiled macro it should complain about access and you might be able to trace the cause of your current error message. libname mymac 'g:\myproj\' access=readonly; ... View more

Oracle only has the DATETIME type.  So use datetime() function. Or other functions that generate datetime values like dhms(). ... View more

Did you try a datetime value?  '14NOV2011:00:00'dt ... View more

Since you are running the SQL in SAS try using SAS syntax for specifying a date literal. '14NOV2011'd ... View more

You will probably need to provide more details as to where exactly in the program it is getting the error. A couple of things to try. 1) Make sure that the catalog with the compiled macros is READONLY.  You can do this with the operating system but you can also use the access=readonly option on the libname that you are using to point to the folder with the catalog. 2) Make sure that the programs are not trying to change the options that specify which macro catalog to use. ... View more

Which queries are you referring to?  The first of the two in the response from FriedEgg requires the NOPRINT option to prevent SAS from writing results to the output destinations in addition to storing the values into the macro variables.  In the second query that is used to drop tables it does not matter whether the noprint option is used as no output will be generated by a drop table statement. ... View more

Pain in the neck isn't it?  SAS is compiling and executing that %LET statement on the local machine and then sending the generated code to the remote. Try wrapping the %LET inside of an %NRSTR() function. %nrstr(%let lmt = &sqlobs) ; Not sure if will help though as the %DO will not be able to run in open code in the remote submit block any way. It is as if you submitted this code: rsubmit ;   %do i=1 %to 10; ... %end; endrsubmit; You will get an error about trying to execute a %DO loop in open code. ... View more

Is it possible that you have some nesting of the macro definition?  Perhaps %CC is trying to redefine a macro that is already running. I would recommend separating your source code so that each macro is stored in a single file.  Each file shoud have only one macro definition in it and the first line should be the %MACRO statement and the last line the %MEND statement.  This will make it impossible to store all the files in one directory and have two different definitions for the same macro. Save the program that calls these macros and run it non-interactively and see if you get the error messages. Personally I normally just use autocall libraries rather than storing compiler macro definitions.  Then I only need to manage the source code files do not need to manage the compiler macro catalogs also. ... View more

The bounds for your inner loop should be J+1 and DIM(AM).  (or you could make the upper bound for your outer loop DIM(ADF)-2 ) do j=1 to dim(ADF);   ADF(j)= 'N';   do i=j+1 to min(J+2,dim(AM));     if AM(i)<9 then ADF(j)="Y";   end; end; ... View more