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.
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.
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 ;
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 ;
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.
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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.