Release is 9.4. I'm using PROC FORMAT to map a continuous variable into groups. The format has 4 levels while the input data values fall into 2 of the 4 levels. I want PROC SQL to display all 4 rows including rows where all values for the aggregation functions are missing. Instead I get just 2 rows. How do I get PROC SQL to display the rows where all values for the row are missing? data df;
infile cards dlm = ',';
length district $ 3
sector $ 5
name $ 8;
input district $
sector $
name $
pre
post
age;
list;
datalines;
I, North, Patton, 17, 27, 22
I, South, Joyner, 13, 22, 19
I, East, Williams, 111, 121, 29
I, West, Jurat, 51, 55, 22
II, North, Aden, 71, 70, 17
II, South, Tanner, 113, 122, 32
II, East, Jenkins, 99, 99, 24
II, West, Milner, 15, 65, 22
III, North, Chang, 69, 101, 21
III, South, Gupta, 11, 22, 21
III, East, Haskins, 45, 41, 19
III, West, LeMay, 35, 69, 20
III, West, LeMay, 35, 69, 20
;;;;
proc format;
value age_fmt
0 - 25 = '0-25'
26 - 50 = '26-50'
51 - 75 = '51-75'
76 - high = 'Over 75';
run;
proc sql;
select unique put(age, age_fmt.) label = 'Group'
, count(age) label = 'Count'
, mean(age) label = 'Mean'
, max(age) label = 'Max'
, mean(age) label = 'Mean'
from df
group by put(age, age_fmt.);
quit;
... View more