BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mirisage
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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
ballardw
Super User

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;

Mirisage
Obsidian | Level 7

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

Mirisage
Obsidian | Level 7

Sorry Reeza,

Thank you for your reply as well.

Mirisage

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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