☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Lapis Lazuli | Level 10

## function that returns a min date from table

Hello,

I would like to create a function that returns the min date of a table wich is greater that an input date.

for instance. with this table of june, if the input date is 23 june then the date returned should be 24 june. many thanks.

Nasser

``````
Data T_DATES ;
input day  ;
informat day ddmmyy10. ;
format day date9. ;
datalines ;
01/06/2023
02/06/2023
03/06/2023
04/06/2023
05/06/2023
06/06/2023
07/06/2023
08/06/2023
09/06/2023
10/06/2023
11/06/2023
12/06/2023
13/06/2023
14/06/2023
15/06/2023
16/06/2023
17/06/2023
18/06/2023
19/06/2023
20/06/2023
21/06/2023
22/06/2023
23/06/2023
24/06/2023
25/06/2023
26/06/2023
27/06/2023
28/06/2023
29/06/2023
30/06/2023
;
run ;``````

1 ACCEPTED SOLUTION

Accepted Solutions
Jade | Level 19

## Re: function that returns a min date from table

Hello @Nasser_DRMCP,

You can specify custom intervals for the INTNX function:

``````data wd;
set T_Working_Days(rename=(day=begin));
run;

options intervalds=(WorkingDays=wd);

data t_want;
set t_have;
output_date=intnx('WorkingDays',input_date,1);
format output_date date9.;
run;``````
19 REPLIES 19
Tourmaline | Level 20

## Re: function that returns a min date from table

How do you want the result? In a macro variable?

Lapis Lazuli | Level 10

## Re: function that returns a min date from table

not neccessary in a macro variable.

``````Data T_TABLE1 ;
input input_date  ;
informat input_date ddmmyy10. ;
format input_date date9. ;
datalines ;
23/06/2023
;
run ;``````

the input date is in a table and I would lie to call the function like this:

data T_TABLE2 ;

set T_TABLE1 ;

output_date = function(input_date,T_DATES) ;

run ;

Rhodochrosite | Level 12

## Re: function that returns a min date from table

Try this, although it's not a function per-say, but it's done in a single data step

