Hi SAS Forum,
I have this data set.
Data have;
input income;
cards;
0
15
.
392
219
95
208
-10
12
41
22
65
372
360
-9
-8
393
190
.
0
0
0
168
93
-7.5
0
14
43
138
52
125
0
-9
45
-1
;
run;
I wanted to get a frequency distribution table of above data set with below freq bands, and also reported exactly in the below order (not jumped here and there).
Missing
<=-9
> -9 to <=-8
> -8 to <=-1
> -1 to < 0
=0
> 0 to <=41
> 41-392
> 392
I have used the below code. It works.
But the freq bands in the freq table being created are jumped averywhere.
data want;
length Income_Range $20;
set have;
if Income = . then Income_Range='Missing';
else if Income <=-9 then Income_Range = '<=-9';
else if Income <=-8 then Income_Range = '> -9 to <=-8';
else if Income <=-1 then Income_Range = '> -8 to <=-1';
else if Income <0 then Income_Range ='> -1 to < 0';
else if income = 0 then Income_Range ='0';
else if Income <=41 then Income_Range ='> 0 to <=41';
else if Income <=392 then Income_Range ='> 41-392';
else Income_Range = '> 392';
run;
proc freq data=want;
tables Income_Range/list missing;
run;
Then I kept some spaces in the freq bands like below but still freq bands headings jumped everywhere.
data want2;
length Income_Range $20;
set have;
if Income = . then Income_Range='Missing';
else if Income <=-9 then Income_Range = ' <=-9';
else if Income <=-8 then Income_Range = ' > -9 to <=-8';
else if Income <=-1 then Income_Range = ' > -8 to <=-1';
else if Income <0 then Income_Range =' > -1 to < 0';
else if income = 0 then Income_Range =' 0';
else if Income <=41 then Income_Range ='> 0 to <=41';
else if Income <=392 then Income_Range ='> 1-392';
else Income_Range = ' > 392';
run;
proc freq data=want2;
tables Income_Range/list missing;
run;
Question:
Could anyone let me know how to create a format so I can get the final freq table's freq bands in the order I wanted.
Thanks
Mirisage
proc format could be the way to get the expected result
to create the format val as the requirement
proc format fmtlib;
value val .='Missing'
low--9='<=-9'
-9<--8= '> -9 to <=-8'
-8<--1='> -8 to <=-1'
-1<-<0='> -1 to < 0'
0='0'
0<-41='<=41'
41<-392='1-392'
392<-high='> 392';
run;
used the dataset have without creating the new dataset want as created earlier. and used the format val
proc freq data=have;
tables income/list missing;
format Income val.;
run;
Thanks,
Jagadish
proc format could be the way to get the expected result
to create the format val as the requirement
proc format fmtlib;
value val .='Missing'
low--9='<=-9'
-9<--8= '> -9 to <=-8'
-8<--1='> -8 to <=-1'
-1<-<0='> -1 to < 0'
0='0'
0<-41='<=41'
41<-392='1-392'
392<-high='> 392';
run;
used the dataset have without creating the new dataset want as created earlier. and used the format val
proc freq data=have;
tables income/list missing;
format Income val.;
run;
Thanks,
Jagadish
The format is simple
proc format library=work;
value IncomeRange
. = 'Missing'
low - -9 = '<= -9'
-9 <- -8 = '>-9 to <=-8'
-8 <- -1 = '>-8 to <=-1'
-1 <-< 0= '>-1 to <0'
0 = '0'
0<- 41 = '>0 to <=41'
41 <- 392 = '>41 to 392'
392 <- high = '>392'
;
run;
You will need to use the option ORDER=data on proc freq statement to get the values in the correct order as default is formatted.
proc freq data=have order=data;
tables Income/list missing;
format Income IncomeRange.;
run;
Hi Jagadishkatam,
Your code woks well. Thank you very much.
Hi ballardw,
Your code works well when "ORDER=data" statement is omitted. Thank you very much.
When "ORDER=data" statement is included, the freq bands jumped like below.
Missing | 2 | 5.71 | 2 | 5.71 |
0 | 6 | 17.14 | 8 | 22.86 |
>0 to <=41 | 5 | 14.29 | 13 | 37.14 |
>41 to 392 | 15 | 42.86 | 28 | 80.00 |
<= -9 | 3 | 8.57 | 31 | 88.57 |
>-9 to <=-8 | 1 | 2.86 | 32 | 91.43 |
>392 | 1 | 2.86 | 33 | 94.29 |
>-8 to <=-1 | 2 | 5.71 | 35 | 100.00 |
Many thanks to both of you again!
Mirisage
Sorry Reeza,
Thank you for your reply as well.
Mirisage
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.