Help using Base SAS procedures

How to introduce a format to avoid jumping freq distribution values?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

How to introduce a format to avoid jumping freq distribution values?

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


Accepted Solutions
Solution
‎09-09-2013 12:34 PM
Trusted Advisor
Posts: 1,129

Re: How to introduce a format to avoid jumping freq distribution values?

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

Thanks,
Jag

View solution in original post


All Replies
Solution
‎09-09-2013 12:34 PM
Trusted Advisor
Posts: 1,129

Re: How to introduce a format to avoid jumping freq distribution values?

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

Thanks,
Jag
Super User
Posts: 10,500

Re: How to introduce a format to avoid jumping freq distribution values?

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;

Super Contributor
Posts: 338

Re: How to introduce a format to avoid jumping freq distribution values?

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.

Missing25.7125.71
0617.14822.86
>0 to <=41514.291337.14
>41 to 3921542.862880.00
<= -938.573188.57
>-9 to <=-812.863291.43
>39212.863394.29
>-8 to <=-125.7135100.00

Many thanks to both of you again!

Mirisage

Super Contributor
Posts: 338

Re: How to introduce a format to avoid jumping freq distribution values?

Sorry Reeza,

Thank you for your reply as well.

Mirisage

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 200 views
  • 4 likes
  • 3 in conversation