Partition in Proc SQL

[david999]

sum(hours_watched) over (partition by user_id, content_title order by calendar_date) AS cumulative_hours,
____
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.

Can we use partitions inside Proc Sql, if not could you please help me the equivalent logic to use?

[tarheel13]

No, you can't use partition in proc sql. 

I think you can try a hash object like this. 

https://communities.sas.com/t5/General-SAS-Programming/Partition-by-equivalent-in-SAS-base-or-proc-s... 

[ballardw]

---

@david999 wrote:

sum(hours_watched) over (partition by user_id, content_title order by calendar_date) AS cumulative_hours,
____
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.

Can we use partitions inside Proc Sql, if not could you please help me the equivalent logic to use?

---

Almost every flavor of SQL has extensions to the basic idea SQL. SAS is a bit closer to the basic ANSI SQL definitions than most where most of its extensions are in functions available not statement constructs. This could well be that SQL is just one of a host of tools. Many things that some data base systems need to do with their SQL are done by SAS with other procedures or data step code.

If you are connecting to a different data base you can use the local flavor of SQL with PASS THROUGH coding that has the statements executed by the other data base.

[LinusH]

Just want to add that SQS SQL is based on ANSI 1992, whereas window functions where added in ANSI 2003.

[Kurt_Bremser]

What is your intention?

Sums for groups can easily be calculated in PROC SUMMARY.

[david999]

Hello Kurt,

I'm try to get the cumulative watched hours of the user for a particular show. I'll be using this field (cumulative_watched_hours) in the later part of the code

For eg. if a user X watched two shows A and B for 2hrs and 3 hrs respectively on 13 Apr and again he watched those shows for 5hrs and 6 hrs respectively. Now I'm trying to get watched hours grouped by userid and content title, in the above case the output should look like

Sum user Title
7 X A
9 X B

[Kurt_Bremser · Accepted solution]

Editor's Note: Marking this as accepted. Thanks @Kurt_Bremser . Also ,the approach described by @Tom  is  another way to  accumulate the sums.

Simple approach, use the SUMMARY procedure:

proc summary data=have nway;
class user title;
var hours_watched;
output
  out=want
  sum()=sum
;
run;

[Tom]

If you want to generate cumulative sums use a retained variable in a data step.  Don't bother to try to use SQL for something like that. SQL was not intended to have to process data row-by-row (it operates of SETS).  Which is why they had to add that complicated windowing language to describe our to trick SQL to operating on ordered records instead of sets.

data have;
  input user $ show  $ date :date. hours;
  format date date9.;
cards;
X A 13APR2021 2
X A 01MAY2021 5
X B 13APR2021 2
Z B 01MAY2021 5
;

data want;
  set have ;
  by user show date;
  if first.show then cum_hours=0;
  cum_hours+hours;
run;

Results:

                                              cum_
Obs    user    show         date    hours    hours

 1      X       A      13APR2021      2        2
 2      X       A      01MAY2021      5        7
 3      X       B      13APR2021      2        2
 4      Z       B      01MAY2021      5        5