BookmarkSubscribeRSS Feed
renjithr
Quartz | Level 8

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.

6 REPLIES 6
Reeza
Super User

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.

renjithr
Quartz | Level 8

Hi Reeza,

If a technician does not visit on any weekdays (see the above example) then only I need to populate the flag.

Linlin
Lapis Lazuli | Level 10

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

Ksharp
Super User

What output do you need ?

andreas_lds
Jade | Level 19

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;

renjithr
Quartz | Level 8

Thanks so much for sharing your thoughts everyone!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 6 replies
  • 931 views
  • 0 likes
  • 5 in conversation