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;
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;
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;
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.
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;
@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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: