PROC FREQ order for day of the week (downame.)

[Quentin]

Hi All,

Suppose I have a date variable, and I want to use PROC FREQ to make a frequency tables of day of the week (Monday-Sunday), and I want the frequency table to be ordered reasonably (Mon-Sun, or Sun-Sat, or whatever).

But my data is sparse, so might look like:

data have ;
  input dt date9. ;
  cards ;
03JUN2024
07JUN2024
11JUN2024
13JUN2024
;
run ;

If I use:

proc freq data=have ;
  tables dt ;
  format dt downame. ;
run ;

I get the default order=internal, which means the day order looks weird:

        dt    Frequency
 ----------------------
    Monday           1
    Friday           1
   Tuesday           1
  Thursday           1

Order=formatted would be alphabetical, no better.

The best I could do was sort day by day of the week (1-7), and then use order=data:

proc sql ;
  create table want as 
  select * from have
  order by put(dt,weekday.)
  ;
quit ;

proc freq data=want order=data;
  tables dt ;
  format dt downame. ;
run ;

which works, but seems like a lot of hoops.

        dt    Frequency
 ----------------------
    Monday           1
   Tuesday           1
  Thursday           1
    Friday           1

Am I missing something obvious?

I know it could be done with PROC TABULATE and preloadformat, but I want to stick with PROC FREQ to get some statistics out of it.

[Reeza]

Not super useful I'm afraid

data have ;
  input dt date9. ;
  dt2=dt;
  cards ;
03JUN2024
07JUN2024
11JUN2024
13JUN2024
;
run ;


proc freq data=have order=formatted;
  tables dt2*dt / list;
  format dt2 weekday. dt downame. ;
run ;

[Quentin]

Thanks, in real problem all I want is a table of DayOfTheWeek*Success(1/0) to look if failures differ by day of the week.  So I can't add a variable to the table statement.

I guess another option would be to make a variable with values 1-7, e.g.:

data have ;
  input dt date9. ;
  day=weekday(dt); *returns 1-7 for Sun-Sat ;
  cards ;
03JUN2024
07JUN2024
11JUN2024
13JUN2024
;
run ;


*is there really no built-in format for this? ;
proc format ;
 value dow
   1="Sun"
   2="Mon"
   3="Tue"
   4="Wed"
   5="Thur"
   6="Fri"
   7="Sat"
 ;
run ;

proc freq data=have order=internal;
  tables day ;
  format day dow. ;
run ;

[ballardw · Accepted solution]

Proc Tabulate and PRELOADFMT will not solve this particular problem, at least without a bit of work. You will get

WARNING: The format for variable dt cannot be preloaded. A finite set of formatted values cannot
         be produced from the format.

Use the Weekday function on the date to create a new variable and do the analysis on that. And a custom format to map the number back to days of the week.

[Quentin]

Thanks @ballardw .  Saw this after I wrote an example of that approach.  : )

Shocked there isn't a format out of the box to map 1=Sun ... 7=Sat.

[ballardw]

---

@Quentin wrote:

Thanks @ballardw .  Saw this after I wrote an example of that approach.  : )

Shocked there isn't a format out of the box to map 1=Sun ... 7=Sat.

---

Just like there is no 1=January 2=February etc.

Maybe time to ask about an order= for date values other than data/internal/formatted/freq...

Actually if you only have ONE of these sparse date values then WEEKDAY function +sort and Order=data works.

data have ;
  input dt date9. ;
  wd = weekday(dt);
  cards ;
03JUN2024
07JUN2024
11JUN2024
13JUN2024
;
run ;

proc sort data=have;
   by wd;
run;

proc freq data=have order=data;
   tables dt;
   format dt downame9.;
run;

But if you have multiple variables you have to sort the data by each corresponding day of week created variable (or recreate and re-sort). So if there are multiple variables involved it may be worth the custom day of week format.