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
