BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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.

4 REPLIES 4
ballardw
Super User

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);

AncaTilea
Pyrite | Level 9

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.

dcruik
Lapis Lazuli | Level 10

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.  Smiley Happy

SASKiwi
PROC Star

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');

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1791 views
  • 0 likes
  • 5 in conversation