How to create a macro to see number of sun,mon,tues to sat in each month in a year

[SGB]

I am in the process of creating a table consisting number of Sun,Mon,Tues,Wed,Thurs,Fri,Sat in each month in 2013 and 2014.

I tried something with INTNX and INTCK functions.. and I thought I would seek the help of the community members for suggestions.

Any inputs is hightly appreciated.

Thanks

Bhuvana

[snoopy369]

Please post an example of your requested output.  I don't see why you'd use a macro to do this, it can be done easily without macros, but it's unclear exactly what you want (and why you'd build an entire table of all of the calendar days just to do it).

[SGB]

Hi

I wanted to make this a dynamic code to ge the same for any given year. Also this is part of the larger stored process code.. hence was thinking to use macros.. instead of DO loops

[Quentin]

Brute force, perhaps:

data a;
  do date="01Jan2012"d to "31Dec2013"d;
    year=year(date);
    output;
  end;
run;

proc freq data=a;
  tables year*date/missing list;
  format date downame.;
run;

[Quentin]

Oops, you wanted by month and year...

data a;
  do date="01Jan2012"d to "31Dec2013"d;
    date2=date;
    output;
  end;
run;

proc freq data=a;
  tables date*date2/missing list;
  format date monyy7. date2 downame.;
run;

[Reeza]

Considering this takes less than a full second to process I don't know why you'd even bother trying to make it efficient as well.

[SGB]

I was just using a data step without arrays and do loops. Tom's and Hai.kuo methods are what I consider the efficient way of programming..

[SGB]

Quentin! This is great! Never thought of doing this way!..

Thanks for your time..

[Tom]

data numdays;

  do year=2013 to 2014 ;

   do month=1 to 12;

    start=mdy(month,1,year);

    end=intnx('month',start,1)-1;

    format start end date9.;

    array day day1-day7;

    do i=1 to 7;

      day(i) = intck(cats('WEEK.',i),start-1,end);

    end;

    total = sum(of day1-day7);

    put (year month total day1-day7) (4.);

  end;

  put;

output;

end;

run;

2013   1  31   4   4   5   5   5   4   4

2013   2  28   4   4   4   4   4   4   4

2013   3  31   5   4   4   4   4   5   5

2013   4  30   4   5   5   4   4   4   4

2013   5  31   4   4   4   5   5   5   4

2013   6  30   5   4   4   4   4   4   5

2013   7  31   4   5   5   5   4   4   4

2013   8  31   4   4   4   4   5   5   5

2013   9  30   5   5   4   4   4   4   4

2013  10  31   4   4   5   5   5   4   4

2013  11  30   4   4   4   4   4   5   5

2013  12  31   5   5   5   4   4   4   4

2014   1  31   4   4   4   5   5   5   4

2014   2  28   4   4   4   4   4   4   4

2014   3  31   5   5   4   4   4   4   5

2014   4  30   4   4   5   5   4   4   4

2014   5  31   4   4   4   4   5   5   5

2014   6  30   5   5   4   4   4   4   4

2014   7  31   4   4   5   5   5   4   4

2014   8  31   5   4   4   4   4   5   5

2014   9  30   4   5   5   4   4   4   4

2014  10  31   4   4   4   5   5   5   4

2014  11  30   5   4   4   4   4   4   5

2014  12  31   4   5   5   5   4   4   4

[SGB]

Hi Tom Thanks for your help..Your code was useful.. There might be a bug in the code to see the right output.. I will post it tomorrow..

[SGB]

Hi Tom

If I switch the output into the inner do loop I get the intended output in your code..

If I want to want to know the dates corresponding to those days along with number of MTWTHFSS in a month in a year.. How do I modify the code.

Is it possible to tweak this code and get the dates.. OR do I need to follow a diff approach?

Year Month Day     num date

2013 Jan     Sun    4      Jan 6 2013

2013 Jan      Sun   4      Jan 13 2013

...

2013 Dec     Sat   4      Jan 7 2013

2013 Dec      Sat   4      Jan 14 2013

Something like this. 

I will appreciate your help in this regard.

Thanks

Bhuvana

[Tom]

Not sure I understand what you want. Looks like you want both the summary count of the number of Sundays in the month AND the list of individual Sundays?  At which point isn't it just easier to generate all of the dates and then summarize them to get the variable NUM in your report?

data days;

  length year 8 monthn 8 dayn 8 month $3 day $3 date 8 ;

  format date date9.;

  do year=2013 to 2014 ;

    do date=mdy(1,1,year) to mdy(12,31,year);

       year=year(date);

       monthn=month(date);

       dayn=weekday(date);

       month=put(date,monname3.);

       day=put(date,downame3.);

       output;

    end;

  end;

run;

proc sort ;

  by year monthn dayn ;

run;

data want ;

   do until(last.dayn);

     set days;

     by year monthn dayn ;

     num=sum(num,1);

   end;

   do until(last.dayn);

     set days;

     by year monthn dayn;

     output;

   end;

run;

proc print;

  var year month day num date;

run;

[SGB]

Thanks Tom for your time..

I think the second/above method you have proposed will solve my purpose.. Appreviate your time and help.

Thanks

SGB

[Haikuo · Accepted solution]

Similar to Tom's solution:

data want;

array wk(7) sun mon tue wed thu fri sat ;

  do year=2013 to 2014;

  do month=1 to 12;

  do day = intnx('month',mdy(month,1,year),0,'b') to intnx('month',mdy(month,1,year),0,'e');

  wk(weekday(day))+1;

end;

output;

call missing (of wk(*));

end;

  end;

  drop day;

run;

Haikuo

[SGB]

- This code works perfectly! Thanks!