BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ChickenLittle
Obsidian | Level 7

Hi, 

I want to calculate the fractional months between different observations from the prior observations with the same key. For example, for the observations with the key "111", I want to calculate the fractional months seen in Counter "2" vs Counter "1", then the fractional month seen in Counter "3" vs Counter "2", then Counter "4" vs Counter "2", then start over again for next key "118". In excel, it would be like using the formula =DATEDIF(D7,D8,"M")+DATEDIF(D7,D8,"MD")/(365/12). Below is an example of the data set and I want to create the "Months btw Dates" column below. Thanks in advance!

Obs Key Counter Date Months btw Dates
1 111 1 1/1/2023  
2 111 2 2/5/2023 1.13
3 111 3 4/7/2023 2.07
4 111 4 6/10/2023 2.10
5 118 1 2/10/2023  
6 118 2 5/1/2023 2.69
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
infile cards expandtabs truncover;
input Obs	Key	Counter	Date :mmddyy10.;
format date mmddyy10.;
cards;
1	111	1	1/1/2023	
2	111	2	2/5/2023	
3	111	3	4/7/2023	
4	111	4	6/10/2023	
5	118	1	2/10/2023	
6	118	2	5/1/2023
;
data want;
 set have;
 lag_date=lag(date);
 if key=lag(key) then want=yrdif(lag_date,date,'30/360')*12;
 drop lag_date;
run;

View solution in original post

3 REPLIES 3
ChickenLittle
Obsidian | Level 7

I mispoke when I said Counter "4" vs Counter "2". What I meant to say was that I want Counter "4" vs Counter "3"

PaigeMiller
Diamond | Level 26

I dislike the idea of "fractional months", as months are different lengths, and so I recommend you not use "fractional months". The following code will compute the number of days between the dates. You can do with that number of days anything you want.

 

data have;
input obs key counter date :mmddyy8.;
format date date9.;
cards;
1	111	1	1/1/2023	 
2	111	2	2/5/2023
3	111	3	4/7/2023
4	111	4	6/10/2023
5	118	1	2/10/2023	 
6	118	2	5/1/2023
;

data want;
    set have;
    by key;
    prev_date=lag(date);
    if first.key then days_between=.;
    else days_between=date-prev_date;
    drop prev_date;
run; 

 

Please note: we expect data to be presented as working SAS data step code, and not other formats such as the one your provided. I have given an example (and instructions here for more complicated data sets). In your future questions, please provide data as shown.

--
Paige Miller
Ksharp
Super User
data have;
infile cards expandtabs truncover;
input Obs	Key	Counter	Date :mmddyy10.;
format date mmddyy10.;
cards;
1	111	1	1/1/2023	
2	111	2	2/5/2023	
3	111	3	4/7/2023	
4	111	4	6/10/2023	
5	118	1	2/10/2023	
6	118	2	5/1/2023
;
data want;
 set have;
 lag_date=lag(date);
 if key=lag(key) then want=yrdif(lag_date,date,'30/360')*12;
 drop lag_date;
run;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 144 views
  • 2 likes
  • 3 in conversation