BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SGB
Obsidian | Level 7 SGB
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

15 REPLIES 15
snoopy369
Barite | Level 11

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Quentin
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Reeza
Super User

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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

Thanks for your time..

Tom
Super User Tom
Super User

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7 SGB
Obsidian | Level 7

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
Onyx | Level 15

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
Obsidian | Level 7 SGB
Obsidian | Level 7

- This code works perfectly! Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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