topic Re: Accumulated Value in SAS Programming

Accumulated Value

Demarchi83 — Tue, 26 Dec 2023 17:15:04 GMT

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.

Re: Accumulated Value

AhmedAl_Attar — Tue, 26 Dec 2023 18:31:00 GMT

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

Re: Accumulated Value

Tom — Tue, 26 Dec 2023 19:23:00 GMT

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;

Re: Accumulated Value

sbxkoenk — Tue, 26 Dec 2023 21:08:58 GMT

@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#p0beovds26sevpn1ti8o20dulkp2

PROC FEDSQL :

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

BR, Koen

Re: Accumulated Value

sbxkoenk — Tue, 26 Dec 2023 21:10:15 GMT

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