BookmarkSubscribeRSS Feed
santhosh
Fluorite | Level 6

I have data set

data test;

input intrest count sum;

datalines;

0.4 1 100

0.6 1 150

1.2 1 40

1.8 1 30

2.1 1 20

0.3 1 20

0.2 1 30

1.6 1 40

2.2 1 60

;

run;

i need to divide the interval based on 0.5 automatically as shown in output and display count and sum of values

output should be

INTREST_RATE   COUNT   SUM

>= 0 - < 0.5    3      150

>= 0.5 - < 1    1      150

>= 1 - < 1.5    1       40

>= 1.5 - < 2    1       30  

>= 2 - < 2.5    2       80

can any one help

2 REPLIES 2
MichaelLarsen
SAS Employee

By using a user defined format you can achieve the desired output:

/* Create a user written format to group the intrest values in 0.5 intervals */

data fmtdata ;

  fmtname = 'intrest'; /* Format name */

  sexcl = 'N'; /* Exclude Start value from interval: No */

  eexcl = 'Y'; /* Exclude End value from interval: Yes   */

  do start=0 to 100 by 0.5;

    end = start + 0.5;

    label = catx(' ',put(start,5.1),'- <',put(end,5.1));

    output;

  end;

  format start end 10.5;

run; 

/* Create the format */

proc format cntlin=fmtdata;

run;

data test;

input intrest countvar sumvar;

datalines;

0.4 1 100

0.6 1 150

1.2 1 40

1.8 1 30

2.1 1 20

0.3 1 20

0.2 1 30

1.6 1 40

2.2 1 60

3.5 1 100

;

run;

/* Create the report */

proc tabulate data=test ;

  class intrest / preloadfmt;

  format intrest intrest.;

  var countvar sumvar;

  table intrest ALL='Total',(N='Count' sumvar=''*sum='Sum'*f=8.)

        ;

run;

Regards,

Michael

bsumesh
Calcite | Level 5

Excellent solution Michael! Instead of creating the output in Results, how can you create the same as a data set in work library?

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1868 views
  • 0 likes
  • 3 in conversation