topic Re: calculating averages between dates to include saturday and sunday in SAS Programming

calculating averages between dates to include saturday and sunday

PPPiraneo — Fri, 14 Oct 2022 13:39:35 GMT

Hi everyone
could you halp me !?!?!
I have a question regarding calculating averages between dates in my dataset.
this is an example of my dataset:
DTCONT CTV_SALDO
01SEP2022 145.026,70
02SEP2022 146.204,88

05SEP2022 147.946,40
06SEP2022 147.999.752,70
07SEP2022 152.085.201,11
08SEP2022 154.195,98
09SEP2022 149.789.820,12

12SEP2022 141.755.850,87
13SEP2022 140.970.282,19
14SEP2022 143.708,69
15SEP2022 140.896.452,76
16SEP2022 138.298.019,82

...

30SEP2022 141.237,33

in my dataset there isn't saturady and sunday; i had to include !!
the balance of saturday (3th) and sunday (4th) is the same of friday (2th)
i had to calculate an average based on mounth (e.s september, 30 days).
so i had to sum the balances of each day of the mounth.
the problem is: my extraction is without saturday and sunday (but also there isn't the 25th december, 1° january, ect.).
so, i want to realize this table:

Date CTV_SALDO
01SEP2022 145.026,70
02SEP2022 146.204,88
saturday 146.204,88 i had to insert date and import the same friday
sunday 146.204,88 i had to insert date and import the same friday
05SEP2022 147.946,40
06SEP2022 147.999.752,70
07SEP2022 152.085.201,11
08SEP2022 154.195,98
09SEP2022 149.789.820,12
saturday 149.789.820,12 i had to insert date and import the same friday
sunday 149.789.820,12 i had to insert date and import the same friday
...

...
30SEP2022 141.237,33
avarage
to calculate --> ______________ sum balances in each day/30
is there a command to insert saturday and sunday?
at the same time, i had to insert the same balance of friday in saturday and sunday

thanks for you helping !!

Re: calculating averages between dates to include saturday and sunday

Astounding — Mon, 24 Oct 2022 02:35:16 GMT

If your dates are SAS dates and not character strings, inserting Saturday and Sunday should be easy:

data want;
   set have;
   if weekday(dtcont) = 6 then do;   /* identify Fridays */
      dtcont + 1;   /* add Saturday using Friday data */
      output;
      dtcont + 1;   /* add Sunday using Friday data */
      output;
   end;
run;

To handle other missing points, it would be easiest if you would actually supply a list of the missing days. "Etc." is not specific enough.

The idea here is to create a complete data set with all the dates present. Calculating monthly statistics would be easy at that point, but post again if that part gives you any trouble.

Re: calculating averages between dates to include saturday and sunday

Ksharp — Mon, 24 Oct 2022 11:54:49 GMT

data have;
input DTCONT : date9. CTV_SALDO : commax32.;
format DTCONT  date9. CTV_SALDO  commax32.2;
cards;
01SEP2022 145.026,70
02SEP2022 146.204,88
05SEP2022 147.946,40
06SEP2022 147.999.752,70
07SEP2022 152.085.201,11
08SEP2022 154.195,98
09SEP2022 149.789.820,12
12SEP2022 141.755.850,87
13SEP2022 140.970.282,19
14SEP2022 143.708,69
15SEP2022 140.896.452,76
16SEP2022 138.298.019,82
;

data want;
 merge have have(firstobs=2 keep=DTCONT rename=(DTCONT=_DTCONT));
 output;
 do DTCONT=DTCONT+1 to ifn(missing(_DTCONT),0,_DTCONT-1);
  output;
 end;
 drop _DTCONT;
run;

Re: calculating averages between dates to include saturday and sunday

PGStats — Mon, 24 Oct 2022 19:06:24 GMT

Very neat @Ksharp , but you might want to use

do DTCONT = DTCONT + 1 to coalesce(_DTCONT, DTCONT) - 1;

instead, to avoid getting note in the LOG and to refer to zero as a date.

Cheers

Re: calculating averages between dates to include saturday and sunday

mkeintz — Mon, 24 Oct 2022 23:42:45 GMT

@Astounding wrote:

If your dates are SAS dates and not character strings, inserting Saturday and Sunday should be easy:
data want;
   set have;
   if weekday(dtcont) = 6 then do;   /* identify Fridays */
      dtcont + 1;   /* add Saturday using Friday data */
      output;
      dtcont + 1;   /* add Sunday using Friday data */
      output;
   end;
run;
To handle other missing points, it would be easiest if you would actually supply a list of the missing days. "Etc." is not specific enough.

The idea here is to create a complete data set with all the dates present. Calculating monthly statistics would be easy at that point, but post again if that part gives you any trouble.

@Astounding :

This needs another OUTPUT statement. As is, it only outputs Sats and Suns.

Welcome to the club.

Re: calculating averages between dates to include saturday and sunday

Astounding — Tue, 25 Oct 2022 02:37:58 GMT

Good catch! That can happen when I post after 10 p.m.

Re: calculating averages between dates to include saturday and sunday

lc_isp — Mon, 28 Nov 2022 12:38:58 GMT

Very elegant solution, congratulations. With my colleague PPPiraneo we had to deal with some randomly missing data too, which wasn't included into his initial post, but your solution deals with 99% of the problem anyway.