topic User defined format values are not shown in output dataset? in SAS Programming

User defined format values are not shown in output dataset?

SuryaKiran — Wed, 19 Oct 2016 21:01:41 GMT

I was struck with this problem. Here is my code-->

proc format library=RMTWORK;

value $sex 'M'='Male'

'F'='Female'

;

Run;

options fmtsearch=RMTWORK;

data RMTWORK.one;

set sashelp.class;

format sex sex. ;

run;

When I open the created dataset and see it has 'M' and 'F' values but, It is supposed to have 'Male' and 'Female' in the output dataset.

When I run

Proc print data=rmtwork.one;

run;

It shows the formated values.

Why did the dataset 'one' didn't show the user defined format values.

I'm using SAS EG 5.1 running on grid(sas9.3).

Re: User defined format values are not shown in output dataset?

Shmuel — Wed, 19 Oct 2016 21:19:10 GMT

you missed a $ sign and maybe need add length to it:

data RMTWORK.one;

set sashelp.class;

format sex $sex6. ;

run;

Re: User defined format values are not shown in output dataset?

ChrisNZ — Wed, 19 Oct 2016 21:19:54 GMT

You must use parentheses.

options fmtsearch=(RMTWORK);

Doing this, it works for me.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Wed, 19 Oct 2016 21:51:48 GMT

Updated the code but, the problem is same

proc format library=RMTWORK;
value $sex 'M'='Male'
'F'='Female'
;
Run;

options fmtsearch=(RMTWORK);
data RMTWORK.one;
set sashelp.class;
format sex $sex6. ;
run;

Proc print data=rmtwork.one;
run;

Re: User defined format values are not shown in output dataset?

ChrisNZ — Wed, 19 Oct 2016 22:25:21 GMT

is RMTWORK on a remote server?

Re: User defined format values are not shown in output dataset?

SuryaKiran — Wed, 19 Oct 2016 22:27:24 GMT

Yes. Its on remote server.

Re: User defined format values are not shown in output dataset?

Kurt_Bremser — Thu, 20 Oct 2016 05:57:21 GMT

@SuryaKiran wrote:

Updated the code but, the problem is same

proc format library=RMTWORK;
value $sex 'M'='Male'
'F'='Female'
;
Run;

options fmtsearch=(RMTWORK);
data RMTWORK.one;
set sashelp.class;
format sex $sex6. ;
run;

Proc print data=rmtwork.one;
run;

Please post the complete log of this code piece.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Thu, 20 Oct 2016 12:48:30 GMT

Here is the complete log:

1 The SAS System 08:40 Thursday, October 20, 2016

1 %_eg_hidenotesandsource;
1528 %_eg_hidenotesandsource;
1534 %_eg_hidenotesandsource;
1550
1551
1552 proc format library=RMTWORK;
1553 value $sex 'M'='Male'
1554 'F'='Female'
1555 ;
2 The SAS System 08:40 Thursday, October 20, 2016

NOTE: Format $SEX is already on the library.
NOTE: Format $SEX has been written to RMTWORK.FORMATS.
1556 Run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

1557
1558 options fmtsearch=(RMTWORK);
1559 data RMTWORK.one;
1560 set sashelp.class;
1561 format sex $sex6. ;
1562 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set RMTWORK.ONE has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

1563
1564 Proc print data=rmtwork.one;
1565 run;

NOTE: There were 19 observations read from the data set RMTWORK.ONE.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

1566
1567 %_eg_hidenotesandsource;
1579
1580
1581 %_eg_hidenotesandsource;
5

Re: User defined format values are not shown in output dataset?

Kurt_Bremser — Thu, 20 Oct 2016 13:49:38 GMT

Hmm. Do a

libname rmtwork list;

and please post the NOTE: from the log.

But I just had another idea. Could it be that you have an identically named format elsewhere (especially in your WORK.FORMATS catalog) that overrides your newly created format?

Start a blank session, do not create the format, but use it. If that causes no error, you need to look where the other instance of the format is stored.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Thu, 20 Oct 2016 14:27:41 GMT

libname rmtwork list;

Here is my log:

1 The SAS System 08:40 Thursday, October 20, 2016

1          %_eg_hidenotesandsource;
1585 %_eg_hidenotesandsource;
1591 %_eg_hidenotesandsource;
1607
1608 libname rmtwork list;
NOTE: Libref=   RMTWORK
      Scope=    Program
      Access=   TEMP
      Levels=   1
        -Level 1-
      Engine=   V9
      Access=   TEMP
      Physical Name= /work/dev_work/SAS_work266802C0002A_p1sasgrid03
      Filename= /work/dev_work/SAS_work266802C0002A_p1sasgrid03
      Inode Number= 61542
      Access Permission= rwxr-xr-x
      Owner Name= suryakiran
      File Size (bytes)= 512
