Hi All, Is there a way to retail the proc format values in the proc print regardless of the raw values? Below is the below sample data & the code. I need to have the proc format values in the output even there is no data for the proc format buckets. when there is no data it is fine to populate it with 0 values. Please let me know if there is a way to do this. Thanks Proc format buckets: "0" "1-1,000" "1,001-10,000" "10,001-20,000" "20,001-30,000" "30,001-40,000" "40,001-50,000" "50,001-60,000" "GT 60,000" "ND" Sample data & the Code: data test1; infile datalines dsd missover dlm="#"; input VAR1$ VAR2$ VAR3$ ID; datalines; LLLL#1#ND#101 MMMM#2#2454#101 XXXX#3#0#102 YYYY#4#195#101 PPPP#6#70000#102 ZZZZ#7#25000#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" ; run; 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 strip(&app.) as Group ,case when VAR1 = "LLLL" and ID in (101) then "USA" when VAR1 = "MMMM" and ID in (101) then "ASIA" when VAR1 = "XXXX" and ID in (102) then "AUSTRALIA" when VAR1 = "YYYY" and ID in (101,102,103,106) then "UK" when VAR1 = "ZZZZ" and ID in (104) then "EUROPE" when VAR1 = "PPPP" and ID in (102,103,104) then "JAPAN" when VAR1 = "VVVV" and ID ne . then "ROI" else VAR1 end as Region, count(VAR2) as Freq_Month1 from test2 group by Group, Region order by (case when VAR4 is null then 9999999999999999999999 else VAR4 end), Region ; quit; %mend; %M_Format(case when VAR4 = . then "ND" else put(input(VAR3,12.),gfmt.) end); proc print data=test3; run; Current Output: SAS Output Obs Group Freq_Month1 1 2 3 4 5 6 0 1 1-1,000 1 1,001-10,000 1 20,001-30,000 1 GT 60,000 1 ND 1 Expected Output: Obs Group Freq_Month1 1 0 1 2 1-1,000 1 3 1,001-10,000 1 4 20,001-30,000 1 5 30,001-40,000 0 6 40,001-50,000 0 7 50,001-60,000 0 8 GT 60,000 1 9 ND 1
... View more
