BookmarkSubscribeRSS Feed
Demarchi83
Calcite | Level 5
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.

4 REPLIES 4
AhmedAl_Attar
Ammonite | Level 13

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
SAS Super FREQ

@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 FEDSQL :

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

 

BR, Koen

Tom
Super User Tom
Super User

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
SAS Super FREQ

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1004 views
  • 0 likes
  • 4 in conversation