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,   ... View more

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

@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); ... View more

This code is still not giving the results as I expected. Am I missing anything? ... View more

Hi,   I am trying to order the values in proc format & not able to succed in it. Is there a work around to get it done? Below is the sample data & the code I am using. Help will be highly appreciated.   data test1; infile datalines dsd missover dlm="#"; input VAR1$ VAR2$ VAR3$ ID; datalines; XXXX#1#ND#101 XXXX#2#2454#101 XXXX#3#0#102 YYYY#4#195#101 YYYY#5#5123#103 XXXX#6#354#102 ZZZZ#7#25000#104 ZZZZ#8#12000#105 VVVV#9#35000#106 VVVV#10#45000#110 ZZZZ#11#55000#101 VVVV#12#70000#103 XXXX#13#226#107 VVVV#14#325#101 YYYY#15#154#106 AAAA#16#ND#104 AAAA#17#.#103 AAAA#18#7000#102 AAAA#19#ND#102 AAAA#19#0#102 ZZZZ#19#0#104 ; run; 　 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);     Current Output: Freq_Month1 Group Region 1 0 EUROPE 1 0 JAPAN 1 0 USA 1 1,001-10,000 JAPAN 1 1,001-10,000 UK 1 1,001-10,000 USA 1 1-1,000 ROI 2 1-1,000 UK 1 1-1,000 USA 1 1-1,000 XXXX 1 10,001-20,000 ZZZZ 1 20,001-30,000 EUROPE 1 30,001-40,000 ROI 1 40,001-50,000 ROI 1 50,001-60,000 EUROPE 1 GT 60,000 ROI 3 ND JAPAN 1 ND USA   Expected Output: Freq_Month1 Group Region 1 0 EUROPE 1 0 JAPAN 1 0 USA 1 1-1,000 ROI 2 1-1,000 UK 1 1-1,000 USA 1 1-1,000 XXXX 1 1,001-10,000 JAPAN 1 1,001-10,000 UK 1 1,001-10,000 USA 1 10,001-20,000 ZZZZ 1 20,001-30,000 EUROPE 1 30,001-40,000 ROI 1 40,001-50,000 ROI 1 50,001-60,000 EUROPE 1 GT 60,000 ROI 3 ND JAPAN 1 ND USA ... View more

Hi,   I am trying to order the values in proc format & not able to succed in it. Is there a work around to get it done? Below is the sample data & the code I am using. Help will be highly appreciated.   data test1; infile datalines dsd missover dlm="#"; input VAR1$ VAR2$ VAR3$ ID; datalines; XXXX#1#ND#101 XXXX#2#2454#101 XXXX#3#0#102 YYYY#4#195#101 YYYY#5#5123#103 XXXX#6#354#102 ZZZZ#7#25000#104 ZZZZ#8#12000#105 VVVV#9#35000#106 VVVV#10#45000#110 ZZZZ#11#55000#101 VVVV#12#70000#103 XXXX#13#226#107 VVVV#14#325#101 YYYY#15#154#106 AAAA#16#ND#104 AAAA#17#.#103 AAAA#18#7000#102 AAAA#19#ND#102 AAAA#19#0#102 ZZZZ#19#0#104 ; run; 　 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);     Current Output: Freq_Month1 Group Region 1 0 EUROPE 1 0 JAPAN 1 0 USA 1 1,001-10,000 JAPAN 1 1,001-10,000 UK 1 1,001-10,000 USA 1 1-1,000 ROI 2 1-1,000 UK 1 1-1,000 USA 1 1-1,000 XXXX 1 10,001-20,000 ZZZZ 1 20,001-30,000 EUROPE 1 30,001-40,000 ROI 1 40,001-50,000 ROI 1 50,001-60,000 EUROPE 1 GT 60,000 ROI 3 ND JAPAN 1 ND USA   Expected Output: Freq_Month1 Group Region 1 0 EUROPE 1 0 JAPAN 1 0 USA 1 1-1,000 ROI 2 1-1,000 UK 1 1-1,000 USA 1 1-1,000 XXXX 1 1,001-10,000 JAPAN 1 1,001-10,000 UK 1 1,001-10,000 USA 1 10,001-20,000 ZZZZ 1 20,001-30,000 EUROPE 1 30,001-40,000 ROI 1 40,001-50,000 ROI 1 50,001-60,000 EUROPE 1 GT 60,000 ROI 3 ND JAPAN 1 ND USA   Thanks ... View more

@Kurt_Bremser How it can be solved by adding another numeric value?   @ballardw Please see below. Current Output & the expected outputs are shown below.   Current Output: Freq_Month1 Group Region 1 0 EUROPE 1 0 JAPAN 1 0 USA 1 1,001-10,000 JAPAN 1 1,001-10,000 UK 1 1,001-10,000 USA 1 1-1,000 ROI 2 1-1,000 UK 1 1-1,000 USA 1 1-1,000 XXXX 1 10,001-20,000 ZZZZ 1 20,001-30,000 EUROPE 1 30,001-40,000 ROI 1 40,001-50,000 ROI 1 50,001-60,000 EUROPE 1 GT 60,000 ROI 3 ND JAPAN 1 ND USA   Expected Output: Freq_Month1 Group Region 1 0 EUROPE 1 0 JAPAN 1 0 USA 1 1-1,000 ROI 2 1-1,000 UK 1 1-1,000 USA 1 1-1,000 XXXX 1 1,001-10,000 JAPAN 1 1,001-10,000 UK 1 1,001-10,000 USA 1 10,001-20,000 ZZZZ 1 20,001-30,000 EUROPE 1 30,001-40,000 ROI 1 40,001-50,000 ROI 1 50,001-60,000 EUROPE 1 GT 60,000 ROI 3 ND JAPAN 1 ND USA   ... View more

Hi,   Thanks for the response. If you notice my code I am ordering by the formatted values (which is "order by group, Region") but still not getting the results as I am expecting.   Thanks,   ... View more

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; ... View more