Desktop productivity for business analysts and programmers

SAS EG Query - Data count interval

Reply
Established User
Posts: 1

SAS EG Query - Data count interval

[ Edited ]

Hello

i have a problem with calculating end dates

how to calculate end date in SAS EG query when i have a start date(date) column,years to end(num) column and months to end(num) column. 

thank you

DATE            YEARS TO END        MONTHS TO END   END DATE(date format)

31MAR2016        03                         3                                          ?

SAS Moderator
Posts: 46

Re: SAS EG Query - Data count [how to improve your question]

Hello @Danchek7,


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.
    use_buttons.png
  • 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.

 

edit_post.png

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 @KurtBremser

.
Super User
Posts: 5,824

Re: SAS EG Query - Data count interval

Basically, you have the INTNX function to rely on. I would try to calculate the total no of months to ut as a parameter to the function.

You should be able to define within query builder as a calculated column.

Data never sleeps
Super User
Posts: 13,008

Re: SAS EG Query - Data count interval


@Danchek7 wrote:

Hello

i have a problem with calculating end dates

how to calculate end date in SAS EG query when i have a start date(date) column,years to end(num) column and months to end(num) column. 

thank you

DATE            YEARS TO END        MONTHS TO END   END DATE(date format)

31MAR2016        03                         3                                          ?


And what would be the result from this exercise for the given date?

I could see 31Mar2019  (bad example to have Months to end match the month of the start. Since your start was March and is month 3 then Months to end might be better with 5 as a worked example.  )

30Jun2019

1Mar2019

1Jun2019

29Jun2019

31MAR2003  (these are the end of the year has to be 03 one interpretation of "years to end")

30Jun2003

31MAR2103

30Jun2103

 

And possibly a few others depending on exactly what "years to end" or "months to end" mean

Respected Advisor
Posts: 4,541

Re: SAS EG Query - Data count interval

[ Edited ]

@Danchek7

Making A LOT of assumptions what you're asking for below code could be an option.

data have;
  input DATE :date9. YEARS_TO_END MONTHS_TO_END;
  format date date9.;
  datalines;
31MAR2016 03 3
;
run;

data want;
  set have;
  format end_date date9.;
  end_date=intnx('month',date,12*years_to_end+months_to_end,'e');
run;

 

In EG you'd create a calculated column in a Query via a formula like:

 intnx('month',date,12*years_to_end+months_to_end,'e')

 

Ask a Question
Discussion stats
  • 4 replies
  • 161 views
  • 0 likes
  • 5 in conversation