Proc Tabulate: Reordering a Formatted Variable

Question

I created a day variable using the following code:

DAY=datepart(checkin_date_time); /*example of checkin_date_time 1/1/2014 4:44:00*/
format DAY DOWNAME.;

Sample Data:

   ID   checkin_date_time      Admit_Type     BED_ORDERED_TO_DISPO
   1      1/1/2014 4:40:00        ICU               456
   2      1/1/2014 5:64:00        Psych             146
   3      1/1/2014 14:48:00       Acute             57
   4      1/1/2014 20:34:00        ICU              952
   5      1/2/2014 10:00:00        Psych            234
   6      1/2/2014 3:48:00        Psych             846
   7      1/2/2014 10:14:00       ICU               90
   8      1/2/2014 22:27:00        ICU              148

I want to analyze some data using Proc Tab where day is one of the class variables and have the day of week appear in chronological order in the output; however, the output table begins with Tuesday. I would like it to start with Sunday. I've read over the the following page http://support.sas.com/resources/papers/proceedings11/085-2011.pdf and tried the proc format invalue code but it's producing a table that where the "day of week" = "21". Not quite sure where to go from here.

Thanks!

 proc format;
 invalue day_name
 'Sunday'=1
 'Monday'=2
 'Tuesday'=3
 'Wednesday'=4
 'Thursday'=5
 'Friday'=6
 'Saturday'=7;
 value day_names
 1='Sunday'
 2='Monday'
 3='Tuesday'
 4='Wednesday'
 5='Thursday'
 6='Friday'
 7='Saturday';
 run; 

 data Combined_day;
 set Combined; 
 day_of_week = input(day,day_name.);
 run; 

 proc tabulate data = Combined_day;
 class  Day Admit_Type;
 var BED_ORDERED_TO_DISPO ;
 format day_of_week day_names.;
 table Day*Admit_Type, BED_ORDERED_TO_DISPO * (N Median); 
 run;

Parfait Parfait · Accepted Answer · 2019-12-02T18:32:25

Fundamentally, you are confusing actual values with displayed values (i.e., formats). Specifically, datepart extracts the date portion out of a date/time field. Then, applying a format only changes how it is displayed not actual underlying value. So below DAY never contains the character values of 'WEDNESDAY' or 'THURSDAY' but original integer value (19724 and 19725).

DAY = datepart(checkin_date_time);   // DATE VALUE
format DAY DOWNAME.;                 // FORMATTED DATE VALUE (SAME UNDERLYING DATE VALUE)

Consider actually assigning a column as weekday value using WEEKDAY function. Then apply your user-defined format for proc tabulate.

data Combined_day;
    set Combined;

    checkin_date = datepart(checkin_date_time);  // NEW DATE VALUE (NO TIME)
    format checkin_date date9.;

    checkin_weekday = weekday(checkin_date);     // NEW INTEGER VALUE OF WEEKDAY
run;


proc tabulate data = Combined_day;
    class checkin_weekday Admit_Type;
    var BED_ORDERED_TO_DISPO ;
    format checkin_weekday day_names.;           // APPLY USER DEFINED FORMAT

    table checkin_weekday*Admit_Type, BED_ORDERED_TO_DISPO * (N Median); 
run;

Proc Tabulate: Reordering a Formatted Variable

1 Answers