INTNX or INTCK in DO Loop with no end date

[BlairWhitty]

Hello all,

In my data set there are a variety of hospital reporting data, each item of data has an affiliated date. I am working to normalize the data as I am using it to track progress towards a goal, but each hospital started at different times.

Right now my code is cumbersome, and I know it is not ideal, but can't quite figure out how to fix it. What I ultimately want is a numeric variable (Month_Enroll) that provides the number of months before or after the start date that a case is from. For example, if a hospital started in June 2018, I would want all cases from May 2018 to have a Month_Enroll of -1 and all cases from July 2017 to have a Month_Enroll of 1.  This data set is constantly be updated, so I am trying to write a program that will require minimum maintenance. I feel like I should be able to accomplish what I need with a DO Loop, but haven't been successful so far.

This is what I have (it isn't working how I want). Delivery date = date a baby was born, record_id is affiliated with a hospital

DATA Set.Clean;
       FORMAT delivery_date  YYMMN6.;
       FORMAT Hospital_Start YYMMN6.;

SET Set.Old;

      Hospital_Start = 06/01/2018;

IF FIND(record_id, "56073") THEN DO;
Month_Enroll = delivery_date - Luth_Start;
END;

RUN;

I appreciate any help!

[Community_Guide]

Hello @BlairWhitty,

Your question requires more details before experts can help. Can you revise your question to include more information? 

Review this checklist:

• Specify a meaningful subject line for your topic.  Avoid generic subjects like "need help," "SAS query," or "urgent."
• When appropriate, provide sample data in text or DATA step format.  See this article for one method you can use.
• If you're encountering an error in SAS, include the SAS log or a screenshot of the error condition. Use the Photos button to include the image in your message.
  
• It also helps to include an example (table or picture) of the result that you're trying to achieve.

To edit your original message, select the "blue gear" icon at the top of the message and select Edit Message.  From there you can adjust the title and add more details to the body of the message.  Or, simply reply to this message with any additional information you can supply.

SAS experts are eager to help -- help them by providing as much detail as you can.

This prewritten response was triggered for you by fellow SAS Support Communities member @ballardw

.

[Reeza]

I don't think this does what you think it does:

Hospital_Start = 06/01/2018;

That will be come 6 divided by 1 divided by 2018 which is a number. When you specify sas dates you need to use date literals.

You would specify the above line as:

Hospital_Start = "01Jun2018"d;

Here's a great, but longer and in depth, reference for dates and times in SAS
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/...

---

@BlairWhitty wrote:

Hello all,

In my data set there are a variety of hospital reporting data, each item of data has an affiliated date. I am working to normalize the data as I am using it to track progress towards a goal, but each hospital started at different times.

Right now my code is cumbersome, and I know it is not ideal, but can't quite figure out how to fix it. What I ultimately want is a numeric variable (Month_Enroll) that provides the number of months before or after the start date that a case is from. For example, if a hospital started in June 2018, I would want all cases from May 2018 to have a Month_Enroll of -1 and all cases from July 2017 to have a Month_Enroll of 1.  This data set is constantly be updated, so I am trying to write a program that will require minimum maintenance. I feel like I should be able to accomplish what I need with a DO Loop, but haven't been successful so far.

This is what I have (it isn't working how I want). Delivery date = date a baby was born, record_id is affiliated with a hospital

DATA Set.Clean;
       FORMAT delivery_date  YYMMN6.;
       FORMAT Hospital_Start YYMMN6.;

SET Set.Old;

      Hospital_Start = 06/01/2018;

IF FIND(record_id, "56073") THEN DO;
Month_Enroll = delivery_date - Luth_Start;
END;

RUN;

I appreciate any help!

---

[VDD]

Welcome to the world of dates, that's one of the reasons that SAS expertise are so great.  They know how to look at dates and ask what are they really asking for based on what they are giving me.  Dates the world of programming.  'Payments due, past dues, death because of inability for system to identify date and time of entry.' 

[BlairWhitty]

Thank you so much! I know better...but my common sense seems to disappear when I am rage coding.

[ballardw · Accepted solution]

---

@BlairWhitty wrote:

Hello all,

In my data set there are a variety of hospital reporting data, each item of data has an affiliated date. I am working to normalize the data as I am using it to track progress towards a goal, but each hospital started at different times.

Right now my code is cumbersome, and I know it is not ideal, but can't quite figure out how to fix it. What I ultimately want is a numeric variable (Month_Enroll) that provides the number of months before or after the start date that a case is from. For example, if a hospital started in June 2018, I would want all cases from May 2018 to have a Month_Enroll of -1 and all cases from July 2017 to have a Month_Enroll of 1.  This data set is constantly be updated, so I am trying to write a program that will require minimum maintenance. I feel like I should be able to accomplish what I need with a DO Loop, but haven't been successful so far.

This is what I have (it isn't working how I want). Delivery date = date a baby was born, record_id is affiliated with a hospital

DATA Set.Clean;
       FORMAT delivery_date  YYMMN6.;
       FORMAT Hospital_Start YYMMN6.;

SET Set.Old;

      Hospital_Start = 06/01/2018;

IF FIND(record_id, "56073") THEN DO;
Month_Enroll = delivery_date - Luth_Start;
END;

RUN;

I appreciate any help!

---

Note that SAS does not consider this to be a "date" value. I strongly suspect that this line of code creates a value you do not want.

Hospital_Start = 06/01/2018;

Hospitalstart will have a value of about 0.00297 because it is dividing 6 by 1 and then dividing that result by 2018.

To provide a DATE value that SAS will use as such you must use

HospitalStart = '01JUN2018'D;

A quoted value in Date9 or Date7 appearance followed by a D tells SAS you intend to use a DATE value.

So what you want is

month_enroll = intck('month',hospital_start,delivery_date);

Dates in SAS are measured in Days and regardless of the display format you will get a difference of Days with date1-date2.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[BlairWhitty]

Thank you so much for your expertise! This worked exactly as I wanted, you are truly a SAS god