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 ... View more

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.   ... View more

@Patrick @mkeintz  Thank you so much for your help! ... View more

@mkeintz Thanks! I tried the following code. The issue that I am facing is that for non-matching observations the value for for week_volume is still missing.  PROC SQL; create table Want as SELECT ABC.*, DEF.* COALESCE(DEF.Week_Volume,0) as Week_Volume, FROM ABC left join DEF ON DEF.MergeName=ABC.brand_name and DEF.Year=ABC.year0 and DEF.GovWeek=ABC.week; QUIT;  However, when I create a new variable (instead of replacing the missing values in the original variable), it seems to give me what I want (i.e. zero for non-matching observations): PROC SQL; create table Want as SELECT ABC.*, DEF.* COALESCE(DEF.Week_Volume,0) as Week_Volume2, FROM ABC left join DEF ON DEF.MergeName=ABC.brand_name and DEF.Year=ABC.year0 and DEF.GovWeek=ABC.week; QUIT; Given that I want to repeat the same thing for a lot of other variables too, it is really tedious to create new variables for each and then drop the original variables. Can you please tell me what the issue is with my first code above? Thanks again!  ... View more

@mkeintz Thanks so much. I was a bit confused about one aspect of your code. Let me use an example:   Let's say the ABC dataset (sales data) that I mentioned, includes three companies: A, B, C.  The DEF dataset (ad expenditure data) includes two companies: A, B (as I said earlier, the companies in DEF are a subset of those in ABC). Both datasets include data on 100 weeks. For company A, ad expenditure and sales data for all 100 weeks are available in the datasets. For company B, 100 weeks of sales data is available in ABC, but only, say, 75 weeks of ad expenditure data is available in DEF (as I said, maybe company B didn't spend anything on advertising during the other 25 weeks and the dataset builder just ignored those weeks, instead of including those weeks and assigning a zero value). What I want to have in my merged dataset is: --100 rows for company A, which would include sales data for 100 weeks and ad expenditure data for 100 weeks. --100 rows for company B, which would include sales data for 100 weeks and ad expenditure data for 75 weeks and assign zero to ad expenditure for the 25 weeks missing in the DEF dataset. --0 rows for company C.   Do the codes that you kindly provided give such an output? Thanks again! ... View more