topic Re: How to format date in fortnightly interval with a user defined starting dat in SAS Procedures

How to format date in fortnightly interval with a user defined starting dat

deleted_user — Tue, 15 Dec 2009 03:13:22 GMT

Hi

How to format date in fortnightly interval with a user defined starting date (e.g. 1 Jan 2009), similar to the format listed below?

SAS Date Annual YEAR4. 1991
Quarterly YYQC6. 1991:4
Monthly MONYY7. OCT1991
Weekly WEEKDATX23. Thursday, 17 Oct 1991

Re: How to format date in fortnightly interval with a user defined starting dat

Cynthia_sas — Tue, 15 Dec 2009 05:17:06 GMT

Hi:
Do you mean that you have a user-defined starting date and you want to advance the date by 14 days (a fortnight)? Or that you want to advance the date to some particular day in the 2-week period? Do you want to do this one time? or once for every observation or generate dates 2 weeks apart for a fixed period of time or fixed number of intervals?

If you need to advance the date by -exactly- 14 days and your starting date is a SAS date variable, then you could either use the INTNX function or you could simply add 14 to the starting date. The advantage of the INTNX function is that you can advance a date by a certain interval and pick the beginning of the fortnight week, the middle of the fortnight week or the end of the fortnight week or the same date to end the fortnight on. Described here in the doc and papers:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000212700.htm
http://www2.sas.com/proceedings/sugi31/027-31.pdf
http://www2.sas.com/proceedings/forum2007/226-2007.pdf

cynthia

Re: How to format date in fortnightly interval with a user defined starting dat

deleted_user — Tue, 15 Dec 2009 06:40:44 GMT

Hi Cynthia

Many thanks for your reply.

I have a SAS date variable (range from 1 Jan 2009 to 31 Dec 2009). I want to recode this date variable to a new fixed fortnightly period variable.

The first period is from 1Jan2009 to 14 Jan 2009 and 1 Jan 2009 is the value for the first period. Then incremented by 14 days, the first date of the period is the value for the period.

Thanks.
Paris

Re: How to format date in fortnightly interval with a user defined starting dat

Cynthia_sas — Tue, 15 Dec 2009 15:23:17 GMT

Generally, to recode means to use a user-defined format. This means that the date would still be internally stored as 1 Jan 2009 (for example) but would be -displayed- on reports as 14 Jan 2009. Is this what you want? If so, then investigate user-defined formats and how to create them from a SAS dataset using the CNTLIN option.

On the other hand, if by "recode" you mean to create a new variable, then you probably don't need a user-defined format and can just use the INTNX function in a DATA step program to create your new variable

You may still need to use the INTNX function to create the user-defined format.

I'm also not sure what you mean by "the first date of the period is the value for the period" -- consider this dataset of 2 variables or columns:

[pre]
Name Start
Alan 1 Jan 2009
Bob 5 Jan 2009
Carl 10 Jan 2009
[/pre]

Do you want a NEW dataset that is a copy of the old dataset, but with the START variable -changed- so it represents the start day of the -next- period?
[pre]
Name Start
Alan 15 Jan 2009
Bob 19 Jan 2009
Carl 24 Jan 2009
[/pre]

Or, do you want the original dataset variables, plus a new variable:
[pre]
Name Start Next
Alan 1 Jan 2009 15 Jan 2009
Bob 5 Jan 2009 19 Jan 2009
Carl 10 Jan 2009 24 Jan 2009
[/pre]

Or, do you just want the original dataset to -display- the start value when you use the dataset with PROC PRINT or some other report procedure????

Another way to interpret your sentence is that if someone's date falls -within- the 2 week period, then you want their START variable to be adjusted to be the first day of the 2 week period.

[pre]
Name P_Date Start_Period
Alan 1 Jan 2009 1 Jan 2009
Bob 5 Jan 2009 1 Jan 2009
Carl 10 Jan 2009 1 Jan 2009
[/pre]

Your verbal description of how the date needs to be adjusted is certainly do-able in a couple of different ways.

However, your input data structure and your desired output (either data or report) will determine the method you use in a SAS program -- whether you use a DATA step program and the INTNX function; whether you use a DATA step program to make a user-defined format; what the output dataset looks like; or what the output report looks like.

Here's a possible scenario: You have data with customers and purchase dates. You want to summarize the amounts they spent by each 2-week (fortnightly) period. So the calculated or adjusted date needs to be used for summary and reporting purposes.

Here's another possible scenario: You have data with patients and their doctor's visits and you want some counts of how many patients' visits fell within each 2 week period.

Here's another possible scenario: You have data with customer name or patient name and some date. You need to track or check something on a date basis. So if your customer's date is 1 Jan 2009, you need to check something based on 15 Jan 2009 being the start of the period after 1 Jan 2009.

Here's another possible scenario: You need to generate a file with all the 2 week or fortnightly periods in 2009:
[pre]
Obs firstday lastday

1 01JAN2009 14JAN2009
2 15JAN2009 28JAN2009
3 29JAN2009 11FEB2009
4 12FEB2009 25FEB2009
5 26FEB2009 11MAR2009
6 12MAR2009 25MAR2009
. . . more . . .
[/pre]

Having some idea of what the input data looks like and what you need to do (the big picture), will help provide the context for the kind of program you need to write. You've provided one piece of the puzzle:
date + 13 = end of period
date + 14 = start of next period

cynthia

Re: How to format date in fortnightly interval with a user defined starting dat

sbb — Tue, 15 Dec 2009 16:14:20 GMT

I agree with the approach of using a user-defined SAS format with value range (START to END) and a SAS numeric date value as the LABEL (returned as character and convert back to a SAS internal DATE variable number.

Here is a code piece to illustrate how you could use DO/END logic with a BY interval to generate the START and END values for a PROC FORMAT CNTLIN= technique. The first DATA step generates some date values for data-points to test, and the second DATA step is simply limiting the check to a pre-defined date range.

Scott Barry
SBBWorks, Inc.

data dates;
do dt=mdy(1,3,2009) to mdy(2,28,2009);
output;
end;
run;
data _null_;
set dates;
do x=mdy(1,1,2009) to mdy(1,1,2010) by 14;
if dt lt x then do;
fortnight_dt = x-14;
leave;
end;
end;
putlog dt= fortnight_dt= ;
format dt fortnight_dt x date9.;
run;

Re: How to format date in fortnightly interval with a user defined starting dat

deleted_user — Wed, 16 Dec 2009 01:46:52 GMT

Thank you both for your very helpful information.
I have solved the problem using 'intnx'.

Paris