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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.