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

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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
  • 1840 views
  • 0 likes
  • 3 in conversation