If I were to run the following code:
libname data1 'C:\mydata\data1';
libname data2 'C:\mydata\data2';
options fmtsearch=(data1.formats data2.formats);
then I can open any tables within the DATA1 or DATA2 libraries that have pre-defined formats assigned to variables.
However, if I were to later run a different program with the following code:
libname data3 'C:\mydata\data3';
options fmtsearch=(data3.formats);
and then attempt to go back to open a table from either DATA1 or DATA2, then I would get the following error message in a pop-up box:
"ERROR: Unable to open the table DATA1.TABLE1. Data1.Table1 cannot be opened. Do you want to select a different table to open?"
Is there a way to quickly see which library format catalogs are active without taking the time to open the desired table, get the error message, close the empty table that appears, resubmit the FMTSEARCH= options, and reopen the table? It would be much easier to view active library format catalogs, resubmit the FMTSEARCH= options if needed, and open the desired table. I hope this makes sense!
The approach I have used in the past involves looking at DICTIONARY.OPTIONS.
You may also want to use the system option NOFMTERR.
Below is a macro that I use to add or remove format catalogs to the the search path that will also generate the list of current format libraries.
/* FMTLIB is the either the library name containing the */
/* format catalog or the LIBNAME.CATALOG explicitly */
/* naming a format catalog to be added or removed from */
/* the current format search list. */
/* Tasks are AE= Add and end of search list
AB= Add at beginning of search list
R = Remove from search list
D = Display search list
FMTLIB is the name of a library to add/remove to the list
The macro does provide some warnings related to existence
of the format library if it is or is not in the search
list. Note, this does not remove a format library unless
specifically asked to do so. This means it is possible
to end up with the library in multiple places in the search
list depending upon how the list has been created. The
Remove option will remove ALL occurences of the library
from the list.
The options to add at the end or beginning is because
the FMTSEARCH is an order dependent option.
Left to the interested reader:
Insert into the search list. May want to insert before/
after another specifiec library/catalong
*/
/*** This could be used as a pattern to help manage SAS */
/*** autocall macro libraries as well using the option */
/*** OPTIONS SASAUTOS= instead of FMTSEARCH */
%macro FmtSearchAid (fmtlib=,task=AE);
%local fmtlib task myfmtsch;
%let fmtlib = %upcase(&fmtlib);
%let task=%upcase(&task);
/* if doing anything other than displaying the format seach list*/
/* need a format catalog to work with. */
proc sql noprint;
select setting into :myfmtsch
from DICTIONARY.OPTIONS
where optname='FMTSEARCH';
quit;
%if %isblank(&fmtlib)=1 %then %do;
%if &task ne D %then %do;
%put WARNING: FMTLIB option not set in Macro FmtSearchAid. ;
%put WARNING: FmtSearchAid canceled.;
%goto term;
/* and wouldn't it be nice to set a system error code?*/
%end;
%end;
%let myfmtsch = %trim(&myfmtsch);
%if &task=AE %then %do;
%let W= %sysfunc(findw(&myfmtsch,&fmtlib));
%IF &W>0 %then %put WARNING: &fmtlib already in FMTSEARCH list;
%else %do;
%let d= %sysfunc(findc("&myfmtsch",')'));
%let t= %eval(&d -2);
%let myfmtsch = %substr(&myfmtsch,1,&t) &fmtlib);
options fmtsearch= &myfmtsch;
%end;
%end;
%if &task=AB %then %do;
%let W= %sysfunc(findw(&myfmtsch,&fmtlib));
%IF &W>0 %then %put WARNING: &fmtlib already in FMTSEARCH list;
%else %do;
%let myfmtsch= (&fmtlib %substr(&myfmtsch, 2);
options fmtsearch= &myfmtsch;
%end;
%end;
%if &task=R %then %do;
%let d= %sysfunc(findw(&myfmtsch,&fmtlib));
%if &d=0 %then %put WARNING: Library &fmtlib not found in FMTSEARCH list.;
%else %do;
%let myfmtsch= %sysfunc(tranwrd(&myfmtsch,&fmtliB,));
%LET myfmtsch= %sysfunc(compbl(&myfmtsch));
options fmtsearch= &myfmtsch;
%end;
%end;
%if &task=D %then %do;
%put Format search string is : &myfmtsch;
%end;
%* this logic should have no, or undefined tasks, do nothing;
%term:
%mend;
The approach I have used in the past involves looking at DICTIONARY.OPTIONS.
You may also want to use the system option NOFMTERR.
Below is a macro that I use to add or remove format catalogs to the the search path that will also generate the list of current format libraries.
/* FMTLIB is the either the library name containing the */
/* format catalog or the LIBNAME.CATALOG explicitly */
/* naming a format catalog to be added or removed from */
/* the current format search list. */
/* Tasks are AE= Add and end of search list
AB= Add at beginning of search list
R = Remove from search list
D = Display search list
FMTLIB is the name of a library to add/remove to the list
The macro does provide some warnings related to existence
of the format library if it is or is not in the search
list. Note, this does not remove a format library unless
specifically asked to do so. This means it is possible
to end up with the library in multiple places in the search
list depending upon how the list has been created. The
Remove option will remove ALL occurences of the library
from the list.
The options to add at the end or beginning is because
the FMTSEARCH is an order dependent option.
Left to the interested reader:
Insert into the search list. May want to insert before/
after another specifiec library/catalong
*/
/*** This could be used as a pattern to help manage SAS */
/*** autocall macro libraries as well using the option */
/*** OPTIONS SASAUTOS= instead of FMTSEARCH */
%macro FmtSearchAid (fmtlib=,task=AE);
%local fmtlib task myfmtsch;
%let fmtlib = %upcase(&fmtlib);
%let task=%upcase(&task);
/* if doing anything other than displaying the format seach list*/
/* need a format catalog to work with. */
proc sql noprint;
select setting into :myfmtsch
from DICTIONARY.OPTIONS
where optname='FMTSEARCH';
quit;
%if %isblank(&fmtlib)=1 %then %do;
%if &task ne D %then %do;
%put WARNING: FMTLIB option not set in Macro FmtSearchAid. ;
%put WARNING: FmtSearchAid canceled.;
%goto term;
/* and wouldn't it be nice to set a system error code?*/
%end;
%end;
%let myfmtsch = %trim(&myfmtsch);
%if &task=AE %then %do;
%let W= %sysfunc(findw(&myfmtsch,&fmtlib));
%IF &W>0 %then %put WARNING: &fmtlib already in FMTSEARCH list;
%else %do;
%let d= %sysfunc(findc("&myfmtsch",')'));
%let t= %eval(&d -2);
%let myfmtsch = %substr(&myfmtsch,1,&t) &fmtlib);
options fmtsearch= &myfmtsch;
%end;
%end;
%if &task=AB %then %do;
%let W= %sysfunc(findw(&myfmtsch,&fmtlib));
%IF &W>0 %then %put WARNING: &fmtlib already in FMTSEARCH list;
%else %do;
%let myfmtsch= (&fmtlib %substr(&myfmtsch, 2);
options fmtsearch= &myfmtsch;
%end;
%end;
%if &task=R %then %do;
%let d= %sysfunc(findw(&myfmtsch,&fmtlib));
%if &d=0 %then %put WARNING: Library &fmtlib not found in FMTSEARCH list.;
%else %do;
%let myfmtsch= %sysfunc(tranwrd(&myfmtsch,&fmtliB,));
%LET myfmtsch= %sysfunc(compbl(&myfmtsch));
options fmtsearch= &myfmtsch;
%end;
%end;
%if &task=D %then %do;
%put Format search string is : &myfmtsch;
%end;
%* this logic should have no, or undefined tasks, do nothing;
%term:
%mend;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.