Proc format :Applying custom format results in column with no values.

[Myra22]

Hi,

I have character column to which i am applying a custom format. the problem is, when I create Frequency table after applying the format , no data appears. I am not sure where i am going wrong. Please help. 

Frequency tables with and without applying format are below.

proc format ;
value $fcrust
"Crust Thickness Unknown"=0
"Continental Crust"=1
"Intermediate Crust"=2
"Oceanic Crust"=3;

proc freq data= Volcano123;
table Crust_type;
format Crust_type $fcrust.;

Edit :

The table below is obtained from proc Contents shows that Crust_type is character type with no  specific format 

proc format;
 value $fplate 
 "Intraplate" =1
 "Rift Zone"=2
 "Subduction Zone"= 3;
proc format ;
value $fcrust
"Crust Thickness Unknown"=0
"Continental Crust"=1
"Intermediate Crust"=2
"Oceanic Crust"=3;

When i use above code following log is displayed.

When i create frequency table i get the tables below followed by log

Thanks everyone

[SASKiwi]

If you create a character format then the format label also needs to be character. Try this:

proc format ;
value $fcrust
"Crust Thickness Unknown"="0"
"Continental Crust"="1"
"Intermediate Crust"="2"
"Oceanic Crust"="3";
run;

[Myra22]

hi SasKiwi,

I have tried that but still not working.

[SASKiwi]

Please post your complete SAS log as it most likely will contain NOTEs that may help us understand what is happening.  

[Tom · Accepted solution]

Are you sure CRUST_TYPE is character?  Perhaps it is numeric and it has some other format attached to it that is making the numbers be displayed as those strings.

If looks like the values have leading spaces. The ODS output you posted the photograph of does not properly display leading spaces.   Try your PROC FREQ using the $QUOTE. format instead.  The you might be able to detect the leading spaces in the output.

Since you defined a format with a maximum width of only one character then any value that was not one of the 4 values defined in your format will essentially be printed using $1. format.  So if all four values in your actual data start with at least one leading space that could be the reason why everything shows as the same blank value.

It is much easier to see leading spaces in plain old text LISTING output.

data have ;
  input crust_type $char30.;
cards;
Crust Thickness Unknown
 Continental Crust
Intermediate Crust
 Oceanic Crust
;


proc freq data=have;
  tables crust_type;
run;

The FREQ Procedure

                                                    Cumulative    Cumulative
crust_type                 Frequency     Percent     Frequency      Percent
----------------------------------------------------------------------------
 Continental Crust                1       25.00             1        25.00
 Oceanic Crust                    1       25.00             2        50.00
Crust Thickness Unknown           1       25.00             3        75.00
Intermediate Crust                1       25.00             4       100.00

[Myra22]

Hi Tom $QUOTE. did the trick. There was a leading blank in the string . Thank you so much .

[Kurt_Bremser]

I guess you did something to the dataset between the two PROC FREQs, or used different datasets. The number of missing values is not changed by a format that does not explicitly create missing values, or reformat them to a non-missing value. Both of which your format does not do.

A value not "caught" by the format would be used raw (unformatted).

Run both PROC FREQs which create your outputs, and the PROC FORMAT in immediate succession, and post the complete log of all three steps.

[Tom]

---

@Kurt_Bremser wrote:

I guess you did something to the dataset between the two PROC FREQs, or used different datasets. The number of missing values is not changed by a format that does not explicitly create missing values, or reformat them to a non-missing value. Both of which your format does not do.

A value not "caught" by the format would be used raw (unformatted).

Run both PROC FREQs which create your outputs, and the PROC FORMAT in immediate succession, and post the complete log of all three steps.

---

PROC FREQ will distinguish between character variables that are all blanks (what it count as missing) and those that are blank after the format is applied.  If there are no observations with a completely empty value then the formatted blanks are treated as a normal category.  If there is at least on completely empty value then all of the formatted values that print as blank are treated as missing.

data have ;
  input crust_type $char30.;
cards;
Crust Thickness Unknown
 Continental Crust
Intermediate Crust
 Oceanic Crust

;

proc freq data=have;
  tables crust_type;
  format crust_type $1.;
run;

proc freq data=have;
  where crust_type ne ' ';
  tables crust_type;
  format crust_type $1.;
run;

And if all of the values format as blank and at least one of them is all blank you get the output from the original posting.  With an empty table section and the count of missing in the footer.

proc freq data=have;
  where crust_type =: ' ';
  tables crust_type;
  format crust_type $1.;
run;