BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vidyasagar1
Obsidian | Level 7
/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

 

looking for the output:

/* For the first two models Build_Date will be today's date and for next three models Build_Date will be tomorrow's and the trend will continue for next five. */;

 

Model     Build_Date

735        11/09/2021

736        11/09/2021

737        11/10/2021 

777        11/10/2021

775        11/10/2021

772        11/11/2021

773        11/11/2021

787        11/12/2021

797        11/12/2021

747        11/12/2021 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

So, you have a dataset containing dates of non-workdays. Make a list out of that, which you then use in a IN condition:

proc sql;
select holiday format=5. into :holidays separated by ","
from holidays;
quit;

data want;
set have;
retain
  flag_3 0
  count 0
  build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
  do until (build_date not in(&holidays.) and weekday(build_date) not in (1,7));
    build_date + 1;
  end;
  count = 1 + flag_3;
  flag_3 = not flag_3;
end;
else count + (-1);
drop count flag_3;
run;

View solution in original post

21 REPLIES 21
vidyasagar1
Obsidian | Level 7

Please help me with the dynamic SAS code. And the Build_Date should be weekday. 

Kurt_Bremser
Super User

Every day is a day of a week, so please define "weekday".

This code creates your dataset:

data have;
input model $10.;
datalines;
735    
736     
737       
777
775
772
773
787
797
747
;

%let start=%eval(%sysfunc(today())-1);

data want;
set have;
retain
  flag_3 0
  count 0
  build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
  build_date + 1;
  count = 1 + flag_3;
  flag_3 = not flag_3;
end;
else count + (-1);
drop count flag_3;
run;
vidyasagar1
Obsidian | Level 7

Hi, I am very happy for your quick reply. This code is working as expected for my request. Sorry I forgot to add few more points. The Build_Date should have only working days, means excludling Saturday's and Sunday's date and also the holiday's.

We will feed the list of Holidays.

Please help me with the SAS code including the above points also.

Kurt_Bremser
Super User

So, you have a dataset containing dates of non-workdays. Make a list out of that, which you then use in a IN condition:

proc sql;
select holiday format=5. into :holidays separated by ","
from holidays;
quit;

data want;
set have;
retain
  flag_3 0
  count 0
  build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
  do until (build_date not in(&holidays.) and weekday(build_date) not in (1,7));
    build_date + 1;
  end;
  count = 1 + flag_3;
  flag_3 = not flag_3;
end;
else count + (-1);
drop count flag_3;
run;
vidyasagar1
Obsidian | Level 7

Hi ,

Could you also please help me to write a code for the below requests also ?

 

Request 1 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first two models today's date and next two model's tomorrow's date and so on.

 Model Build_Date

 735    11/09/2021

 736    11/09/2021

 737    11/10/2021

 777    11/10/2021

 775    11/11/2021

 772    11/11/2021

 773    11/12/2021

 787    11/12/2021

 797    11/13/2021

 747    11/13/2021  

 

Request 2 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first three models today's date and next three model's tomorrow's date and so on.

 Model Build_Date

 735    11/09/2021

 736    11/09/2021

 737    11/09/2021

 777    11/10/2021

 775    11/10/2021

 772    11/10/2021

 773    11/11/2021

 787    11/11/2021

 797    11/11/2021

 747    11/12/2021 

 

Request 3 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first model today's date and next model day after tomorrow's date and so on.

 Model Build_Date

 735    11/09/2021

 736    11/11/2021

 737    11/13/2021

 777    11/15/2021

 775    11/17/2021

 772    11/19/2021

 773    11/21/2021

 787    11/23/2021

 797    11/25/2021

 747    11/27/2021

 

Request 4 :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735    
736     
737       
777
775
772
773
787
797
747
;
RUN;

Expected Output: For the first model today's date and next model tomorrow's date and so on.

 Model Build_Date

 735    11/09/2021

 736    11/10/2021

 737    11/11/2021

 777    11/12/2021

 775    11/13/2021

 772    11/14/2021

 773    11/15/2021

 787    11/16/2021

 797    11/17/2021

 747    11/18/2021

 

 

 

 

 

