Hi,
i have this proc report:
proc report nowd data=ATM_EXPO.TOTALE_EXPORT_RE_T_&TERM_YM ;
COLUMN
PERIMETRO
CLUSTER
tipo_terminale_
N_TERM
N_prelievi_medi
Importo_prelievi_medio
Delta_anno_imp_prel
ORDINE
Media_trim_LDS_prel
Delta_anno_prec_LDS_prel
N_TERM_VER
N_versamenti_medi
Importo_versamenti_medio
Delta_anno_imp_verS
Media_trim_LDS_ver_as
Delta_anno_prec_ver_as
Media_trim_LDS_ver_co
Delta_anno_prec_ver_co
;
DEFINE PERIMETRO /GROUP 'Perimetro';
DEFINE ORDINE/ NOPRINT ORDER=DATA;
DEFINE CLUSTER /GROUP ORDER=DATA 'Cluster';
DEFINE tipo_terminale_ /GROUP ORDER=DATA 'Tipo terminale';
DEFINE N_TERM /analysis SUM 'Numero ATM' ;
DEFINE N_prelievi_medi / analysis mean 'Numero prelievi per ATM' ;
DEFINE Importo_prelievi_medio / analysis mean 'Imp.prel. per ATM' ;
define Delta_anno_imp_prel / analysis mean 'Delta' style(column)={tagattr='format:###0.00%'};
DEFINE Media_trim_LDS_prel / analysis mean 'LDS prelievo ultimo trimeste' style(column)={tagattr='format:###0.00%'};
DEFINE Delta_anno_prec_LDS_prel / analysis mean 'Delta trimestre anno corr. vs prec.' style(column)={tagattr='format:###0.00%'};
DEFINE N_TERM_VER /analysis SUM 'Numero ATM di versamento' ;
DEFINE N_versamenti_medi / analysis mean 'Numero versamenti per ATM' ;
DEFINE Importo_versamenti_medio / analysis mean 'Imp. versato per ATM' ;
DEFINE Delta_anno_imp_verS / analysis mean 'Delta' style(column)={tagattr='format:###0.00%'};
DEFINE Media_trim_LDS_ver_as / analysis mean 'LDS ver.ass. ultimo trimestre' style(column)={tagattr='format:###0.00%'};
DEFINE Delta_anno_prec_ver_as / analysis mean 'Delta trimestre anno corr. vs prec.' style(column)={tagattr='format:###0.00%'};
DEFINE Media_trim_LDS_ver_co / analysis mean 'LDS ver.cont. ultimo trimestre' style(column)={tagattr='format:###0.00%'};
DEFINE Delta_anno_prec_ver_co / analysis mean 'Delta trimestre anno corr. vs prec.' style(column)={tagattr='format:###0.00%'};
break after CLUSTER / suppress summarize dol skip;
compute after CLUSTER;
tipo_terminale_ = "SubTotale" ;
if CLUSTER = "Totale Region" then call define (_row_, 'style', 'style=[background=lightgreen]');
call define (_row_, 'style', 'style=[background=lightgray]');
endcomp;
compute cluster;
if CLUSTER = "Totale Region" then call define (_row_, 'style', 'style=[background=lightgreen]');
if CLUSTER = "ITALY" then call define (_row_, 'style', 'style=[background=YELLOW]');
endcomp;
run;
How can i calculate the weighted average with variable “NUMERO ATM’ (1.107,2.092,537,1.796) ??
Thank's so much
Use PROC SUMMARY, compute the weighted average there (and all other needed statistics), then create the PROC REPORT code to display it.
Proc REPORT has support for the WEIGHT statement. See sample code below. Maybe with this you can get what you need.
proc means data=sashelp.cars sum mean maxdec=0;
class type;
var invoice;
weight Cylinders;
run;
proc report data=sashelp.cars;
column type invoice=invsum invoice=invavg;
define type / group;
define invsum / analysis sum format=comma12. "invsum";
define invavg / analysis mean format=comma12. "invavg";
weight Cylinders;
run;
data have;
input Customer_ID Team $ Loan_Sum interest_rate;
cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
run;
proc report data=have;
column Team Loan_Sum=x Loan_Sum=XX Customer_ID interest_rate;
define Team / group;
define x / analysis sum format=comma12. "Loan_Sum";/*Wrong result!!*/
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
weight XX;
run;
ERROR: Variable XX not found.
The sum is still not correct.
I even don't understand what values are displayed in sum column
data have;
input Customer_ID Team $ Loan_Sum interest_rate;
cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
run;
proc report data=have;
column Team Loan_Sum Loan_Sum=x Customer_ID interest_rate;
define Team / group;
define x / analysis sum format=comma12. "Loan_Sum";/*Wrong result!!*/
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
weight Loan_Sum;
run;
You are weighting Loan_Sum by itself. (Essentially squaring loan_sum and then adding up those squares). Probably not what you wanted to do, but its not clear to me what you did want. Show us the desired output.
Have a look at the following example. To use a weight on a variable, it has to be a variable in the input data set. So I created a second variable load_sum2 that has the same content as loan_sum.
Both variables are used in the report using an alias ls and ls2. LS2 is using the weight= option. This gives us the two different totals plain and weighted.
We then have a third computed column that uses the two LS... values to computed the number you are looking for.
In the DEFINE statement we can use the NOPRINT option to suppress the printing.
Give it a try:
data have;
input Customer_ID Team $ Loan_Sum interest_rate;
Loan_Sum2 = Loan_Sum;
cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
proc report data=have;
column Team Loan_Sum=ls loan_sum2=ls2 ls_wgt Customer_ID interest_rate;
define Team / group;
define ls / analysis sum format=comma12. "Loan SUM";
define ls2 / analysis sum weight=interest_rate format=comma12. "Loan SUM weight" /* noprint */ ;
define ls_wgt / computed format=comma10.3 ;
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
compute ls_wgt;
ls_wgt = ls2 / ls;
endcomp;
;
run;
Thank you!
Your result is 100% but logically I don't understand.
The weight is the sum_loan and not the interest rate.
We want Weighted Average of interest rate (Weights are the sum_loan).
Why did you put the interest as weights and not sum_loan?
Do you understand my point?
You can get this easier with a WEIGHT= option:
proc report data=have;
column Team Loan_Sum Customer_ID interest_rate;
define Team / group;
define loan_sum / analysis sum;
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest_rate / analysis mean weight=loan_sum format=comma12.3 "Weighted_Avg_interest_rate";
run;
As in PROC SUMMARY, do not use a global WEIGHT. Read the documentation of the DEFINE statement.
Hello
I want to ask about the following statement
define ls2 / analysis sum weight=interest_rate format=comma12. ;
Is it performing SUM of multiplications of Loan_Sum and interest_rate?
If yes,will it equivalent to write
define interest_rate / analysis sum weight=ls2 format=comma12. ;
data have;
input Customer_ID Team $ Loan_Sum interest_rate;
Loan_Sum2 = Loan_Sum;
cards;
111 A 100 2
333 A 200 3
888 A 300 4
222 A 400 5
111 B 300 6
777 B 400 2
333 B 100 3
;
proc report data=have;
column Team Loan_Sum=ls loan_sum2=ls2 ls_wgt Customer_ID interest_rate;
define Team / group;
define ls / analysis sum format=comma12. "Loan SUM";
define ls2 / analysis sum weight=interest_rate format=comma12. "Loan SUM weight" /* noprint */ ;
define ls_wgt / computed format=comma10.3 ;
define Customer_ID / analysis N format=comma12. "Nr_Loans";
define interest_rate / analysis mean format=comma12.3 "Weighted_Avg_interest_rate";
compute ls_wgt;
ls_wgt = ls2 / ls;
endcomp;
;
run;
Yes weight= will multiply the original value with the weight value.
You can not use an alias (ls2) in the weight= option it has to be a proper variable.
The example provided by @Kurt_Bremser does exactly what you need. Please note it is using the MEAN statistic, as this is what you need.
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.