proc format for missing and other

[user23]

Hi,

I am trying to set up a format but having some trouble. I'd like to set missing values to 0, set values 1-10 as *, and any other value/greater than 10 to just remain the same. Then I'd like to use the format in a proc sql step.

I currently have the following:

proc format;

VALUE suppressf

.  = 0

1-10 = '*'

other= ' ';

proc sql;

select year, month, put((count(*)), suppressf.) as recordcnt

from ddata.d2020

group by year, month;

quit;

When I run this, it looks like the * value is working, but the ones that were missing get dropped and the other value ones are blank. (also for clarification, if there was a value of 17, i'd want it to remain 17 since it's not missing or in the range of 1-10.) I'm pretty sure there is an issue in the way i'm defining the format, as I've done other formats in the proc sql step in a similar way, and those have worked as intended. Just not sure what to fix.

Any help would be really appreciated!

[LeonidBatkhan]

If you want "other" remain the same, that is un-formatted, then remove "other=" from the format definition and specify default width to accommodate un-formatted values :

proc format;
   VALUE suppressf (default=10) 
   .  = '0'
   1-10 = '*'
   ;
run;

Hope this helps.

[user23]

I tried that but I'm still getting blanks for the ones that I want to stay unformatted.

[SASKiwi]

That's because you are using the PUT function. Try it by just applying the format:

proc sql;
select year, month, count(*) as recordcnt format = suppressf.
from ddata.d2020
group by year, month;
quit;

[LeonidBatkhan]

Try this:

proc format;
  VALUE suppressf
  . = '0'
  1-10 = '*'
  other = [comma10.]
;
run;

 Or specify default length:

proc format;
  VALUE suppressf (default=10)
  . = '0'
  1-10 = '*'
;
run;

[Tom · Accepted solution]

You are getting blanks because you told it output a blank if the value was not one of the values you explicitly assigned display strings.

You could specify the format you want to use for the other values.  Perhaps BEST12? or COMMA?.

VALUE suppressf 
  .  = '0'
1-10 = '*'
 other = [best12.] 
;

You could not include the OTHER clause, but then you need to set a default length otherwise SAS will guess you want to use only 1 character to display the values.

VALUE suppressf (default=12)
  .  = '0'
1-10 = '*'
;

Note that numeric formats normally right align so the PUT() results might be confusing since normal numeric formats will have leading spaces and your strings don't.

       i  formatted_i
----------------------
       .  0
      -1            -1
       0             0
       1  *
      10  *
      11            11
    5555          5555

[ChrisNZ]

One way to apply the different explanations offered (other= is wrong, format length is wrong, formatting is right justified) is the change you code to:

proc format;
VALUE suppressf
.  = 0
1-10 = '*'
;
 
proc sql;
select year, month, put((count(*)), suppressf12. -l) as recordcnt
from ddata.d2020
group by year, month;
quit;

[user23]

Thank you all, it is working now! Didn't think about adding the default length or specifying a format for other.