topic Re: grouping numbers formatted as character in SAS Programming

grouping numbers formatted as character

Batman — Thu, 25 Mar 2021 22:07:08 GMT

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;

Re: grouping numbers formatted as character

SASKiwi — Thu, 25 Mar 2021 22:17:01 GMT

"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?

Re: grouping numbers formatted as character

Batman — Thu, 25 Mar 2021 22:19:31 GMT

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.

Re: grouping numbers formatted as character

ballardw — Thu, 25 Mar 2021 22:47:24 GMT

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.

Re: grouping numbers formatted as character

Batman — Thu, 25 Mar 2021 22:54:11 GMT

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;

Re: grouping numbers formatted as character

SASKiwi — Thu, 25 Mar 2021 22:58:55 GMT

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

Re: grouping numbers formatted as character

Batman — Thu, 25 Mar 2021 23:00:33 GMT

I did, see my last reply before yours.

Re: grouping numbers formatted as character

Batman — Thu, 25 Mar 2021 23:59:21 GMT

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

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