BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gnome
Fluorite | Level 6

Hello,

 

I am trying to convert yearly population estimates (which are made on July 1st) into weekly estimates. I am going to use this to calculate weekly incident rates for a disease. The problem is that I am using CDC or epidemiological weeks. Is there some way to get proc expand to convert to CDC weeks instead of regular calendar weeks? Here is my code:


proc expand data=test1 out=test2 from=annual to=week extrapolate method=join observed=middle ;
id date;
convert population;
run;

 

Greg

 

1 ACCEPTED SOLUTION

Accepted Solutions
TammyJackson
SAS Employee

Ok. I created a custom interval to exactly match the CDC definition. The interval starts on Sundays, and the seasonal index (1-53) matches the CDC definition. I used the custom interval in proc expand, and it worked exactly as using a standard week interval. Now, the standard week interval is a bit faster because a custom interval requires a look-up, ie. seach, whereas the week is just a simple calculation. However, you still have the problem of identifying the CDC week value. The custom interval can help with that through the use of the INTINDEX function. Another possibility (not shown) would be merging the CDC_Weeks data set and the Proc Expand out= data set by the date values. I am including my code. Let me know if you have any questions.

 

/* create a data set describing your intervals.
   Here, the year begins on the Sunday on or preceeding Jan 4.
   And the intervals are Sunday-Saturday */
data CDC_Weeks(keep=begin season year);
     start_year = 2010;
     end_year = 2020;
     do year = start_year to end_year;
        /* Sunday preceeding January 4th */
        begin_year = INTNX("WEEK",MDY(1,4,year),0);
        next_Jan_4 = MDY(1,4,year+1);
        do season = 1 to 53;
           begin = INTNX("WEEK",begin_year,season-1);
           end = INTNX("WEEK",begin,0,'E');
           if (end LT next_Jan_4) then output;
        end;
     end;
     /* use a format to indicate that these are SAS DATE values */
     format begin DATE.;
run;
title "Custom interval with Season and Year Identified";
proc print; run;

/* just some test data */
data test1(keep=date daysinyear year);
     do year=2011 to 2019;
        date=MDY(1,1,year);
        next=MDY(1,1,year+1);
        daysinyear=next-date;
        output;
     end;
     format date DATE;
run;

/* Create your own interval - connect to your definition data set */
options intervalds=(CDCWeek=CDC_Weeks);

/* You can use the custom interval in proc expand */
title "Result of using a custom interval";
proc expand data=test1 out=test2 from=annual to=CDCWeek extrapolate method=join observed=middle ;
id date;
convert daysinyear year;
run;

proc print data=test2;
run;
 
/* Compare to week. Week will be a bit faster computationally. */
proc expand data=test1 out=test3 from=annual to=week extrapolate method=join observed=middle ;
id date;
convert daysinyear year;
run;

/* Compare the custom weeks to the standard weeks. */
proc compare base=test2 compare=test3;
run;

/* Use the custom interval to retrieve the proper week value */
title "Output with CDC Weeks Identified";
data test3;
     set test3;
     /* Will use the SEASON from CDCWeek custom interval */
     CDCWeek=INTINDEX("CDCWeek",date);
run;
proc print;run;

 

View solution in original post

4 REPLIES 4
ballardw
Super User

You may have to explain how a CDC week differs from a calendar week.

Gnome
Fluorite | Level 6

CDC weeks are numbered from one to 52 or 53. They start on Sunday. The first week of the year begins on the first calendar week that has at least four days in the new year. So, the Sunday in week 1 may land on Dec 29th, 30th, or 31st of the previous year. Every CDC week has 7 days and their can be 53 weeks.

 

PROC EXPAND TO=WEEK calculates to weeks ending on Sunday. So, I should be able to use this as long as I format it with WEEKUw. The problem is that WEEKUw. starts week 1 on the first Sunday of the new year and includes week 0 for days of the new year before the first Sunday. So, there are weeks without 7 days which are made up for with week 53 in the previous year.

TammyJackson
SAS Employee

Ok. I created a custom interval to exactly match the CDC definition. The interval starts on Sundays, and the seasonal index (1-53) matches the CDC definition. I used the custom interval in proc expand, and it worked exactly as using a standard week interval. Now, the standard week interval is a bit faster because a custom interval requires a look-up, ie. seach, whereas the week is just a simple calculation. However, you still have the problem of identifying the CDC week value. The custom interval can help with that through the use of the INTINDEX function. Another possibility (not shown) would be merging the CDC_Weeks data set and the Proc Expand out= data set by the date values. I am including my code. Let me know if you have any questions.

 

/* create a data set describing your intervals.
   Here, the year begins on the Sunday on or preceeding Jan 4.
   And the intervals are Sunday-Saturday */
data CDC_Weeks(keep=begin season year);
     start_year = 2010;
     end_year = 2020;
     do year = start_year to end_year;
        /* Sunday preceeding January 4th */
        begin_year = INTNX("WEEK",MDY(1,4,year),0);
        next_Jan_4 = MDY(1,4,year+1);
        do season = 1 to 53;
           begin = INTNX("WEEK",begin_year,season-1);
           end = INTNX("WEEK",begin,0,'E');
           if (end LT next_Jan_4) then output;
        end;
     end;
     /* use a format to indicate that these are SAS DATE values */
     format begin DATE.;
run;
title "Custom interval with Season and Year Identified";
proc print; run;

/* just some test data */
data test1(keep=date daysinyear year);
     do year=2011 to 2019;
        date=MDY(1,1,year);
        next=MDY(1,1,year+1);
        daysinyear=next-date;
        output;
     end;
     format date DATE;
run;

/* Create your own interval - connect to your definition data set */
options intervalds=(CDCWeek=CDC_Weeks);

/* You can use the custom interval in proc expand */
title "Result of using a custom interval";
proc expand data=test1 out=test2 from=annual to=CDCWeek extrapolate method=join observed=middle ;
id date;
convert daysinyear year;
run;

proc print data=test2;
run;
 
/* Compare to week. Week will be a bit faster computationally. */
proc expand data=test1 out=test3 from=annual to=week extrapolate method=join observed=middle ;
id date;
convert daysinyear year;
run;

/* Compare the custom weeks to the standard weeks. */
proc compare base=test2 compare=test3;
run;

/* Use the custom interval to retrieve the proper week value */
title "Output with CDC Weeks Identified";
data test3;
     set test3;
     /* Will use the SEASON from CDCWeek custom interval */
     CDCWeek=INTINDEX("CDCWeek",date);
run;
proc print;run;

 

Gnome
Fluorite | Level 6
Thanks! This helps a lot!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 3036 views
  • 4 likes
  • 3 in conversation