1609
1610 %_eg_hidenotesandsource;
1622
2                                                          The SAS System                           08:40 Thursday, October 20, 2016

1623
1624 %_eg_hidenotesandsource;
5

When I started with new session and used the format it showed error:

1543 data RMTWORK.one;
1544 set sashelp.class;
1545 format sex $sex. ;
-----
484
NOTE 484-185: Format $SEX was not found or could not be loaded.

Re: User defined format values are not shown in output dataset?

Kurt_Bremser — Fri, 21 Oct 2016 08:48:20 GMT

Now I'm positively stumped. Since not even one of the SAS people reading here has chimed in, I suggest putting this to SAS technical support. You might have uncovered a bug in the way the grid reads and applies formats.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Fri, 21 Oct 2016 14:14:15 GMT

Yes KurtBremser, It seems like a bug. I tried different ways to figure it out. But, everything failed. Check this code and output:

proc format library=RMTWORK;

value $sex 'M'='Male'

'F'='Female';

run;

options fmtsearch=(RMTWORK);

data RMTWORK.ONE;

set sashelp.class;

format sex $sex. ;

gender=put(sex,$sex.);

run;

Re: User defined format values are not shown in output dataset?

Tom — Fri, 21 Oct 2016 14:34:19 GMT

When I started with new session and used the format it showed error:

Sounds like you do not fully understand how formats work. When you permanently attach a format to variable, by using a FORMAT statement in a DATA step for example, then all it does it store the format name in the metadata of the dataset. It does not store the format defintion. Those live separately in a format catalog. When SAS wants to find a user defined format it searches the catalogs defined in the FMTSEARCH option.

So when you start a new session you need to make sure that in that new session you have set the FMTSEARCH option so that SAS can find the format that is attached to your variable.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Fri, 21 Oct 2016 15:07:48 GMT

Hi Tom,

I'm running my program in sas session. My problem is format is applied but it shows 'M' 'F' values in sex column instead of 'Male' 'Female'.

I tried the same code in different environment it worked. Even for ChrisNZ it worked but it did't work in my environment.

I want a dataset like this

But my dataset looks like this even after I applied the format.

Re: User defined format values are not shown in output dataset?

Tom — Fri, 21 Oct 2016 15:21:22 GMT

In the session where it doesn't work try testing the value of the FMTSEARCH option.

%put %sysfunc(getoption(fmtsearch));

And also test that it works for PROC PRINT or other SAS tool instead of EG tool.

If that shows proper search path such that your user defined format can be found and PROC PRINT does in fact find the format and use it, then if EG still does not show the format when browsing the data it sounds like an issue specific to EG. Perhaps you are using EG with a version of SAS that your EG version does not fully support?

You should rasie a ticket with SAS support and see if they can find out why it doesn't work.

Re: User defined format values are not shown in output dataset?

Kurt_Bremser — Fri, 21 Oct 2016 18:39:46 GMT

Your example where the format works in the put() function, but not in the immediately following viewtable is IMO convincing enough for a call to SAS TS.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Fri, 21 Oct 2016 19:28:30 GMT

I need to contact my SAS Admins for a fix to this problem.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Tue, 25 Oct 2016 18:19:05 GMT

Today my environment was changed from SAS EG 5.1(GRID SAS93) to SAS EG 7.1(GRID SAS94). I tried running the same code in my new environment. Suprisingly it gave the output I'm looking. Even I tried running the same code in different environments.
Code:

proc format ;

value $se 'M'='Male'

'F'='Female';

run;

data ONE;

set sashelp.class;

format sex $se. ;

gender=put(sex,$se.);

run;

Results:

SAS EG 5.1 and Grid on SAS 9.3

SAS EG 5.1 and Grid on SAS 9.4

SAS EG 7.1 and Grid on SAS 9.3

SAS EG 7.1 and Grid on SAS 9.4

Ohh! This is something interesting to find the reason for this.

Re: User defined format values are not shown in output dataset?

Tom — Tue, 25 Oct 2016 18:40:30 GMT

If you are trying to mix using 9.3 and 9.4 on the same sets of files you will have issues with format catalogs. In general SAS dataset will work ok across versions (on similar hardware), but format catalogs are version specific. So if you create a format catalog using SAS 9.4 and then tried to read it using SAS 9.3 it would not recognize the format catalog and hence not find your user defined format.

Re: User defined format values are not shown in output dataset?

SuryaKiran — Tue, 25 Oct 2016 18:54:10 GMT

Hi Tom,

I'm sure I didn't mix 9.3 and 9.4. I didn't have 9.4 earlier. I got it today only.