/* 1. Le tri est obligatoire pour utiliser BY */
proc sort data=transactions;
by customer_id trans_date;
run;
/* 2. Le Data Step */
data want;
set transactions;
by customer_id;
/* Si c'est la première ligne du client, on remet le compteur à 1 */
if first.customer_id then row_num = 1;
/* Sinon, on incrémente */
else row_num + 1;
run;
1
/* 1. Le tri est obligatoire pour utiliser BY */
2
PROC SORTDATA=transactions;
3
BY customer_id trans_date;
4
RUN;
5
6
/* 2. Le Data Step */
7
DATA want;
8
SET transactions;
9
BY customer_id;
10
11
/* Si c'est la première ligne du client, on remet le compteur à 1 */
12
IF first.customer_id THEN row_num = 1;
13
/* Sinon, on incrémente */
14
ELSE row_num + 1;
15
RUN;
Ejemplo: Replicar un SUM() Acumulativo
En SQL estándar:SUM(amount) OVER (PARTITION BY customer_id ORDER BY date)
data want;
set transactions;
by customer_id;
/* RETAIN garde la valeur de la ligne précédente */
retain running_total;
if first.customer_id then running_total = amount;
else running_total = running_total + amount;
run;
1
DATA want;
2
SET transactions;
3
BY customer_id;
4
5
/* RETAIN garde la valeur de la ligne précédente */
proc sql;
connect to oracle (user=... pass=... path=...);
create table want as
select * from connection to oracle
(
/* Ici, vous pouvez utiliser toute la syntaxe Oracle,
y compris les fonctions de fenêtrage */
SELECT
customer_id,
trans_date,
RANK() OVER (PARTITION BY customer_id ORDER BY trans_date DESC) as rang
FROM
schema.transactions
);
disconnect from oracle;
quit;
1
PROC SQL;
2
connect to oracle (user=... pass=... path=...);
3
4
create TABLE want as
5
select * from connection to oracle
6
(
7
/* Ici, vous pouvez utiliser toute la syntaxe Oracle,
8
y compris les fonctions de fenêtrage */
9
SELECT
10
customer_id,
11
trans_date,
12
RANK() OVER (PARTITION BY customer_id ORDER BY trans_date DESC) as rang
13
FROM
14
schema.transactions
15
);
16
17
disconnect from oracle;
18
QUIT;
Ventaja: Utiliza la sintaxis SQL que ya conoce y aprovecha la potencia del servidor de la base de datos.
proc rank data=transactions out=want descending;
by customer_id; /* Équivalent du PARTITION BY */
var amount; /* La variable sur laquelle on classe */
ranks my_rank; /* Le nom de la nouvelle colonne */
run;
1
PROC RANKDATA=transactions out=want descending;
2
BY customer_id; /* Équivalent du PARTITION BY */
3
var amount; /* La variable sur laquelle on classe */
4
ranks my_rank; /* Le nom de la nouvelle colonne */
Quédese con PROC SQL estándar (sin funciones de ventana).
Important Disclaimer
The codes and examples provided on WeAreCAS.eu are for educational purposes. It is imperative not to blindly copy-paste them into your production environments. The best approach is to understand the logic before applying it. We strongly recommend testing these scripts in a test environment (Sandbox/Dev). WeAreCAS accepts no responsibility for any impact or data loss on your systems.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.
This site uses technical and analytical cookies to improve your experience.
Read more.
