BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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;
deleted_user
Not applicable
Thank you both for your very helpful information.
I have solved the problem using 'intnx'.

Paris

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 2091 views
  • 0 likes
  • 3 in conversation