calculating averages between dates to include saturday and sunday

[PPPiraneo]

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 !!

[Astounding]

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.

[mkeintz]

---

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

[Astounding]

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

[Ksharp]

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;

[PGStats]

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

[lc_isp]

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.