function that returns a min date from table

[Nasser_DRMCP]

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 ;

[PeterClemmensen]

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

Please be specific.

[Nasser_DRMCP]

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 ;

[AhmedAl_Attar]

Hi @Nasser_DRMCP 

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

[PaigeMiller]

---

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

[Nasser_DRMCP]

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 ;

[PaigeMiller]

---

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

[Nasser_DRMCP]

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 ;

[Nasser_DRMCP]

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

[PaigeMiller]

---

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

[Nasser_DRMCP]

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 

[Quentin]

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.

[AhmedAl_Attar]

@Nasser_DRMCP 

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;

[FreelanceReinh · Accepted solution]

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;

[Quentin]

---

@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. : )