Creating a daily dataset from a monthly dataset

[AlG]

Hi friends,

I have a dataset which includes the monthly advertising expenditure of several companies from 2014 to 2018 (needless to say, it has a year and a month column too).

What I want to do is to create a daily dataset based on this data. Specifically, I want this new dataset to have a daily ad expenditure column, which should equal the advertising expenditure of each company in that month divided by the the number of days within that month. So, for example, for company A all of the days of, say, September 2017, would have the same daily ad expenditure value, which will be equal to (September 2017 ad expenditure of company A)/30. As I said my dataset includes several different companies.

Is there any way to do this in SAS? 

Thanks a lot in advance. 

[Reeza]

---

@AlG wrote:

Is there any way to do this in SAS? 

Thanks a lot in advance. 

---

Yes, there are many ways. PROC TIMESERIES is one approach, but not everyone has SAS/ETS license. In that case use a data step. INTNX() will let you increment a month to the number of days. 

n_days_month = day(intnx('month', monthVariable, 0, 'e'));

Then you can use a DO loop to loop through the number of days and OUTPUT a record for each day. 

do i= 1 to n_days_month;
daily_amount = monthly_amount / n_days_month;
output;
end;

Hope that helps. 

[s_lassen]

First, find the first day of the month (initial value of DATE in example). Then the number of days in the month (N_DAYS). Then calculate daily expenditure (DAILY). Finally, loop through month.

Example:

data test;
  input year month expenditure;
  date=mdy(month,1,year);
  format date date9.;
  n_days=day(intnx('month',date,0,'E'));
  daily=expenditure/n_days;
  do date=date to date+n_days;
    output;
    end;
  keep date daily;
cards;
2013 1 31000
2020 2 2900
;run;

[AlG]

Thanks so much @Reeza and @s_lassen 

I am very new to SAS, so my apologies if my questions are very basic.

It seems to me that both of you are suggesting pretty much similar solutions. I tried to use what you suggested and wrote the code below (The name of my monthly dataset that I want to convert to daily is YouGov. The year variable is named year, the month variable is named month, and the advertising expenditure variable is named TV). When I run the code I get two errors. Below is my code:

data Daily;
   set Yougov;
   date=mdy(month,1,year);
  format date date9.;
  n_days=day(intnx('month',date,0,'E'));
  daily=TV/n_days;
  do date=date to date+n_days;
    output;
    end;

Below is the error:

56   data Daily;
57      set Yougov;
58      date=mdy(month,1,year);
59     format date date9.;
                   ------
                   48
ERROR 48-59: The format $DATE was not found or could not be loaded.

60     n_days=day(intnx('month',date,0,'E'));
61     daily=TV/n_days;
62     do date=date to date+n_days;
          -
          133
ERROR 133-185: A loop variable cannot be an array name or a character variable;  It must be a
               scalar numeric.

63       output;
64       end;

One last point is that, as I said before, this dataset includes several brands (so I have a column named BrandName), and I want to convert the monthly ad expenditure of each brand into daily ad expenditure. Shouldn't the column "BrandName" be somehow included in the code.

Thanks so much again and my apologies if the questions are too basic.

[s_lassen · Accepted solution]

Obviously, the problem is that you already have a (character) variable named DATE on your Yougov table. If you do not need that variable, just drop the variable when reading the table:

data Daily;
   set Yougov(drop=date); /* drop character variable DATE */
   date=mdy(month,1,year);
  format date date9.;
  n_days=day(intnx('month',date,0,'E'));
  daily=TV/n_days;
  do date=date to date+n_days;
    output;
    end;

Otherwise, call your new date variable something else.

[AlG]

Thanks so much @s_lassen 

The code you provided works. TI just noticed one issue in the output.

The code seems to generate one excessive row for each month. For example, whereas January 2014 should have 31 rows, it generates 32 rows. Then what happens is that the variable date (this is the variable date generated by the code. Per your suggestion, I dropped the original variable date) lists the 32nd row as belonging to the next month (i.e. 1-Feb-14). As a result, there are rows labeled 1-Feb-14. The same thing happens for all of the months: there are two rows for the first day of each month. Do you have any idea why thins happens?

Please note that the variables n_days and daily are correct, the issue is only with the variable date. Below are four rows from the output for illustration from September (which has 30 days), and October:

date                  n-days        daily

30-Sep-14	30	0
1-Oct-14	30	0
1-Oct-14	31	2.3516129
2-Oct-14	31	2.3516129

[Tom]

---

@AlG wrote:

Thanks so much @s_lassen 

The code you provided works. TI just noticed one issue in the output.

The code seems to generate one excessive row for each month. For example, whereas January 2014 should have 31 rows, it generates 32 rows. Then what happens is that the variable date (this is the variable date generated by the code. Per your suggestion, I dropped the original variable date) lists the 32nd row as belonging to the next month (i.e. 1-Feb-14). As a result, there are rows labeled 1-Feb-14. The same thing happens for all of the months: there are two rows for the first day of each month. Do you have any idea why thins happens?

Please note that the variables n_days and daily are correct, the issue is only with the variable date. Below are four rows from the output for illustration from September (which has 30 days), and October:

date                  n-days        daily

30-Sep-14	30	0
1-Oct-14	30	0
1-Oct-14	31	2.3516129
2-Oct-14	31	2.3516129

---

You have an arithmetic problem.  Say there are 31 days in the month.  If you code DO DAY=1 to 1+31 you will generate 32 values, not 31.

Just subtract one.

do date=date to date+n_days -1 ;