grouping numbers formatted as character

[Batman]

I need to convert some numbers into character values and then group them, but I’m having trouble with numbers over 3 digits.   Any suggestions how to keep 100+ folks in the 85+ age group?

proc format;                                            

  value numfmt                                          

  low-65='<65'                                          

  65-74='65-74'                                         

  75-84='75-84'                                          

  85-high='85+'                                         

  ;                                                     

  value $charfmt                                        

  low-'65' ='<65'                                       

  '65'-'74'='65-74'                                     

  '75'-'84'='75-84'                                     

  '85'-high='85+'                                       

  ;                                                     

data check;                                             

  do age=95 to 105;              

  agechar=strip(put(age,8.));                  

  numfmt=put(age,numfmt.);                              

  charfmt=put(agechar,$charfmt.); 

  output;

  end;

run;                                                    

proc print data=check(obs=10);                          

  where numfmt ne charfmt;

run;

[SASKiwi]

"I need to convert some numbers into character values and then group them"  - why? Keeping age as a numeric and grouping them works fine so why create problems by converting?

[Batman]

That would be my preference, but in order to make the word "Total" appear as the row header for the summary row in Proc Report, I need to switch the format of the age variable from numeric to character.

[ballardw]

If you really must do character values then you will have to list them out to prevent 100, or indeed practically any value from ending up in an unexpected range.

Proc Format uses the same comparison rules as sorting text when given a "range". So '100' is less than '85' (or '8') because the characters are compared one at a time until there is not an exact match. So '3' is greater than '100' because '3' is greater than (comes after in sort sequence) '1'.

Another approach could be to create a "dummy" numeric value that should not appear in your data such as -9999.

Supply a formatted value for that of 'Total' , note this means that your numeric range cannot start with "low" but should use the lowest expected value, such as 0 (assuming you really are dealing with ages as stated).

Then in the "summary" or what ever assign a column value of -9999.

Or provide data as a data step we can run and the entire code of you report for more targeted response.

[Batman]

Ok, if I could stick with the numeric format, here is some sample code.   However, it leaves a blank in the last row of the first column and I'd like to have that cell show "Total."

proc format;
value numfmt
low-65='<65'
65-74='65-74'
75-84='75-84'
85-high='85+'
;

data check;
do age=25 to 95;
cnt=1;
output;
end;
run;

proc report data=check;
cols age cnt;
define age / group format=numfmt.;
define cnt / sum;
rbreak after / summarize;
compute after;
age='Total';
endcomp;
run;

[SASKiwi]

Please post your PROC REPORT code. Maybe there is another way of doing this.

[Batman · Accepted solution]

I did, see my last reply before yours.

[Batman]

Never mind, this link has a solution on p. 9

https://support.sas.com/resources/papers/proceedings15/SAS1642-2015.pdf