DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor SGB
Contributor
Posts: 41
Accepted Solution

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

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


Accepted Solutions
Solution
‎12-10-2013 04:22 PM
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Regular Contributor
Posts: 244

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

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).

Contributor SGB
Contributor
Posts: 41

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

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

PROC Star
Posts: 1,233

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

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;
PROC Star
Posts: 1,233

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

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;
Super User
Posts: 17,868

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

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.

Contributor SGB
Contributor
Posts: 41

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

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..

Contributor SGB
Contributor
Posts: 41

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

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

Thanks for your time..

Super User
Super User
Posts: 6,502

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

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

Contributor SGB
Contributor
Posts: 41

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

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..


Contributor SGB
Contributor
Posts: 41

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

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

Super User
Super User
Posts: 6,502

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

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;

Contributor SGB
Contributor
Posts: 41

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

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

Solution
‎12-10-2013 04:22 PM
Respected Advisor
Posts: 3,124

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

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

Contributor SGB
Contributor
Posts: 41

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

- This code works perfectly! Thanks!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 549 views
  • 9 likes
  • 6 in conversation