topic Re: keep the VALUE for days that are within the first 5 or last 5 working days in SAS Programming

keep the VALUE for days that are within the first 5 or last 5 working days

JacAder — Mon, 09 Dec 2019 21:22:09 GMT

For each ID, keep the VALUE for days that are within the first 5 or last 5 working days.

For example, for id1, 10/3/2019, 10/4/2019 and 10/7/2019 are within the first 5 working days of October 2019 (the first 5 working days in this case are Octboer 1,2,3,4,and 7), while 10/28/2019 and 10/29/2019 are within the last 5 working days of October 2019.

It is something like, first identify the first 5 or last 5 working days for each month, then compare the value in the dataset with the first 5 or last 5 working days of the month.

How to achieve that? Really appreciate your kindly help.

data have;
informat ID $3. DATE mmddyy10. VALUE 2.;
format DATE mmddyy10.;
input  ID DATE VALUE;
cards;
id1	10/3/2019	1
id1	10/4/2019	2
id1	10/7/2019	3
id1	10/8/2019	4
id1	10/9/2019	5
id1	10/10/2019	6
id1	10/11/2019	7
id1	10/14/2019	8
id1	10/15/2019	9
id1	10/16/2019	10
id1	10/17/2019	11
id1	10/18/2019	12
id1	10/22/2019	13
id1	10/23/2019	14
id1	10/28/2019	15
id1	10/29/2019	16
id2	8/9/2018	17
id2	8/14/2018	18
id2	8/15/2018	19
id2	8/20/2018	20
id2	8/21/2018	21
id2	8/21/2018	22
id2	8/22/2018	23
id2	8/24/2018	24
id2	8/27/2018	25
id2	8/30/2018	26
id2	8/31/2018	27
;
run;

Re: keep the VALUE for days that are within the first 5 or last 5 working days

novinosrin — Mon, 09 Dec 2019 22:12:58 GMT

Hi @JacAder GROUPFORMAT with key indexing is fun way




data have;
informat ID $3. DATE mmddyy10. VALUE 2.;
format DATE mmddyy10.;
input  ID DATE VALUE;
cards;
id1	10/3/2019	1
id1	10/4/2019	2
id1	10/7/2019	3
id1	10/8/2019	4
id1	10/9/2019	5
id1	10/10/2019	6
id1	10/11/2019	7
id1	10/14/2019	8
id1	10/15/2019	9
id1	10/16/2019	10
id1	10/17/2019	11
id1	10/18/2019	12
id1	10/22/2019	13
id1	10/23/2019	14
id1	10/28/2019	15
id1	10/29/2019	16
id2	8/9/2018	17
id2	8/14/2018	18
id2	8/15/2018	19
id2	8/20/2018	20
id2	8/21/2018	21
id2	8/21/2018	22
id2	8/22/2018	23
id2	8/24/2018	24
id2	8/27/2018	25
id2	8/30/2018	26
id2	8/31/2018	27
;
run;


data want;
 do _n_=1 by 1 until(last.DATE);
  set have;
  by id groupformat date;
  format date monyy. date1 mmddyy10.;;
  date1=date;
  array t(-3652:33237) _temporary_;
  if _n_=1 then do;
   do _iorc_=intnx('mon',date,0) by 1 until(_c=5);
    if weekday(_iorc_)  in (1,7) then continue;
	 _c=sum(_c,1);
     t(_iorc_)=1;
   end;
   do _iorc_=intnx('mon',date,0,'e') by -1 until(_c=10);
    if weekday(_iorc_)  in (1,7) then continue;
	 _c=sum(_c,1);
     t(_iorc_)=1;
   end;
  end;
  if t(date) then output;
end;
call missing(of t(*));
drop _:;
run;

Re: keep the VALUE for days that are within the first 5 or last 5 working days

JacAder — Mon, 09 Dec 2019 23:23:09 GMT

Many thanks @novinosrin !!

Just one more thing, your code is very concise, I have no idea how to modify the code to create another column, say, "order of the working day", indicating the order of those working dates within the month. For example, 10/3/2019 is the 3rd of the working days in October 2019, and 10/4/2019 is the 4th of the working days in October 2019, etc.

Keep the number of working days for each month would be great.

Re: keep the VALUE for days that are within the first 5 or last 5 working days

novinosrin — Tue, 10 Dec 2019 00:03:50 GMT

Hi @JacAder Incomplete communication leads to DING DONG in the pendulum causing delays. Please post the requirement in full so we can offer solutions in one shot. Anyways, try the modified version below and let us know. if anything, I can only look into it tomorrow coz I am in a hurry to catch the bus and go home. But hey, global folks would help when we at Connecticut in Eastern Time are sleeping. Have a good evening


data have;
informat ID $3. DATE mmddyy10. VALUE 2.;
format DATE mmddyy10.;
input  ID DATE VALUE;
cards;
id1	10/3/2019	1
id1	10/4/2019	2
id1	10/7/2019	3
id1	10/8/2019	4
id1	10/9/2019	5
id1	10/10/2019	6
id1	10/11/2019	7
id1	10/14/2019	8
id1	10/15/2019	9
id1	10/16/2019	10
id1	10/17/2019	11
id1	10/18/2019	12
id1	10/22/2019	13
id1	10/23/2019	14
id1	10/28/2019	15
id1	10/29/2019	16
id2	8/9/2018	17
id2	8/14/2018	18
id2	8/15/2018	19
id2	8/20/2018	20
id2	8/21/2018	21
id2	8/21/2018	22
id2	8/22/2018	23
id2	8/24/2018	24
id2	8/27/2018	25
id2	8/30/2018	26
id2	8/31/2018	27
;
run;


data want;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("date") ;
   h.definedata ("workday_order") ;
   h.definedone () ;
 end;
 do _n_=1 by 1until(last.DATE);
  set have;
  by id groupformat date;
  date1=date;
  format date monyy. date1 date9.;
  if _n_=1 then do;
  do _iorc_=intnx('mon',date,0) by 1 until(_c=5);
    if weekday(_iorc_)  in (1,7) then continue;
	 _c=sum(_c,1);
     h.add(key:_iorc_,data:_c);
  end;
  _t=_iorc_;
  do _iorc_=intnx('mon',date,0,'e') by -1 until(_c=10);
    if weekday(_iorc_)  in (1,7) then continue;
	 _c=sum(_c,1);
     workday_order=intck('weekday',_t,_iorc_)+5;
     h.add(key:_iorc_,data:workday_order);
  end;
  end;
  if h.find()=0 then do;
   num_of_workingdays=intck('weekday',intnx('mon',date,0),intnx('mon',date,0,'e'))+1;
   output;
  end;
 end;
 h.clear();
 drop _:;
run;

Re: keep the VALUE for days that are within the first 5 or last 5 working days

JacAder — Tue, 10 Dec 2019 02:33:30 GMT

@novinosrin very grateful for your assistance! sorry for not being able to ask all questions at once, just forgot when I post the question. Have a great evening and thank you again!