Obsidian | Level 7

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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;``````
11 REPLIES 11
Super User

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

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

Super User

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

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?

Obsidian | Level 7

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

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!

Obsidian | Level 7

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

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

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

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;``````
Obsidian | Level 7

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

Thanks, that worked!
Obsidian | Level 7

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

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;``````

Super User

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

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

Obsidian | Level 7

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

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

Super User

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

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.

Obsidian | Level 7

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

Thanks Kurt, that worked just fine!

Discussion stats
• 11 replies
• 1148 views
• 6 likes
• 3 in conversation