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
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
Excellent solution Michael! Instead of creating the output in Results, how can you create the same as a data set in work library?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.