Accumulated Value

[Demarchi83]

Please people, a little help here.

I would like to calculate the accumulated value, but when I use the formula below the following error appears:



PROC SQL;
CREATE TABLE WORK.PARETO AS
SELECT
T1.CD_CLI,
T1.MRG_SR,
Sum(T1.MRG_SR) OVER (ORDER BY T1.PERIODO ROWS
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'acumulado'
FROM CLSD_01 T1
WHERE T1.PERIODO = 202311
;QUIT;

____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

[AhmedAl_Attar]

Hi @Demarchi83 

SAS Proc SQL which I think is based on SQL 1992 standards does not support SQL windowing analytical functions.

You alternatives are :
 - Data Step with the DOW loop --- Fully Customizable

 - Proc Report --- Fully Customizable
 - Proc Freq  --- Limited Customizable

 - Proc summary/means --- Limited Customizable

Hope this helps,

Ahmed

[sbxkoenk]

---

@AhmedAl_Attar wrote:

SAS Proc SQL which I think is based on SQL 1992 standards does not support SQL windowing analytical functions.

---

PROC SQL :

SAS Viya 4 SQL Procedure User’s Guide

• PROC SQL and the ANSI Standard
  ANSI = The American National Standards Institute
  https://go.documentation.sas.com/doc/en/pgmsascdc/v_046/sqlproc/p0corah7t43hy7n1r7efplde5c8z.htm
• Differences in PROC SQL and ANSI SQL
  https://go.documentation.sas.com/doc/en/pgmsascdc/v_046/sqlproc/p0corah7t43hy7n1r7efplde5c8z.htm#p0b...

PROC FEDSQL :

The FedSQL language (PROC FEDSQL) is the SAS implementation of the ANSI SQL:1999 core standard.

BR, Koen

[Tom]

If you want to calculate a running sum just use a SUM STATEMENT.

There is no need to jumps throw hoops to try an impose sequential data processing on SQL's normal set based processing.

data PARETO ;
  set CLSD_01;
  where PERIODO = 202311;
  acumulado + MRG_SR;
  keep CD_CLI MRG_SR acumulado ;
run;

If you want to generate separate accumulations per group then use BY group processing.

data PARETO ;
  set CLSD_01;
  by PERIODO CD_CLI ;
  if first.PERIODO then acumulado=0;
  acumulado + MRG_SR;
  keep PERIODO CD_CLI MRG_SR acumulado ;
run;

[sbxkoenk]

Home > Learn SAS > New Users >
culmulative sum by group using Proc SQL
https://communities.sas.com/t5/New-SAS-User/culmulative-sum-by-group-using-Proc-SQL/td-p/517593

Home > Programming > Programming >
Cumulative total using proc sql
https://communities.sas.com/t5/SAS-Programming/Cumulative-total-using-proc-sql/td-p/614451

Koen