topic Re: Group data into specific days of the week in SAS Programming

Group data into specific days of the week

podarum — Sun, 27 Mar 2022 12:06:16 GMT

Hi, I have data with a date (closing_date) spanning out to 120 days.. I'm trying to group them by different Thursday to Wednesday groupings and name them like "March-10-22 - March-16-22'... and show about 17 groupings each one with a Thr-Wed title..

Thanks

Re: Group data into specific days of the week

yabwon — Sun, 27 Mar 2022 13:35:22 GMT

I would try something like:

data have;
  do closing_dat = today() - 60 to today() + 60;
    output;
  end;
  format closing_dat yymmdd10.;
run;

%let last_day=4; /* 4 = Thursday */
%let format = worddate12.;

data want;
  do _N_ = 1 by 1 until(weekday(closing_dat) = &last_day. or EOF);
    set have end=EOF;

    if _N_=1 then dt = closing_dat;

    if weekday(closing_dat) = &last_day. or EOF then
      label = catx(" - ",put(dt,&format.),put(closing_dat,&format.));
  end;

  do _N_ = 1 to _N_;
    set have;
    output;
  end;
  drop dt;
run;

Bart

Re: Group data into specific days of the week

ballardw — Mon, 28 Mar 2022 15:33:31 GMT

My take:

data example;
   somedate = '15Feb2022'd;
   do closing_date= somedate to (somedate+120);
      weekstart = intnx('week.5',closing_date,0,'B');
      weekend  = intnx('week.5',closing_date,0,'E');
      text = catx('-',put(weekstart,worddate12.),put(weekend,worddate12.));
      output;
   end;

   format closing_date date9. weekstart weekend date9.;
run;

The INTNX function can use offsets to define the boundary of the interval.

I show creating some text as requested but I would, if this were my project stick with the WEEKSTART variable and not bother showing the end unless I went to considerable work to use a function to create a possibly obnoxious format. Why would I do that? Because none of the text formats for dates except YYYYMMDD types sort at all correctly and as soon as you go to make a table with that text your going to have something like

Apr 14 2022

Apr 21 2022

Apr 28 2022

Then Feb

Then Jun

Then Mar

Then May

(for example with 120 days starting in Feb)

Re: Group data into specific days of the week

podarum — Thu, 31 Mar 2022 12:57:04 GMT

Thank you , this is great

Re: Group data into specific days of the week

PaigeMiller — Thu, 31 Mar 2022 13:08:57 GMT

Although I haven't worked through all the details, I believe you can create a custom format using PROC FCMP to accomplish this. Here's a similar example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/p1gg77jyhc9s42n1f1vjyx2hsg8b.htm