Help using Base SAS procedures

Weekdays of a Month

Reply
Frequent Contributor
Posts: 122

Weekdays of a Month

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.

Super User
Posts: 19,817

Re: Weekdays of a Month

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.

Frequent Contributor
Posts: 122

Re: Weekdays of a Month

Hi Reeza,

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

Super Contributor
Posts: 1,636

Re: Weekdays of a Month

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

Super User
Posts: 10,035

Re: Weekdays of a Month

What output do you need ?

Super Contributor
Posts: 345

Re: Weekdays of a Month

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;

Frequent Contributor
Posts: 122

Re: Weekdays of a Month

Posted in reply to andreas_lds

Thanks so much for sharing your thoughts everyone!

Ask a Question
Discussion stats
  • 6 replies
  • 325 views
  • 0 likes
  • 5 in conversation