topic proc format for missing and other in SAS Programming

proc format for missing and other

user23 — Tue, 13 Apr 2021 17:39:44 GMT

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!

Re: proc format for missing and other

LeonidBatkhan — Wed, 28 Apr 2021 20:10:41 GMT

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.

Re: proc format for missing and other

user23 — Tue, 13 Apr 2021 20:26:37 GMT

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

Re: proc format for missing and other

SASKiwi — Tue, 13 Apr 2021 20:47:33 GMT

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;

Re: proc format for missing and other

LeonidBatkhan — Tue, 13 Apr 2021 21:15:12 GMT

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;

Re: proc format for missing and other

Tom — Tue, 13 Apr 2021 22:03:29 GMT

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

Re: proc format for missing and other

ChrisNZ — Tue, 13 Apr 2021 22:56:05 GMT

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;

Re: proc format for missing and other

user23 — Wed, 14 Apr 2021 21:55:27 GMT

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