topic Re: SAS BASE - how can i perform Over Partition in SAS Programming

SAS BASE - how can i perform Over Partition

gabras — Tue, 07 Aug 2018 18:04:25 GMT

Hi,

i have to write sas code that simulate an sql over partition.

the following code is what i would like to convert in SAS:

select * ,
COUNT(*) OVER (PARTITION BY CUST_ID) AS COUNT_overPart,
AVG(dayDiff) OVER (PARTITION BY CUST_ID) AS meanDayContact_overPart,
SUM(InStore) OVER (PARTITION BY CUST_ID) AS countInStore_overPart,
AVG(case when InStore = 1 then dayDiff else 0 end) OVER (PARTITION BY CUST_ID)end AS meanDay_InStore_overPart
from HAVE

Here is my HAVE table

ID	CUST_ID	DATE	Source	OBJ	ord	prev_date	dayDiff	prev_obj	prev_Source	InStore
1	1	03AUG2018	Mail	A	1	03AUG2018	0	A	Mail	0
2	1	06AUG2018	Mail	A	2	03AUG2018	3	A	Mail	0
3	1	06AUG2018	Fax	A	3	06AUG2018	0	A	Mail	0
4	2	06AUG2018	Tel	E	1	06AUG2018	0	E	Tel	0
5	2	06AUG2018	Store	F	2	06AUG2018	0	E	Tel	1
6	3	06AUG2018	Tel	B	1	06AUG2018	0	B	Tel	0
7	3	06AUG2018	Tel	B	2	06AUG2018	0	B	Tel	0
8	4	06AUG2018	Tel	C	1	06AUG2018	0	C	Tel	0
9	4	06AUG2018	Store	D	2	06AUG2018	0	C	Tel	1

and i want this table:

ID	CUST_ID	DATE	Source	OBJ	ord	prev_date	dayDiff	prev_obj	prev_Source	InStore	COUNT_overPart	meanDayContact_overPart	countInStore_overPart	meanDay_InStore_overPart
1	1	03AUG2018	Mail	A	1	03AUG2018	0	A	Mail	0	3	1	0	0
2	1	06AUG2018	Mail	A	2	03AUG2018	3	A	Mail	0	3	1	0	0
3	1	06AUG2018	Fax	A	3	06AUG2018	0	A	Mail	0	3	1	0	0
4	2	06AUG2018	Tel	E	1	06AUG2018	0	E	Tel	0	2	0	1	0
5	2	06AUG2018	Store	F	2	06AUG2018	0	E	Tel	1	2	0	1	0
6	3	06AUG2018	Tel	B	1	06AUG2018	0	B	Tel	0	2	0	0	0
7	3	06AUG2018	Tel	B	2	06AUG2018	0	B	Tel	0	2	0	0	0
8	4	06AUG2018	Tel	C	1	06AUG2018	0	C	Tel	0	2	0	1	0
9	4	06AUG2018	Store	D	2	06AUG2018	0	C	Tel	1	2	0	1	0

@Ksharp i saw a post where you used the hash table (or another solution if he had not sufficient memory) but i couldn't use your code to accomplish my task, can you help me?

Here the post:

https://communities.sas.com/t5/General-SAS-Programming/Partition-by-equivalent-in-SAS-base-or-proc-sql/td-p/415993

Thank you

Re: SAS BASE - how can i perform Over Partition

LinusH — Tue, 07 Aug 2018 20:29:33 GMT

I'm not 100% sure but I think can solved by just specifying cust_id in the GROUP BY clause, then SAS will remerge stats with detail data.

Re: SAS BASE - how can i perform Over Partition

gabras — Tue, 07 Aug 2018 20:58:31 GMT

Hi @LinusH

I think you are referring to the group by clause of an sql statement.
I tried to use sql statement, calculating the over partition variabile as (select count() from have group by custId) as count_overpart but it takes a lot of time.

Re: SAS BASE - how can i perform Over Partition

ChrisNZ — Tue, 07 Aug 2018 22:57:43 GMT

There is no partition by operator in SAS (vote if you want it).

@LinusH's suggestion is the way to go.

You may (or not) speed up the query by deriving the GROUPed BY values separately and joining, and/or by loading the table in memory using SASFILE if it's not too large.

Re: SAS BASE - how can i perform Over Partition

gabras — Wed, 08 Aug 2018 06:38:13 GMT

Hi @ChrisNZ
Thank you for your reply.

I hope this is not THE solution, but a workaround.
I think using hash table is a better solution.
@Ksharp posted a very cool way to use it (see at the bottom of my first post) but since i have never used hash table i wasn’t able to re engineering the code he wrote

Re: SAS BASE - how can i perform Over Partition

Ksharp — Wed, 08 Aug 2018 12:30:06 GMT

SAS SQL can do it too . No need Hash Table.

data have;
infile cards expandtabs truncover;
input ID	CUST_ID	DATE : $20.	Source	$ OBJ	$ ord 	prev_date :$20.	dayDiff	prev_obj $	prev_Source $	InStore;
cards;
1	1	03AUG2018	Mail	A	1	03AUG2018	0	A	Mail	0
2	1	06AUG2018	Mail	A	2	03AUG2018	3	A	Mail	0
3	1	06AUG2018	Fax	A	3	06AUG2018	0	A	Mail	0
4	2	06AUG2018	Tel	E	1	06AUG2018	0	E	Tel	0
5	2	06AUG2018	Store	F	2	06AUG2018	0	E	Tel	1
6	3	06AUG2018	Tel	B	1	06AUG2018	0	B	Tel	0
7	3	06AUG2018	Tel	B	2	06AUG2018	0	B	Tel	0
8	4	06AUG2018	Tel	C	1	06AUG2018	0	C	Tel	0
9	4	06AUG2018	Store	D	2	06AUG2018	0	C	Tel	1
;
run;

proc sql;
create table want as
select * ,
COUNT(*) AS COUNT_overPart,
AVG(dayDiff)  AS meanDayContact_overPart,
SUM(InStore)  AS countInStore_overPart,
AVG(case when InStore = 1 then dayDiff else 0 end)  AS meanDay_InStore_overPart
from HAVE
group BY CUST_ID;
quit;

Re: SAS BASE - how can i perform Over Partition

gabras — Wed, 08 Aug 2018 13:29:40 GMT

Hi @Ksharp

thank you for the reply. It works.

If you have time, can you post a solution using hash table ?

Thank you very much

Re: SAS BASE - how can i perform Over Partition

Ksharp — Wed, 08 Aug 2018 13:44:17 GMT

Why ? It is not right scenario to Hash Table.

But I would like to write some IML code if you want .

Re: SAS BASE - how can i perform Over Partition

gabras — Wed, 08 Aug 2018 15:05:34 GMT

Yes, of course.