topic Re: How to introduce a format to avoid jumping freq distribution values? in SAS Procedures

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

Mirisage — Mon, 09 Sep 2013 16:08:07 GMT

Hi SAS Forum,

I have this data set.

Data have;

input income;

cards;

392

219

208

-10

372

360

-9

-8

393

190

168

-7.5

138

125

-9

-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 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

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

Jagadishkatam — Mon, 09 Sep 2013 16:34:28 GMT

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

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

ballardw — Mon, 09 Sep 2013 16:38:39 GMT

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;

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

Mirisage — Wed, 11 Sep 2013 16:52:49 GMT

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

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

Mirisage — Wed, 11 Sep 2013 16:56:55 GMT

Sorry Reeza,

Thank you for your reply as well.

Mirisage