Hello All,
I have a requirement where I have a dataset which has the days a technician visits a customer.We are only considering weekdays(Mon-Fri).If a technician did not visit the a particular customer
on a weekday then I need to identify that and populate a flag as "NO Visit".Is there any way to achive this based on the below example?
eg: Customer_name TechVist_day
John 02JAN2012
John 03JAN2012
John 05JAN2012
John 06JAN2012
John 09JAN2012
Mary 02JAN2012
Mary 03JAN2012
Sam 03JAN2012
Sam 04JAN2012
From the above example I need to populate the flag as "NO Visit" for 04JAN2012 for the customer John and same with customer Sam for 02JAN2012.
Please share your thoughts.
Sorry not the correct answer to your question.You're looking to fill in missing dates/weekdays instead.
There was a similar question last week on here, so some searching will probably turn up an answer.
Hi Reeza,
If a technician does not visit on any weekdays (see the above example) then only I need to populate the flag.
data have;
input Customer_name : $8. TechVist_day date9.;
format TechVist_day date9.;
cards;
John 02JAN2012
John 03JAN2012
John 05JAN2012
John 06JAN2012
John 09JAN2012
Mary 02JAN2012
Mary 03JAN2012
Sam 03JAN2012
Sam 04JAN2012
Sam 11APR2012
;
data want;
set have;
weekday=weekday(TechVist_day)-1;
data want1;
set want;
by Customer_name;
if first.Customer_name or lag(weekday)=5 then group+1;
run;
data want2;
set want1;
by group;
if last.group;
data want3(keep=group weekday);
set want2;
by group;
do i=1 to weekday;
weekday=i;
output;
end;
run;
data final;
length flag $ 10;
merge want1(in=a) want3(in=b);
by group weekday;
if not a then flag='no visit';
else flag=' ';
run;
proc print;run;
Customer_ TechVist_
Obs flag name day weekday group
1 John 02JAN2012 1 1
2 John 03JAN2012 2 1
3 no visit . 3 1
4 John 05JAN2012 4 1
5 John 06JAN2012 5 1
6 John 09JAN2012 1 2
7 Mary 02JAN2012 1 3
8 Mary 03JAN2012 2 3
9 no visit . 1 4
10 Sam 03JAN2012 2 4
11 Sam 04JAN2012 3 4
12 Sam 11APR2012 3 4
Linlin
What output do you need ?
Can be done in one data step. Still ignoring public holidays?
data work.have;
length CustomerName $ 10 TechVisitDay 8;
format TechVisitDay ddmmyyp10.;
informat TechVisitDay date.;
input CustomerName TechVisitDay;
datalines;
John 02JAN2012
John 03JAN2012
John 05JAN2012
John 06JAN2012
John 09JAN2012
Mary 02JAN2012
Mary 03JAN2012
Sam 03JAN2012
Sam 04JAN2012
;
run;
data work.want;
set work.have;
by CustomerName;
length
lastVisit saved day missingStart missingEnd 8
Flag $ 20;
retain lastVisit;
if first.CustomerName then do;
/* get first monday */
missingStart = nwkdom(1, 2, month(TechVisitDay), year(TechVisitDay));
end;
else do;
missingStart = intnx('DAY', lastVisit, 1);
end;
missingEnd = intnx('DAY', TechVisitDay, -1);
saved = TechVisitDay;
do day = missingStart to missingEnd;
/* 1 = Sunday, 7 = Saturday */
if 1 < weekday(day) < 7 then do;
TechVisitDay = day;
Flag = 'No Visit';
output;
end;
end;
Flag = ' ';
TechVisitDay = saved;
output;
lastVisit = TechVisitDay;
keep CustomerName TechVisitDay Flag;
run;
Thanks so much for sharing your thoughts everyone!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.