Bonjour,
Comment calculer la moyenne d'une variable dans une table sas, sur les 7 jours précédant chaque jour férié de l'année?
Dans ce cas, voici deux méthodes pour faire ce calcul:
data have;
input date yymmdd10. x;
format date yymmdd10.;
datalines;
2023-01-01 0.12
2023-01-02 0.68
2023-01-03 0.90
2023-01-04 0.10
2023-01-05 0.59
2023-01-06 0.50
2023-01-07 0.04
2023-01-08 0.83
2023-01-09 0.25
2023-01-10 0.05
2023-01-11 0.01
2023-01-12 0.42
2023-01-13 0.82
2023-01-14 0.85
2023-01-15 0.69
2023-01-16 0.18
2023-01-17 0.07
2023-01-18 0.42
2023-01-19 0.40
2023-01-20 0.10
2023-01-21 0.59
2023-01-22 0.41
2023-01-23 0.74
2023-01-24 0.35
2023-01-25 1.00
2023-01-26 0.53
2023-01-27 0.73
2023-01-28 0.11
2023-01-29 0.18
2023-01-30 0.44
2023-01-31 0.36
2023-02-01 0.08
2023-02-02 0.16
2023-02-03 0.57
2023-02-04 0.97
2023-02-05 0.24
2023-02-06 0.73
2023-02-07 0.05
2023-02-08 0.27
2023-02-09 0.79
2023-02-10 0.46
2023-02-11 0.51
2023-02-12 0.80
2023-02-13 0.06
2023-02-14 0.26
2023-02-15 0.26
2023-02-16 0.30
2023-02-17 0.62
2023-02-18 0.38
2023-02-19 0.09
2023-02-20 0.44
2023-02-21 0.46
2023-02-22 0.39
2023-02-23 0.09
2023-02-24 0.98
2023-02-25 0.04
2023-02-26 0.85
2023-02-27 0.94
2023-02-28 0.03
2023-03-01 0.37
2023-03-02 0.50
2023-03-03 0.27
;
/* Liste des jours fériés */
data ferie;
input dateFerie yymmdd10.;
format dateFerie yymmdd10.;
datalines;
2023-01-01
2023-01-21
2023-02-14
;
/* Solution sans SQL */
data temp1(keep = dateFerie date);
set ferie;
do date = dateFerie-7 to dateFerie-1;
output;
end;
run;
data temp2;
merge
have
temp1 (in=garder);
by date;
if garder;
run;
proc summary data=temp2;
var x;
by dateFerie;
output out=want1(where=(nManque=0)) mean=xMoy nMiss=nManque;
run;
/* Solution avec SQL */
proc sql;
create table want2 as
select
a.dateFerie label="Jour férié",
mean(b.x) as xMoy label="Moy 7 jours"
from
ferie as a inner join
have as b on b.date between a.dateFerie-7 and a.dateFerie-1
group by a.dateFerie
having count(b.x) = 7;
select * from want2;
quit;
Avec les mêmes résultats que ci-haut.
Comment comptez-vous identifier les jours fériés? SVP, donnez un petit exemple de vos données.
Voici un exemple avec des données simulées:
data have;
input date yymmdd10. x ferie;
format date yymmdd10.;
datalines;
2023-01-01 0.12 1
2023-01-02 0.68 0
2023-01-03 0.90 0
2023-01-04 0.10 0
2023-01-05 0.59 0
2023-01-06 0.50 0
2023-01-07 0.04 0
2023-01-08 0.83 0
2023-01-09 0.25 0
2023-01-10 0.05 0
2023-01-11 0.01 0
2023-01-12 0.42 0
2023-01-13 0.82 0
2023-01-14 0.85 0
2023-01-15 0.69 0
2023-01-16 0.18 0
2023-01-17 0.07 0
2023-01-18 0.42 0
2023-01-19 0.40 0
2023-01-20 0.10 0
2023-01-21 0.59 1
2023-01-22 0.41 0
2023-01-23 0.74 0
2023-01-24 0.35 0
2023-01-25 1.00 0
2023-01-26 0.53 0
2023-01-27 0.73 0
2023-01-28 0.11 0
2023-01-29 0.18 0
2023-01-30 0.44 0
2023-01-31 0.36 0
2023-02-01 0.08 0
2023-02-02 0.16 0
2023-02-03 0.57 0
2023-02-04 0.97 0
2023-02-05 0.24 0
2023-02-06 0.73 0
2023-02-07 0.05 0
2023-02-08 0.27 0
2023-02-09 0.79 0
2023-02-10 0.46 0
2023-02-11 0.51 0
2023-02-12 0.80 0
2023-02-13 0.06 0
2023-02-14 0.26 1
2023-02-15 0.26 0
2023-02-16 0.30 0
2023-02-17 0.62 0
2023-02-18 0.38 0
2023-02-19 0.09 0
2023-02-20 0.44 0
2023-02-21 0.46 0
2023-02-22 0.39 0
2023-02-23 0.09 0
2023-02-24 0.98 0
2023-02-25 0.04 0
2023-02-26 0.85 0
2023-02-27 0.94 0
2023-02-28 0.03 0
2023-03-01 0.37 0
2023-03-02 0.50 0
2023-03-03 0.27 0
;
proc sql;
create table want as
select
a.date as dateFerie label="Jour férié",
mean(b.x) as xMoy label="Moy 7 jours"
from
have as a inner join
have as b on b.date between a.date-7 and a.date-1
where a.ferie
group by a.date
having count(b.x) = 7;
select * from want;
quit;
Les mêmes calculs peuvent aussi s'effectuer avec des datasteps ou avec proc expand, mais la requête SQL est la méthode la plus immédiate.
Où sont vos jours fériés?
Dans ce cas, voici deux méthodes pour faire ce calcul:
data have;
input date yymmdd10. x;
format date yymmdd10.;
datalines;
2023-01-01 0.12
2023-01-02 0.68
2023-01-03 0.90
2023-01-04 0.10
2023-01-05 0.59
2023-01-06 0.50
2023-01-07 0.04
2023-01-08 0.83
2023-01-09 0.25
2023-01-10 0.05
2023-01-11 0.01
2023-01-12 0.42
2023-01-13 0.82
2023-01-14 0.85
2023-01-15 0.69
2023-01-16 0.18
2023-01-17 0.07
2023-01-18 0.42
2023-01-19 0.40
2023-01-20 0.10
2023-01-21 0.59
2023-01-22 0.41
2023-01-23 0.74
2023-01-24 0.35
2023-01-25 1.00
2023-01-26 0.53
2023-01-27 0.73
2023-01-28 0.11
2023-01-29 0.18
2023-01-30 0.44
2023-01-31 0.36
2023-02-01 0.08
2023-02-02 0.16
2023-02-03 0.57
2023-02-04 0.97
2023-02-05 0.24
2023-02-06 0.73
2023-02-07 0.05
2023-02-08 0.27
2023-02-09 0.79
2023-02-10 0.46
2023-02-11 0.51
2023-02-12 0.80
2023-02-13 0.06
2023-02-14 0.26
2023-02-15 0.26
2023-02-16 0.30
2023-02-17 0.62
2023-02-18 0.38
2023-02-19 0.09
2023-02-20 0.44
2023-02-21 0.46
2023-02-22 0.39
2023-02-23 0.09
2023-02-24 0.98
2023-02-25 0.04
2023-02-26 0.85
2023-02-27 0.94
2023-02-28 0.03
2023-03-01 0.37
2023-03-02 0.50
2023-03-03 0.27
;
/* Liste des jours fériés */
data ferie;
input dateFerie yymmdd10.;
format dateFerie yymmdd10.;
datalines;
2023-01-01
2023-01-21
2023-02-14
;
/* Solution sans SQL */
data temp1(keep = dateFerie date);
set ferie;
do date = dateFerie-7 to dateFerie-1;
output;
end;
run;
data temp2;
merge
have
temp1 (in=garder);
by date;
if garder;
run;
proc summary data=temp2;
var x;
by dateFerie;
output out=want1(where=(nManque=0)) mean=xMoy nMiss=nManque;
run;
/* Solution avec SQL */
proc sql;
create table want2 as
select
a.dateFerie label="Jour férié",
mean(b.x) as xMoy label="Moy 7 jours"
from
ferie as a inner join
have as b on b.date between a.dateFerie-7 and a.dateFerie-1
group by a.dateFerie
having count(b.x) = 7;
select * from want2;
quit;
Avec les mêmes résultats que ci-haut.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.