``````data T_TABLE2(drop=rc) ;
set T_TABLE1 ;
rc = dosubl('proc sql outobs=1 noprint;
select day into :next_day trimmed
from (select day
from t_dates
where day >'|| input_date ||'); quit;');
output_date = INPUT(symget('next_day'),date9.) ;
format output_date date9.;
run;
``````

If a macro input_date were to be used, then this could work too

``````%let user_day = '23Jun2023'd;
proc sql outobs=1 noprint;
select day
into :next_day
from (select day
from t_dates
where day > &user_day);
quit;
%put &=next_day;``````

Hope this helps

Diamond | Level 26

## Re: function that returns a min date from table

@Nasser_DRMCP wrote:

Hello,

I would like to create a function that returns the min date of a table wich is greater that an input date.

for instance. with this table of june, if the input date is 23 june then the date returned should be 24 june. many thanks.

Okay, two steps: 1. find dates that are greater than 23 june; 2. find minimum of those dates

``````data step1;
set dates;
if day>'23jun2023'd then output;
run;
proc summary data=step1;
var day;
output out=step2 min=min_day;
run;``````
--
Paige Miller
Lapis Lazuli | Level 10

## Re: function that returns a min date from table

Thanks for your prompt respons

I realize that my problem was not clearly defined; sorry for that.

I have many dates in the input table;  this is my "have" table and "want" table; that's why I would like to create a function; thanks

``````
Data T_TABLE_WANT ;
input input_date  ;
informat input_date ddmmyy10. ;
format input_date date9. ;
datalines ;
02/06/2023
07/06/2023
15/06/2023
23/06/2023
29/06/2023
;
run ;

Data T_TABLE_WANT ;
input input_date output_date ;
informat input_date output_date ddmmyy10. ;
format input_date output_date date9. ;
datalines ;
02/06/2023 03/06/2023
07/06/2023 08/06/2023
15/06/2023 16/06/2023
23/06/2023 24/06/2023
29/06/2023 30/06/2023
;
run ;``````

Diamond | Level 26

## Re: function that returns a min date from table

@Nasser_DRMCP wrote:

``````Data T_TABLE_WANT ;
input input_date output_date ;
informat input_date output_date ddmmyy10. ;
format input_date output_date date9. ;
datalines ;
02/06/2023 03/06/2023
07/06/2023 08/06/2023
15/06/2023 16/06/2023
23/06/2023 24/06/2023
29/06/2023 30/06/2023
;
run ;``````

Hello @Nasser_DRMCP It looks like you want to add one day to the day to the value in INPUT_DATE. Is that what you want? This seems to contradict everything you have said so far.

--
Paige Miller
Lapis Lazuli | Level 10

## Re: function that returns a min date from table

yes but only based on a list of days in a table; it is important because I have a table with all "working" days defined; and then, I want to get the first working date after a specifiq day that is contained in a "want" table.

I am trying something like this

``````
proc fcmp outlib=work.fonction.dates;
function next_working_day(p_date , p_table \$ , p_colonne \$);

proc sql;
select min(p_colonne) into :nextdate
from p_table
where p_colonne > p_date
;

return(nextdate);
endsub;
run; quit;

Data T_TABLE_WANT ;
set T_TABLE_HAVE ;
output_date = next_working_day(input_date);
run ;``````
Lapis Lazuli | Level 10

## Re: function that returns a min date from table

I want to get the first working date after a specifiq day that is contained in a "HAVE" (not "want") table.

Diamond | Level 26

## Re: function that returns a min date from table

@Nasser_DRMCP wrote:

yes but only based on a list of days in a table;

I really don't know what you mean by this. Discussions that have "yes but ..." are by definition unclear. You just showed us an example where you have a data set T_TABLE_WANT, and the desired output is to shown which is one day greater than the day in the original table, there's no additional table with a list of days. Then you say

it is important because I have a table with all "working" days defined;

If we are going to help you write code that works, we need to see this table as well.

Can you please give us a complete and clear problem description, with portions of all necessary tables?

--
Paige Miller
Lapis Lazuli | Level 10

## Re: function that returns a min date from table

OK.I try to be clear. with july it should be clearer;

in input I have 2 tables; T_WORKING_DAYS and T_HAVE;

note that 14 is missing in T_WORKING_DAYS because is not a working day

``````
Data T_WORKING_DAYS ;
input day  ;
informat day ddmmyy10. ;
format day date9. ;
datalines ;
01/07/2023
02/07/2023
03/07/2023
04/07/2023
05/07/2023
06/07/2023
07/07/2023
08/07/2023
09/07/2023
10/07/2023
11/07/2023
12/07/2023
13/07/2023
15/07/2023
16/07/2023
17/07/2023
18/07/2023
19/07/2023
20/07/2023
21/07/2023
22/07/2023
23/07/2023
24/07/2023
25/07/2023
26/07/2023
27/07/2023
28/07/2023
29/07/2023
30/07/2023
31/07/2023
;
run ;

Data T_HAVE ;
input input_date  ;
informat input_date ddmmyy10. ;
format input_date date9. ;
datalines ;
13/07/2023
23/07/2023
29/07/2023
;
run ;``````

I would like to get a T_WANT_TABLE that contains 2 columns. "input_date" column coming from T_HAVE table. and "output_date" column would be the first date (the smallest) after the input_date. for examle, by applying the function to first row, where the input_date is 13/07/2023; the function woud return 15/07/2023 because 15/07 is the first day among dates in `T_WORKING_DAYS that is greater than 13 `

Super User

## Re: function that returns a min date from table

Do you really need a function?

This feels like a SQL join.  Correlated subqueries definitely aren't my specialty, but after hacking around I came up with:

``````proc sql ;
select
input_date
,(select min(day) as day
from T_WORKING_DAYS as b
where b.day>a.input_date
) as want_date format=date9.
from T_HAVE as a
;
quit ;``````

I'm sure there are better SQL solutions.

If you really want a function, since you're dealing with calendar dates over a limited time span, you could also just make a table that maps every date to the next business date.  And use that table to create a format, nextbusinessday, then use the PUT function to look up a value.  Or whatever table lookup method you like.

BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Rhodochrosite | Level 12

## Re: function that returns a min date from table

This step gives you the desired output

``````data T_WANT(drop=rc) ;
set T_HAVE ;
rc = dosubl('proc sql outobs=1 noprint;
select day into :next_day trimmed
from (select day
from t_working_days
where day >'|| input_date ||'); quit;');
output_date = INPUT(symget('next_day'),date9.) ;
format output_date date9.;
run;``````

Jade | Level 19

## Re: function that returns a min date from table

Hello @Nasser_DRMCP,

You can specify custom intervals for the INTNX function:

``````data wd;
set T_Working_Days(rename=(day=begin));
run;

options intervalds=(WorkingDays=wd);

data t_want;
set t_have;
output_date=intnx('WorkingDays',input_date,1);
format output_date date9.;
run;``````
Super User

## Re: function that returns a min date from table

@FreelanceReinh wrote:

Hello @Nasser_DRMCP,

You can specify custom intervals for the INTNX function:

``````data wd;
set T_Working_Days(rename=(day=begin));
run;

options intervalds=(WorkingDays=wd);

data t_want;
set t_have;
output_date=intnx('WorkingDays',input_date,1);
format output_date date9.;
run;``````

I think we have a winner.  Wow, @FreelanceReinh , thanks for sharing that.  If I can discover one new (to me) approach like that a year, my time on communities will be worth it. : )

BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Discussion stats
• 19 replies
• 1198 views
• 7 likes
• 6 in conversation