topic Weekdays of a Month in SAS Procedures

Weekdays of a Month

renjithr — Wed, 11 Apr 2012 23:34:47 GMT

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.

Re: Weekdays of a Month

Reeza — Thu, 12 Apr 2012 00:03:23 GMT

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.

Re: Weekdays of a Month

renjithr — Thu, 12 Apr 2012 00:06:58 GMT

Hi Reeza,

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

Re: Weekdays of a Month

Linlin — Thu, 12 Apr 2012 01:27:01 GMT

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

Re: Weekdays of a Month

Ksharp — Thu, 12 Apr 2012 05:35:52 GMT

What output do you need ?

Re: Weekdays of a Month

andreas_lds — Thu, 12 Apr 2012 15:44:13 GMT

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;

Re: Weekdays of a Month

renjithr — Thu, 12 Apr 2012 17:09:50 GMT

Thanks so much for sharing your thoughts everyone!