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
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
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;
hi SasKiwi,
I have tried that but still not working.
Please post your complete SAS log as it most likely will contain NOTEs that may help us understand what is happening.
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
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.
@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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.