Kurt_Bremser
Super User

It's the same logic, with different offset value. You can easily solve that on your own now.

Like other muscles, an unused brain will atrophy.

vidyasagar1
Obsidian | Level 7

Sorry. Thank you ! 🙂

vidyasagar1
Obsidian | Level 7

Hi, I am unable to solve it . Please help me with offset values for each of my query .

 

vidyasagar1
Obsidian | Level 7

Finally I developed the code for the my requests  and it is working fine. 

 

%let start=%eval(%sysfunc(today())-1);

Code for Request 1 :

data want;
set have;
retain
flag_3 1
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 2 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

 

Code for Request 2 :

data want;
set have;
retain
flag_3 1
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 3 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

Code for Request 3 :

data want;
set have;
retain
flag_3 2
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 3 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

 

Code for Request 4 :

data want;
set have;
retain
flag_3 1
count 0
build_date &start.
;
format build_date yymmdd10.;
if count = 0
then do;
do until (weekday(build_date) not in (1,7));
build_date + 1;
end;
count = 1 + (-flag_3);
/* flag_3 = not flag_3;*/
end;
else count + (-1);
/*drop count flag_3;*/
run;

vidyasagar1
Obsidian | Level 7

For this below request I am unable to develop the code.

For first two models today's date and for next four models Build_date is incremented by 1, it has to follow same sequence for next 6 models. 

New Request :

/* Input */;
DATA INPUT;
INFILE DATALINES;
INPUT MODEL $10.;
DATALINES;
735
736
737
777
775
772
773
787
797
747
;
RUN;
Expected Output:

Model Build_Date

735 11/16/2021

736 11/16/2021

737 11/17/2021

777 11/18/2021

775 11/19/2021

772 11/22/2021

773 11/23/2021

787 11/23/2021

797 11/24/2021

747 11/25/2021

Tom
Super User Tom
Super User

Just use two nested DO loops.

DATA INPUT;
  INPUT MODEL $10.;
DATALINES;
735
736
737
777
775
772
773
787
797
747
;

data want;
  do build_date=today() by 1 ;
    do i=1 to 3;
      set input;
      output;
    end;
  end;
  format build_date yymmdd10.;
run;

proc print;
run;
Obs    build_date    i    MODEL

  1    2021-11-16    1     735
  2    2021-11-16    2     736
  3    2021-11-16    3     737
  4    2021-11-17    1     777
  5    2021-11-17    2     775
  6    2021-11-17    3     772
  7    2021-11-18    1     773
  8    2021-11-18    2     787
  9    2021-11-18    3     797
 10    2021-11-19    1     747
vidyasagar1
Obsidian | Level 7

Sorry, I have corrected my query. Please help me to write the code for my corrected query. It has to exclude weekends and list of holiday's which we provide.

Tom
Super User Tom
Super User

Just incorporate the logic to skip the holidays.

For example if have a list of holidays in a dataset named HOLIDAYS (and the list is short enough to put into a macro variable) then something like this should work.

Let's test it by pretending tomorrow is a holiday.

data holidays;
  holiday=today()+1;
run;

proc sql;
  select holiday into :holidays separated by ' '
  from holidays;
quit;

data want;
  do build_date=today() by 1 ;
    do build_date=build_date by 1
       while(build_date in (&holidays) or weekday(build_date) in (1 7));
    end;
    do i=1 to 3;
      set input;
      output;
    end;
  end;
  format build_date yymmdd10.;
run;

Result

Obs    build_date    i    MODEL

  1    2021-11-16    1     735
  2    2021-11-16    2     736
  3    2021-11-16    3     737
  4    2021-11-18    1     777
  5    2021-11-18    2     775
  6    2021-11-18    3     772
  7    2021-11-19    1     773
  8    2021-11-19    2     787
  9    2021-11-19    3     797
 10    2021-11-22    1     747

So you can see it skipped tomorrow and also the week-end.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 21 replies
  • 1116 views
  • 2 likes
  • 4 in conversation