strange problem with simple proc format

[deleted_user]

I have a really strange problem using the just proc format. There is nothing wrong with the programming, since when changing the format, it does get assigned. The program is as follows:

proc format;
value LBWSAM01F
1 = '1 Pilot cohort'
2 = '2 Main cohort'
;
RUN;

DATA test2 ;
SET dataset;
FORMAT lbwsam01 lbwsam01F.;
RUN;

The way it is above does not assign the formats, however there is no error message in the log. when opening the datafile, there are no formats assigned and looking in the column attributes, it says that the format assigned is actually called lbwsam0114. (instead of lbwsam01F). If I do a proc tabulate with the same dataset, it uses the proper formats (?!?!?!?!) as if there were assigned! Isn't that strange? Now if I change the format name to lbwsam1f. or lbwsam0f. it assigns the formats as normal. Any ideas? I know that format names cannot end with a number that's why the letter F is attached...thanks for any hints.
Cheers, Melanie.

[ArtC]

Your code worked fine for me in both 9.1 and 9.2 (windows xp).
How did you check the attributes (proc contents, viewtable, columns window)? In your statement of the problem you say both that the format was not assigned, but it was also assigned incorrectly.

If you were checking the column attributes under the ViewTable was the format not actually shown as LBWSAM01F14.? The 14 is the width and is an artifact of the way the format was created.

[deleted_user]

I don't think there is anything wrong with the code.
I checked attributes with proc contents, that's where it lists that the format has been assigned and in the viewtable/columns window, where the format shows as LBWSAM0114. No F, I double checked again. I could send you the small program and the subset of the dataset via email. I'm using version 9.1 and windows xp.

[sbb]

Explain exactly what step(s) you performed to double-check, such as what is the outward symptom you observe that makes you believe the FORMAT is not being applied? Suggest you share some SAS-generated log information, PROC CONTENTS, PRINT, FREQ, or some other specific indication from your SAS session that a format is not applied. Also, is this across multiple SAS sessions? Remember, a SAS format written to WORK.FORMATS is only available during the life of your current SAS session, unless you can some action to preserve the format written with PROC FORMAT.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Ok a bit of background information. I have been programming SAS for six years now, and I have never came accross anything like this. this dataset consists of 3000 variables; all of them get a format assigned, which is basically named like the variable, and those which end with a number, get an F attached. Then, in one program I do the following:

proc format;
value LBWSAM01F
1 = '1 Pilot cohort'
2 = '2 Main cohort'
;
RUN;
DATA test1 ;
SET dataset.small;
KEEP lbwsam01;
FORMAT lbwsam01 lbwsam01F.; /*checking column attributes the format SAS attaches is called lbwsam0114.*/
RUN; /*no error message in the log, but opening test1, the formats are not assigned*/

Proc TAbulate data=test1;
class lbwsam01;
table lbwsam01;
RUN; /*the output table is using the right formats, as if they were assigned*/
PRoc contents data=test1; /*it says lbwsam01f is assigned*/
RUn;

Then, I open the dataset (test1). Depending on how you have SAS organised, I have my libraries on the left hand side, I open my temporary library (work) find the dataset test1, double click and straight away I can see it open in a spread sheet...without formats assigned, which means I only see the values 1 and 2, instead 1 Pilot Cohort, 2 Main Cohort. If I then right click on the column, I get to my column attributes and can see the name of the format attached, which is lbwsam0114...
I wouldn't worry too much about it and change simply the format name, but I have a couple of hundred variabels for which I experience this problem...so I'd rather know what's going on, also it's a longitudinal dataset...so more to come. I have sent this little program and a part of the dataset 'small' to another SAS programmer, and he has experienced the same problems and cannot understand it. I'm happy to send it to you, email me [email protected] as I don't think I can upload it here somehow...or can I?!

[Cynthia_sas]

Hi:
A clarification, please. You said:
I open my temporary library (work) find the dataset test1, double click and straight away I can see it open in a spread sheet...without formats assigned, which means I only see the values 1 and 2, instead 1 Pilot Cohort, 2 Main Cohort.

