topic Re: Transforming from monthly to weekly variables in SAS Programming

Transforming from monthly to weekly variables

niki0209 — Tue, 26 Sep 2023 13:56:11 GMT

I have this type of data:

person	variable_2020_01	variable_2020_02
A	1	5
B	6	8

And I need it transformed into weekly data:

person	weekly_variable_2020_01	weekly_variable_2020_02	weekly_variable_2020_03	weekly_variable_2020_04	weekly_variable_2020_05	weekly_variable_2020_06	weekly_variable_2020_07	weekly_variable_2020_08
A	1	1	1	1	5	5	5	5
B	6	6	6	6	8	8	8	8

I realise that I have two problems:

1) I don't know how to code this in a away that just assumes 4 weeks=1month

2) 4 weeks doesn't always equal 1 month

So I am hoping that someone has experience with this dilemma and is able to help me.

Thank a lot! 🙂

Re: Transforming from monthly to weekly variables

PaigeMiller — Tue, 26 Sep 2023 14:00:09 GMT

There is no exact unambiguous and clearly agreed upon method of turning months into weeks. If you can state, in words, exactly how you want to do it, we can help code it for you.

The whole thing would be easier if months and weeks were stored as data in the variables, instead of having months and weeks stored in variable names.

Repeating: please provide data as working SAS data step code (Examples and instructions).

Re: Transforming from monthly to weekly variables

Kurt_Bremser — Tue, 26 Sep 2023 14:09:00 GMT

Before you do anything else, transform your stupid wide data to intelligent long data, converting the interesting part of the names to SAS date values on the way. Data (dates) does not belong in structure (variable names)!

From there, you can create weekly data rather easily, but first you need to clearly define what a "week" is in your context, e.g starts on Sunday, starts on Monday, or starts on a given day (e.g. the first of a month) using a 7-day interval from there. If a week starts on a fixed weekday, how do you allocate partial weeks to months?