topic Re: function that returns a min date from table in SAS Programming

function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 09:28:45 GMT

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 ;

Re: function that returns a min date from table

PeterClemmensen — Tue, 02 May 2023 09:35:15 GMT

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

Please be specific.

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 09:51:59 GMT

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 ;

Re: function that returns a min date from table

PaigeMiller — Tue, 02 May 2023 10:10:29 GMT

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

Re: function that returns a min date from table

AhmedAl_Attar — Tue, 02 May 2023 10:47:12 GMT

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

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 11:57:02 GMT

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 ;

Re: function that returns a min date from table

PaigeMiller — Tue, 02 May 2023 12:07:19 GMT

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

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 12:13:04 GMT

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 ;

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 12:14:18 GMT

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

Re: function that returns a min date from table

PaigeMiller — Tue, 02 May 2023 12:18:19 GMT

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

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 12:41:52 GMT

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

Re: function that returns a min date from table

Quentin — Tue, 02 May 2023 13:03:13 GMT

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.

Re: function that returns a min date from table

AhmedAl_Attar — Tue, 02 May 2023 13:03:26 GMT

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

Re: function that returns a min date from table

FreelanceReinh — Tue, 02 May 2023 13:14:13 GMT

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;

Re: function that returns a min date from table

Quentin — Tue, 02 May 2023 13:27:56 GMT

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

Re: function that returns a min date from table

FreelanceReinh — Tue, 02 May 2023 14:03:50 GMT

@Quentin wrote:
If I can discover one new (to me) approach like that a year, my time on communities will be worth it. : )

Thanks. Same here. :-) I think I learned that approach in 2018 from this post by John King.

Re: function that returns a min date from table

Quentin — Tue, 02 May 2023 15:03:14 GMT

@Nasser_DRMCP , I suggest you un-accept my answer as the solution, and then select @FreelanceReinh 's custom interval answer as the solution. My answer was just to celebrate the cool solution. I didn't feel like a simple "like" was enough. : )

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 15:19:53 GMT

Quentin,

I agree with you to accept the FreelanceReinh 's solution; but I don't know how t un-accept yours

Re: function that returns a min date from table

Quentin — Tue, 02 May 2023 15:41:23 GMT

I think if you click a button near my solution, there should be an option to select "this is not the solution" or something like that. Or if you can't do it, it's possible one of the admins or super-users will do it. They have special powers. : )

Re: function that returns a min date from table

Nasser_DRMCP — Tue, 02 May 2023 16:12:51 GMT

it s done !

thank for your help