topic Re: Copying greatest value row to all other rows within a 4 week interval (4 rows) in SAS Programming

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

edison83 — Wed, 29 Mar 2023 15:13:24 GMT

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

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

Reeza — Wed, 29 Mar 2023 15:23:12 GMT

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

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

Kurt_Bremser — Wed, 29 Mar 2023 15:26:21 GMT

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?

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

edison83 — Wed, 29 Mar 2023 15:51:46 GMT

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!

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

edison83 — Wed, 29 Mar 2023 16:01:29 GMT

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!

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

Reeza — Wed, 29 Mar 2023 16:07:39 GMT

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;

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

edison83 — Wed, 29 Mar 2023 17:24:56 GMT

Thanks, that worked!

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

edison83 — Mon, 03 Apr 2023 19:10:18 GMT

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;

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

Kurt_Bremser — Mon, 03 Apr 2023 20:46:27 GMT

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

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

edison83 — Mon, 03 Apr 2023 20:50:20 GMT

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

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

Kurt_Bremser — Tue, 04 Apr 2023 05:50:30 GMT

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.

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

edison83 — Tue, 04 Apr 2023 14:50:30 GMT

Thanks Kurt, that worked just fine!