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

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

SGB — Tue, 10 Dec 2013 20:24:32 GMT

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

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

snoopy369 — Tue, 10 Dec 2013 20:36:04 GMT

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

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

Quentin — Tue, 10 Dec 2013 20:37:57 GMT

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;

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

Quentin — Tue, 10 Dec 2013 20:44:13 GMT

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;

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

Reeza — Tue, 10 Dec 2013 21:16:20 GMT

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.

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

Tom — Tue, 10 Dec 2013 21:18:25 GMT

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

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

Haikuo — Tue, 10 Dec 2013 21:22:55 GMT

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;

drop day;

run;

Haikuo

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

SGB — Tue, 10 Dec 2013 21:54:45 GMT

- This code works perfectly! Thanks!

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

SGB — Tue, 10 Dec 2013 22:00:51 GMT

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

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

SGB — Tue, 10 Dec 2013 22:01:47 GMT

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

Thanks for your time..

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

SGB — Tue, 10 Dec 2013 22:03:06 GMT

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

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

SGB — Tue, 10 Dec 2013 22:04:09 GMT

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

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

SGB — Thu, 12 Dec 2013 16:20:48 GMT

Hi Hai.Kuo!

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

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

SGB — Thu, 12 Dec 2013 16:22:52 GMT

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

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

Tom — Thu, 12 Dec 2013 17:33:06 GMT

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;

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;

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

SGB — Thu, 12 Dec 2013 19:15:47 GMT

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