BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

1 REPLY 1
ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 829 views
  • 0 likes
  • 2 in conversation