SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Calculating months apart from 2 date variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Calculating months apart from 2 date variables

I have a character variable length 9 (dt) and a numeric variable w/ the format MMDDYY8 (encounterdate) and i want to find months apart. I think they both need to be in the format MMDDYY8 but I was having trouble converting the variable "dt" to that format. 


Accepted Solutions
Solution
‎06-29-2017 03:09 PM
Super User
Posts: 11,343

Re: Calculating months apart from 2 date variables

[ Edited ]
data temp1; 
   set temp;
   date = input(dt,anydtdte.);
   format date yymmdd8.;
 run;

yymmdd would not work as the infomats like that expect digits in each position with optional separators such as / or -.

 

From your displayed values for DT the first is day of the month and yymmdd would expect a YEAR for the first digits as well.

The Date9. informat would also work.

View solution in original post


All Replies
Super User
Posts: 19,846

Re: Calculating months apart from 2 date variables

Formats don't matter. 

As long as both are dates ie number with any date format they'll work.

 

What are you current types/formats?

 

 

Occasional Contributor
Posts: 9

Re: Calculating months apart from 2 date variables

Only one variable (encounterdate) is in a date format: MMDDYY8.

 

The other varible (dt) is character and is length 9. How do I get that into a date format?

Super User
Posts: 11,343

Re: Calculating months apart from 2 date variables

[ Edited ]

To compare dates, or practically any other date manipulation you want a SAS date value. The current display format for a SAS date valued variable does not impact any of the date functions.

 

You do not show how your current text date value looks to suggest a specific approach but you may be able to get the conversion you need as

 

date_value = input(textdate, anydtdte.);

if that doesn't work then provide examples of what your variable looks like. Note that two digit years as in yymmdd type values may not work with the anydtdte. informat as it has to make a guess and values like 040509 are real hard to guess which is a day of the month, month or year.

You should assign a format to see if the value looks correct such as

format date_value mmddyy8.;

 

 

Once you have a date value then you use the intck function

 

Nummonths = intck('month', dateone, datetwo);

 

Occasional Contributor
Posts: 9

Re: Calculating months apart from 2 date variables

Hi,

 

Thank you.

 

I tried:


data temp1; set temp;
date = input(dt,yymmdd8.);

 run;

 

but the new "date" variable was empty. 

 

This is how the variables look:

Capture.PNG

 

Occasional Contributor
Posts: 9

Re: Calculating months apart from 2 date variables

[ Edited ]

These are the formats:

 

formats.PNG

 

Thanks so much.

Super User
Posts: 19,847

Re: Calculating months apart from 2 date variables

date_dt = input(compress(dt, '-'), date9.);
format date_dt date9.;

month_diff = intck('month', date_dt, encounter_date);

Some like the following should work for you.

Occasional Contributor
Posts: 9

Re: Calculating months apart from 2 date variables

Much appreciated. This worked in changing the format of the dt variable but month_diff is still all blanks... 

Solution
‎06-29-2017 03:09 PM
Super User
Posts: 11,343

Re: Calculating months apart from 2 date variables

[ Edited ]
data temp1; 
   set temp;
   date = input(dt,anydtdte.);
   format date yymmdd8.;
 run;

yymmdd would not work as the infomats like that expect digits in each position with optional separators such as / or -.

 

From your displayed values for DT the first is day of the month and yymmdd would expect a YEAR for the first digits as well.

The Date9. informat would also work.

Super User
Posts: 5,516

Re: Calculating months apart from 2 date variables

Here are two issues to consider.

 

First, you have to read the log.  If you spell a variable name ENCOUNTER_DATE, but the actual name is ENCOUNTERDATE, you should expect that the log will give you a hint about the problem.

 

Second, you have to consider the formula you want for measuring months.  Using INTCK might be correct, or it might not, depending on what you want the result to be.  INTCK measures how many month boundaries you cross.  Thus if your date range begins at January 2 and ends at January 31, INTCK returns 0.  There might be 29 days there, but the range does not cross any month boundaries.  On the other hand, if your date range begins on January 31 and ends on February 2, those are 2 days but INTCK returns a 1 since the range crosses a month boundary.  You might want to consider other possibilities for measuring months such as:

 

duration_in_days = EncounterDate - input(dt, date9.);

 

Then possibly:

 

duration_in_months = duration_in_days / 30.5;

Occasional Contributor
Posts: 9

Re: Calculating months apart from 2 date variables

Posted in reply to Astounding

Worked, thanks!!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 204 views
  • 2 likes
  • 4 in conversation