You mean you open the WORK version of TEST1 in SAS Viewtable mode???? Not in an Excel spreadsheet????

If you DO mean an Excel spreadsheet, how are you creating the file for Excel?? Are you using ODS or choosing the "View in Excel" choice from the drop-down menu? What about the PROC TABULATE output?? Do you care about the TABULATE output or only about WORK.TEST1??

When I try code similar to your code, I see the user-defined format being used in Viewtable mode when I look at WORK.TEST1 -and- being used in Excel, if I choose menu option "View in Excel". Code I tried is below. The only thing different is that I create LBWSAM01 in my code from SASHELP.CLASS -- whereas in your code, you must have LBWSAM01 in your SET dataset.small file.

You may have reached the point where you need to work with Tech Support on this issue. They can look at your data, ALL your data, at your SAS logs and you can send them screen shots of what you're seeing or not seeing in Viewtable mode.

To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

cynthia
[pre]
proc format;
value LBWSAM01F
1 = '1 Pilot cohort'
2 = '2 Main cohort' ;
RUN;

DATA test1 ;
SET sashelp.class;
if sex = 'F' then lbwsam01 = 1;
else lbwsam01 = 2;
KEEP lbwsam01 sex name;
FORMAT lbwsam01 lbwsam01F.;
RUN;

Proc TAbulate data=test1;
class lbwsam01;
table lbwsam01;
RUN;

Proc contents data=test1;
Run;

[/pre]

[Peter_C]

I wonder if this problem is occuring across the "longer format name" boundary at length 8?
That might explain the disappearing "F" from the 9th position of the format name. Possible cause - if the 3000 variable data sits in a v8 type library.

As a quick check original poster could retry with shorter format name.

just my two cents
peterC

[Cynthia_sas]

Ah, good eyes and good call, Peter! I hadn't thought about the V8 issue. That is the perfect suggestion for debugging.

cynthia

[ArtC]

Using Cynthia'a code and SAS9.1.3 I was able to duplicate the original issue. It seems to be a problem with the way VIEWTABLE handles the longer format name. Good catch Peter. The format is assigned correctly and works correctly - in TABULATE and CONTENTS, however VIEWTABLE seems unable to properly use it or even display it.

VIEWTABLE seemed to work fine in SAS9.2. Message was edited by: ArtC

[deleted_user]

No it doesn't work correctly...Proc Export also doesn't find the format and terminates with an error message. That is how I actually realised that there is a problem. Also Stat Transfer is not able to use that format. So do you think the only way of resolving the problem is to shorten the label name? Or is there an option I could use to allow longer label names? I really don't want to change the label names (too many). Thanks for everybody who is trying to figure out this problem!!!!
Maybe I need to update to SAS9.2.

[ArtC]

As a minimum you need to chat with the Technical Support folks (include a link to this thread). There may be a hot fix. Otherwise shortening the format name(s) or using SAS9.2 are alternatives.

[deleted_user · Accepted solution]

Here the answer from SAS technical support:

"I believe you are experiencing a known problem as regards long format names.
In SAS 9.1, long format and informat names are not supported in the VIEWTABLE window. We have this documented in SAS Note SN-008070:

http://support.sas.com/techsup/unotes/SN/008/008070.html

The issue is however fixed in SAS 9.2."

Thanks again to everyone!!
Melanie.

[deleted_user]

No excel...SAS viewable.

[Andrew-S]

We have multiple users experiencing the same issue here on SAS 9.4 TS1M3. 

Format errors are ignored. Dataset is opened and Formats are not applied in VIEWTABLE output. Checking the column attributes, the Format name is also incorrect (random name). However, if you output the dataset to Excel or listings the Formats appear correctly. 

• All format names are 8 characters or less
• Tried increasing SAS memory to 3GB, 4GB

LIBRARY is path to current directory

then OPTIONS FMTSEARCH=(LIBRARY);

Any ideas?

[Tom]

Open a new topic.  Try to create a simple example that causes the problem on your system and post the example in the new question.