BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AllanD
Fluorite | Level 6

Hi All,

 

We are just starting out using the data management studio 2.6 to validate and cleanse data so if this seems a very basic question I apologise.

 

We normally receive a start date and end date for a record along with other information in a text file and have used the qkb to validate and cleanse this information prior to printing marketing material for these purchases.

 

There is one file we receive which has a start date and a duration rather than end date.  The duration is in months and can range from 1 to 24.

 

We need to calculate the end date from this but I cannot seem to find any way of adding the months to the start date.

 

Is there any way to do this or is there documentation that could help in identifying a solution?

 

Any help is greatly appreciated.

 

THanks,

 

Allan.

1 ACCEPTED SOLUTION

Accepted Solutions
NicolasRobert
SAS Super FREQ

Hello,

 

You can try this.

 

string start_date_string
integer duration_in_months

date start_date
date end_date
date end_date2
integer start_day
integer start_month
integer start_year

integer end_day
integer end_month
integer end_year

start_date_string='2016-07-14'
duration_in_months=18

/* first solution, some additional checks might be required (end of month) */
start_date=todate(start_date_string)
start_day=tointeger(formatdate(start_date,"DD"))
start_month=tointeger(formatdate(start_date,"MM"))
start_year=tointeger(formatdate(start_date,"YYYY"))

end_day=start_day
if (start_month+duration_in_months)%12==0 then
	end_month=12
else end_month=(start_month+duration_in_months)%12
end_year=start_year+(tointeger((start_month+duration_in_months-1)/12))

end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day)) /* date validity to be checked if falling end of month */

/* second solution, less accurate */
end_date2=start_date+tointeger(duration_in_months*30.4375)

This page could be of interest.

 

Hope this helps.

Nicolas.

 

Nicolas is an Advisory Technical Architect in the Global Enablement and Learning (GEL) Team within SAS Customer Success Division

View solution in original post

2 REPLIES 2
NicolasRobert
SAS Super FREQ

Hello,

 

You can try this.

 

string start_date_string
integer duration_in_months

date start_date
date end_date
date end_date2
integer start_day
integer start_month
integer start_year

integer end_day
integer end_month
integer end_year

start_date_string='2016-07-14'
duration_in_months=18

/* first solution, some additional checks might be required (end of month) */
start_date=todate(start_date_string)
start_day=tointeger(formatdate(start_date,"DD"))
start_month=tointeger(formatdate(start_date,"MM"))
start_year=tointeger(formatdate(start_date,"YYYY"))

end_day=start_day
if (start_month+duration_in_months)%12==0 then
	end_month=12
else end_month=(start_month+duration_in_months)%12
end_year=start_year+(tointeger((start_month+duration_in_months-1)/12))

end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day)) /* date validity to be checked if falling end of month */

/* second solution, less accurate */
end_date2=start_date+tointeger(duration_in_months*30.4375)

This page could be of interest.

 

Hope this helps.

Nicolas.

 

Nicolas is an Advisory Technical Architect in the Global Enablement and Learning (GEL) Team within SAS Customer Success Division
AllanD
Fluorite | Level 6

Hi Nicolas,

 

I used the first solution and added the following code to loop through to find the last valid month end date to take care of the end of month issues you raised as an issue.  Not sure if there is a better way but it works.

 

if isnull(end_date)
end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day-1))
if isnull(end_date)
end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day-2))
if isnull(end_date)
end_date=todate(tostring(end_year) & '-' & tostring(end_month) & '-' & tostring(end_day-3))

 

Thanks for the speedy response.

 

Allan.

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 848 views
  • 0 likes
  • 2 in conversation