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
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;
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
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?
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!
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;
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;
Which part(s) of the code should be dynamic?
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".
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.
Thanks Kurt, that worked just fine!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.