topic Re: Fill missing dates and set week number in New SAS User

Fill missing dates and set week number

NewUsrStat — Tue, 02 Jul 2024 09:23:16 GMT

Hi guys,

suppose to have the following:

data DB;
  input Admission :date09. Number;
  format Admission date9.;
cards;
15JUN2014   4
20JUL2014   2
11JAN2015   1
18JAN2015   2
;run;

This dataset contains number of events by dates. Each date starts the first day of the week, so Sunday.

I would like to add the week number for each date and a variable that indicates the year (it is ok by using "year" function. I can do this). Moreover, I would like to fill all the remaining weeks (that here are not shown because no events are present) back and forth with respect to each date.

The desired output should be the following (indicative output is shown for simplicity):

data DB1;
  input Admission :date09. Number Week_Number year;
  format Admission date9.;
cards;
30DEC2013   0    1   2014
06JAN2014   0    2   2014
13JAN2014   0    3   2014
.........................
15JUN2014   4   24   2014
20JUL2014   2   29   2014 
.........................
Last week of 2014
First week of 2015
........................
11JAN2015   1    2   2015
18JAN2015   2    3   2015
.........................
Last week of 2015
;run;

Can anyone help me please?

Thank you in advance!

Re: Fill missing dates and set week number

Mazi — Tue, 02 Jul 2024 10:24:14 GMT

Hi There,

When you say "Moreover, I would like to fill all the remaining weeks (that here are not shown because no events are present) back and forth with respect to each date." does this mean you want to create a dataset with all weeks ranging from 2014 up to 2015 with one record per week for each month?

Re: Fill missing dates and set week number

PaigeMiller — Tue, 02 Jul 2024 10:25:33 GMT

The problem with week numbers is that every year has 52 and a fraction weeks. That fractional week is partly in the previous year and partly in the current year. You can choose to deal with this any way you'd like, but I choose to deal with the Sundays rather than the week numbers. Also, is week 38 in August? People can't easily translate week number in their minds to calendar information. Make you life simpler and don't deal with week numbers, use the date of the Sunday start of the week.

Here is code so you can get all your Sundays into one data set.

data all_sundays;
    do admission = '30DEC2013'd to '01JAN2016'd by 7;
        output;
    end;
    format admission date9.;
run;

data want;
    merge db all_sundays;
    by admission;
run;

Re: Fill missing dates and set week number

NewUsrStat — Tue, 02 Jul 2024 10:29:31 GMT

Yes, exactly!

Re: Fill missing dates and set week number

Mazi — Tue, 02 Jul 2024 10:47:08 GMT

data all_sundays;
	format date date9.;
	dcl hash _h_();
			 _h_.definekey('date');
			 _h_.definedone();
    do year = 2013 to 2016;
		do month=1 to 12;
			do week=1 to 5;
				date=nwkdom(week, 1, month, year);
				week_of_month=intck('week', intnx('month',date, 0), date) +1; 
				if _h_.check() then do;
					output;
					_h_.add();
				end;
				else continue;
			end;
		end;
    end;
run;

In that case, can you give this a try?