Hello everyone.
I am using SAS code to get data from API, and I want to create a table that shown response rate. To create that table, I am using this code:
proc sort data=work.TARGET_SKLM_KOTAKAB3;by KotaKabKecKel;run;
proc sort data=work.RESPONDEN_SKLM_KOTKAB2;by KotaKabKecKel;run;
data work.Response_Rate;
length KotaKabKecKel $200;
merge work.RESPONDEN_SKLM_KOTKAB2(in=a) work.TARGET_SKLM_KOTAKAB3(in=b);
by KotaKabKecKel;
if b;
format Capaian percent9.2;
Capaian = Responden/Data_valid;
Kota_Kabupaten = KotaKabKecKel;
drop KotaKabKecKel;
run;
data Response_Rate2;
retain Kota_Kabupaten;
set Response_Rate;
run;
The result from that would look like this:
It's the right result, and then I promote the table into caslib so I can visualize it in Visual Analytics.
I then use List Table to show the result and it looks like this:
Because of default aggregation for measure data item is sum, the result looks like that.
What I want to ask, is there any way to rename "Sum:" in the total column?
And how to change the sum percentage to the right number ( 212/214 = 99.07% ) ?
Yes you can achieve the same result using Proc REPORT, see code example below.
Some explanation about the code:
Proc REPORT will build the report line by line.
data have;
infile cards dlm=",";
input
kota : $32.
resp : 8.
valid : 8.
;
cards;
seribu,18,18
barat,44,44
pusat,36,36
selatan,44,44
timur,35,35
utara,35,37
;
proc report data=have;
column kota resp valid capian _dummy;
define kota / group;
define resp / analysis sum;
define valid / analysis sum;
define capian / computed format=percent9.2;
define _dummy / computed noprint;
rbreak after / summarize style={backgroundcolor=lightblue};
compute _dummy ;
capian = resp.sum / valid.sum;
if _break_ = "_RBREAK_" then do;
kota = "Total";
end;
endcomp;
run;
Calculate the value for capaian within the report using an expression like this:
Sum(_ByGroup_, resp) / Sum(_ByGroup_,valid)
Change the format to percent, use your names for the resp and valid. This will give you the correct total value.
As for the text in the total line, there is currently not a way to change this. I would simply not display the labels for the total values.
Yes you can achieve the same result using Proc REPORT, see code example below.
Some explanation about the code:
Proc REPORT will build the report line by line.
data have;
infile cards dlm=",";
input
kota : $32.
resp : 8.
valid : 8.
;
cards;
seribu,18,18
barat,44,44
pusat,36,36
selatan,44,44
timur,35,35
utara,35,37
;
proc report data=have;
column kota resp valid capian _dummy;
define kota / group;
define resp / analysis sum;
define valid / analysis sum;
define capian / computed format=percent9.2;
define _dummy / computed noprint;
rbreak after / summarize style={backgroundcolor=lightblue};
compute _dummy ;
capian = resp.sum / valid.sum;
if _break_ = "_RBREAK_" then do;
kota = "Total";
end;
endcomp;
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.