User-defined formats and Enterprise Guide : Strange behavior

[tlt]

Hi everybody,

Were are using SAS 9.04.01M5P091317 + SEGuide 7.15 HF8 (7.100.5.6214) (32 bits)

User-defined formats sometimes surprise me

Here's an example

1. A simple format, french style
   proc format; value $ sexf 'M'='Masculin' 'F'='Féminin' other='NSP' ; run;
2. Attach the format to variable SEX in a view on SASHELP.CLASS (my favorite)
   proc sql;
     create view class as select NAME, SEX format=$SEXF., AGE, HEIGHT, WEIGHT from sashelp.class;
   quit;
3. A PROC FREQ on the variable
   proc freq data=class; tables SEX; run;
4. Re-define the format, US style
   proc format; value $ sexf 'M'='Male' 'F'='Female' other='Unknown' ; run;
5. Another freq
   proc freq data=class; tables SEX; run;

The output shows the 2 versions as expected.

But if I execute steps 1+2, open the view (which shows the french labels), then execute 3+4+5, output shows twice the same version, french style.

Same phenomena if I use table, with rebuilding, instead of views.

Any hint would be highly appreciated.

Thien

[Tom]

Let me re-state the problem to make sure I understand.

You are using some Enterprise Guide feature to let you browse the contents of a dataset instead of looking at the output of a report.  And it is able to show you the decoded (formatted) value of your variable.  You then ran some SAS code that changed the meaning of the format in your SAS session.   Now the tool in Enterprise Guide is still using the old definition of the format.

Did you close that viewer tool and re-open it after you changed the definition of the format in your SAS session?

Did you try starting your SAS session and then changing the format definition (or creating for the first time if not permanent) and then re-opening the Enterprise Guide browser tool?

Did you try closing Enterprise Guide and starting it again?  If you just define the new format does it find it?

[tlt]

Hi Tom,

Thanks for your comments.

Yes, your re-statement is correct.

At our shop, users don't have access to old standard SAS, but just SAS Enterprise Guide, which have built-in capacities to open tables ans views.

Yes, I did close and re-open the viewer, but it's the first defined format that is used, even if the log shows when we ran another proc format to redefines the former

NOTE: Format $SEXF is already on the library WORK.FORMATS.
NOTE: Format $SEXF has been output.

proc format cntlout=sexf; select $sexf; run;

proc print; run;

show the correct formatting with english labels

still proc freq show the french version

If you close Enterprise Guide and start it again, then it would be a new sas session and same thing happens.

thien

[tlt]

Yes, I did close and re-open the viewer, but it's the first defined format that is used, even if the log shows when we ran another proc format to redefines the former

NOTE: Format $SEXF is already on the library WORK.FORMATS.
NOTE: Format $SEXF has been output.

proc format cntlout=sexf; select $sexf; run;

proc print; run;

show the correct formatting with english labels

still proc freq show the french version

If you close Enterprise Guide and start it again, then it would be a new sas session and same thing happens.

thien

[Tom]

Sounds like a limitation of EG.

What version of EG are you using? What version of SAS?

Why not just use different names for the different formats?

[tlt]

Were are using SAS 9.04.01M5P091317 + SEGuide 7.15 HF8 (7.100.5.6214) (32 bits)

View with format redefinition allow dynamic results with the same procedure.

If we don't open the view/table between redefintions of the format, it's OK.

[FreelanceReinh]

Hi @tlt,

I was able to replicate the phenomenon in SAS 9.4M5 (not using EG) with the ViewTable window. It appears that the format definition is kept in memory as long as the ViewTable using it is open.

Here's the code I used:

proc format; value $sexf 'M'='Male' 'F'='Female' other='Unknown'; run;

data class;
set sashelp.class;
format sex $sexf.;
run;

dm 'vt class browse'; /* opens CLASS in ViewTable window (browse mode is optional) */

proc catalog c=formats;
delete sexf / et=formatc;
quit; /* Deletion successful, but ... */

data test;
x='M';
format x $sexf.; /* ... the format is still available ... */
proc print;
run; /* ... and applied as usual -- thanks to the open ViewTable window. */

However, once I close the ViewTable window, repeating the last PROC PRINT step fails with the usual error message about the format not being found. Before that, the log was clean.

[tlt]

Hi Dear Respected Advisor,

Thanks for your hint.

DM is not allowed in our environnment, so I cannot launch view table explicitly.

By default, NOFMTERROR is set here.

So, I set it to FMTERR, define the format, defines the view, open it (OK), delete the format, issue a proc format cntlout just to be sure the format is not found, open the view again ===> NO ERROR, issue the FREQ ===> NO ERROR

My guess is that's probably SAS Enterprise Guide is using view table, launch it when I click on my view and does not end it even if I close the view.

Thien

[tlt · Accepted solution]

From support@sas.com

Problem Note 65104: Labels might be incorrect when a custom format is created and updated in the same session and used in a view definition
http://support.sas.com/kb/65104