Copying greatest value row to all other rows within a 4 week interval (4 rows)

[edison83]

Hi everyone, I have the following table below, where each row is a weekly event.

HAVE				WANT
year	week	Count		year	week	Count
2022	41	872		2022	41	872
2022	42	0		2022	42	872
2022	43	0		2022	43	872
2022	44	0		2022	44	872
2022	45	726		2022	45	726
2022	46	0		2022	46	726
2022	47	0		2022	47	726
2022	48	0		2022	48	726
2022	49	762		2022	49	762
2022	50	35		2022	50	762
2022	51	0		2022	51	762
2022	52	0		2022	52	762
2023	1	830		2023	1	830
2023	2	12		2023	2	830
2023	3	1		2023	3	830
2023	4	0		2023	4	830

Some weeks contains a smaller value and other weeks are assigned to zero or smaller values.

Is there any way to copy the greatest value between the 4 week interval to all rows within this interval?

Let`s say that from week 1-4 select the greatest value will be copied to all other rows in this interval.

That will continue until the end of the year (until week 52)

Best regards

[Reeza]

data want;

array p{0:3} _temporary_;
set have; 
by year;

if first.year then 
     call missing(of p{*});

p{mod(_n_,4)} = count;
count_want= max(of p{*});

run;

---

@edison83 wrote:

Hi everyone, I have the following table below, where each row is a weekly event.

HAVE				WANT
year	week	Count		year	week	Count
2022	41	872		2022	41	872
2022	42	0		2022	42	872
2022	43	0		2022	43	872
2022	44	0		2022	44	872
2022	45	726		2022	45	726
2022	46	0		2022	46	726
2022	47	0		2022	47	726
2022	48	0		2022	48	726
2022	49	762		2022	49	762
2022	50	35		2022	50	762
2022	51	0		2022	51	762
2022	52	0		2022	52	762
2023	1	830		2023	1	830
2023	2	12		2023	2	830
2023	3	1		2023	3	830
2023	4	0		2023	4	830

Some weeks contains a smaller value and other weeks are assigned to zero or smaller values.

Is there any way to copy the greatest value between the 4 week interval to all rows within this interval?

Let`s say that from week 1-4 select the greatest value will be copied to all other rows in this interval.

That will continue until the end of the year (until week 52)

Best regards

---

[Kurt_Bremser]

So the intervals are always weeks 1-4, 5-8, 9-12, and so on?

And do you do this in SAS DI Studio, or in a code node in EG or SAS Studio?

[edison83]

Hi Kurt, correct!

The thing is that in my complete dataset I have 3 years of data, so the loop from week 1-52 repeats three times.

I also may be able to replace the weeks by a column with the respective month if that helps.

For example the week 1-4 of 2023 in my example would all be month=1 ( a single column)

I am doing this in SAS studio by the way. Thanks!

[edison83]

Hi Kurt, correct!

The thing is that in my complete dataset I have 3 years of data, so the loop from week 1-52 repeats three times.



I also may be able to replace the weeks by a column with the respective month if that helps.

For example the week 1-4 of 2023 in my example would all be month=1 ( a single column)



I am doing this in SAS studio by the way. Thanks!

[Reeza · Accepted solution]

And another way:

proc sql;
create table want as
select *, floor((week-1)/4) as week_group, max(count) as max_count
from have 
group by year, week_group;
quit;

[edison83]

Thanks, that worked!

[edison83]

Hi Kurt, I saw another post where you wrote a macro for doing something similar to this.

Do you know if we could do a macro for the code that our friend mentioned above?

data want;

array p{0:3} _temporary_;
set have; 
by year;

if first.year then 
     call missing(of p{*});

p{mod(_n_,4)} = count;
count_want= max(of p{*});

run;

[Kurt_Bremser]

Which part(s) of the code should be dynamic?

[edison83]

Sorry for not specifying.

I want to repeat the same code but for 10 more columns.

so it would be columns: "Count", "Count2", "Count3",... all the way to column "Count10".

[Kurt_Bremser]

Transpose by year and week for all count: variables.

Sort by _name_, year and week.

Run the same windowing code (by _name_) for variable col1.

Consider keeping your data in a long layout anyway.

[edison83]

Thanks Kurt, that worked just fine!