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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.