topic Re: # of months between two date vars in SAS Programming

# of months between two date vars

Walternate — Tue, 14 Jul 2015 14:10:19 GMT

Hi,

I have a dataset at the person/month/year level--ie, each row is one person for one mth/year combination. People occur multiple times in the dataset (but a new row would be a different mth/year). There are two date columns. date_col_1 is at the person level--ie, for each of the person's rows, date_col_1 will always be the same. date_col_2 reflects the mth/year that the event took place (format is yyyymm). Total is a sum variable reflecting the # of times an event happened within each mth/year for each person.

Example data:

ID date_col_1 date_col_2 total

1 1/3/2012 201203 5

1 1/3/2012 201204 7

1 1/3/2012 201205 3

2 10/5/2013 201201 1

2 10/5/2013 201202 12

3 3/4/2012 201301 11

3 3/4/2012 201305 4

3 3/4/2012 201401 5

3 3/4/2012 201403 10

What I want is a new variable that gives date_col_2 relative to date_col_1--that is, the number of months that date_col_2 occurs before or after date_col_1. For example, in the first row it would be +2 months because date_col_2 is two months after date_col_1; in the first row for ID 2 it would be -21 because date_col_2 is 21 months prior to date_col_1.

Any help is much appreciated.

Re: # of months between two date vars

ballardw — Tue, 14 Jul 2015 14:13:55 GMT

Are your dates SAS date value variables or character? That is the first step then use the function INTCK.

months = intck('month', date_col_1, date_col_2);

Re: # of months between two date vars

AncaTilea — Tue, 14 Jul 2015 14:18:10 GMT

How about something like this:

data have;

informat date_col_1 mmddyy10. date_col_2 yymmn6.;

format date_col_1 mmddyy10. date_col_2 yymmn6.;

input ID date_col_1 date_col_2 total;

diff_mths = (date_col_1 - date_col_2)/30.25;

datalines;

1 1/3/2012 201203 5

1 1/3/2012 201204 7

1 1/3/2012 201205 3

2 10/5/2013 201201 1

2 10/5/2013 201202 12

3 3/4/2012 201301 11

3 3/4/2012 201305 4

3 3/4/2012 201401 5

3 3/4/2012 201403 10

;

run;

proc print;run;

Anca.

Re: # of months between two date vars

dcruik — Tue, 14 Jul 2015 14:21:42 GMT

I would look into the INTCK() function as you can subtract years, days, months, hours, etc.. from date variables. Below is what you're looking for:

Months=intck('MONTH',date_col_1,date_col_2);

Hope this helps!

But I see Ballardw beat me to it, so look into that function if both fields are date formats in your data set.

Re: # of months between two date vars

SASKiwi — Tue, 14 Jul 2015 20:02:56 GMT

Also with INTCK you need to consider if you are counting month boundaries or month intervals.

The default method is month boundaries so intck('month', '30jun2015'd, '01jul2015') will count as 1 month as a boundary has been crossed.

If you want to count month intervals based on the start and end date add the METHOD parameter C for CONTINUOUS: Months=intck('MONTH',date_col_1,date_col_2, 'C');