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

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
FreelanceReinh
Jade | Level 19

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;

View solution in original post

19 REPLIES 19
PeterClemmensen
Tourmaline | Level 20

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

 

Please be specific.

Nasser_DRMCP
Lapis Lazuli | Level 10

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
Rhodochrosite | Level 12

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
Diamond | Level 26

@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
Nasser_DRMCP
Lapis Lazuli | Level 10

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
Diamond | Level 26

@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
Nasser_DRMCP
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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

PaigeMiller
Diamond | Level 26

@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
Nasser_DRMCP
Lapis Lazuli | Level 10

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
Super User

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.
AhmedAl_Attar
Rhodochrosite | Level 12

@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
Jade | Level 19

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
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1162 views
  • 7 likes
  • 6 in conversation