topic Partition in Proc SQL in SAS Programming

Partition in Proc SQL

david999 — Tue, 15 Jun 2021 20:15:36 GMT

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?

Re: Partition in Proc SQL

tarheel13 — Tue, 15 Jun 2021 20:37:48 GMT

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-sql/td-p/415993

Re: Partition in Proc SQL

ballardw — Tue, 15 Jun 2021 20:48:24 GMT

@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.

Re: Partition in Proc SQL

Kurt_Bremser — Wed, 16 Jun 2021 04:51:57 GMT

What is your intention?

Sums for groups can easily be calculated in PROC SUMMARY.

Re: Partition in Proc SQL

david999 — Wed, 16 Jun 2021 05:58:13 GMT

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

Re: Partition in Proc SQL

Kurt_Bremser — Tue, 17 Oct 2023 14:24:57 GMT

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;

Re: Partition in Proc SQL

LinusH — Wed, 16 Jun 2021 08:35:51 GMT

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

Re: Partition in Proc SQL

Tom — Wed, 16 Jun 2021 14:19:20 GMT

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