Using a format to control the appearance order of data

[Data_User]

@Kurt_Bremser Thanks for checking, but I am looking for solution with the below code. I don't want to remove the macro variables since I have a lot of variables to pass through & expecting the results as I mentioned in my original request. I know I can achieve by other methods, but looking to solve by tweaking the below code only as I have a buch of variables to pass through. Please let me know if there is a way to solve it.

proc format;
value gfmt
low-0               = "   0"
1-1000            = "   1-1,000"
1001-10000    = "  1,001-10,000"
10001-20000  = " 10,001-20,000"
20001-30000  = " 20,001-30,000"
30001-40000  = " 30,001-40,000"
40001-50000  = " 40,001-50,000"
50001-60000  = " 50,001-60,000"
60001-high     = " GT 60,000"
other               = " ND"
;

data test2;
set test1;
if VAR3 = "ND" then VAR4=. ;
else VAR4=input(VAR3,12.);
run;

%macro M_Format (app);
proc sql;
create table test3 as
select count(VAR2) as Freq_Month1
,strip(&app.) as Group
,case when VAR1 = "XXXX" and ID in (101,102,103,104,105,106) then "USA"
when VAR1 = "YYYY" and ID in (101,102,103,106) then "UK"
when VAR1 = "ZZZZ" and ID in (101,102,103,104) then "EUROPE"
when VAR1 = "VVVV" and ID ne . then "ROI"
when VAR1 = "AAAA" and ID in (101,102,103,104) then "JAPAN"
else VAR1 end as Region
from test2
group by Group, Region
order by group, Region
;
quit;
%mend;

%M_Format(case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end);

[Kurt_Bremser]

If you can define in which ways the code needs to be made dynamic, I could wrap my code into a macro again so it fits your needs. Your code can never create the desired order reliably, because of the way strings order up along the ASCII character table.

[ballardw]

---

@Data_User wrote:

I also tried with the below format as well, but no luck.

proc format;

value gfmt

low-0        = "         0"

1-1000       = "        1-1,000"

1001-10000   = "       1,001-10,000"

10001-20000  = "      10,001-20,000"

20001-30000  = "     20,001-30,000"

30001-40000  = "    30,001-40,000"

40001-50000  = "   40,001-50,000"

50001-60000  = "  50,001-60,000"

60001-high   = " GT 60,000"

other        = "ND"

;

run;

---

What type of values do you expect to get the formatted value of ND? The format as stated will assign ND to missing a decimal values between your stated endpoints such as 0.5, 1000.1, 20000.3

[Data_User]

@ballardw "ND"s can be missing and other values which falls outside the format range I have defined.

[Data_User]

Hi All, thanks very much for all your contributions on this. I am now able to achieve what I want. I really appreciate your help on this. Have a good weekend 🙂

Best Regards,