topic Issue with Sorting in Chronological Order in SAS Programming

Issue with Sorting in Chronological Order

unwashedhelimix — Mon, 25 Nov 2024 09:44:22 GMT

I have the following code:

data docvisits;
	input patientID $ weekday $ score;
	cards;
101	Friday    15
163	Wednesday 11
104	Friday    23
163	Thursday  13
123	Tuesday	10
104	Monday	20
157	Friday	21
101	Monday	10
112	Tuesday	11
157	Tuesday	10
123	Monday	9
123	Friday	9
101	Tuesday	11
112	Monday	9
157	Thursday  18
174	Monday	12
;

proc format;
    value $weekdayfmt
        'Monday' = 1
        'Tuesday' = 2
        'Wednesday' = 3
        'Thursday' = 4
        'Friday' = 5;
run;

data docvisits_sorted;
    set docvisits;
    daynum = input(weekday, $weekdayfmt.); 
run;


proc sql;
   select weekday, count(distinct patientID) as Total_Patients
   from docvisits_sorted
   group by weekday
   order by daynum;
quit;

However, there is no output. My goal is to list the days of the week that visits were on and the total number of patients who visited each day.

Prior to the one above, I tried:

data docvisits;
	input patientID$ weekday$5-13 score;
	cards;
101	Friday    15
163	Wednesday 11
104	Friday    23
163	Thursday  13
123	Tuesday	10
104	Monday	20
157	Friday	21
101	Monday	10
112	Tuesday	11
157	Tuesday	10
123	Monday	9
123	Friday	9
101	Tuesday	11
112	Monday	9
157	Thursday  18
174	Monday	12
;

proc sql;
   select weekday, count(distinct patientID) as Total_Patients
   from docvisits
   group by weekday;
quit;

However, this does not output the days in chronological order.

How can I fix my problem?

Re: Issue with Sorting in Chronological Order

Patrick — Mon, 25 Nov 2024 10:34:13 GMT

Try below.

proc format;
 invalue $weekdayfmt
 'Monday' = 1
 'Tuesday' = 2
 'Wednesday' = 3
 'Thursday' = 4
 'Friday' = 5;
run;

proc sql;
  select weekday, count(distinct patientID) as Total_Patients
  from docvisits
  group by weekday
  order by input(weekday, $weekdayfmt.)
  ;
quit;

Please note that you need to create in INformat (proc format INvalue) for reading data with an input statement. Formats are for writing (put statement).

Re: Issue with Sorting in Chronological Order

PaigeMiller — Mon, 25 Nov 2024 10:56:34 GMT

Calendar days should be represented as valid numeric SAS date values. Then sorting is easy and determining the day of week is easy. This is an extremely good habit to get into, and will save you tons of effort in the future.

As soon as you make calendar information character, you then have to go through extra steps to make it sort properly.

Re: Issue with Sorting in Chronological Order

Ksharp — Tue, 26 Nov 2024 02:17:50 GMT

Your PROC FORMAT is NOT right.

data docvisits;
	input patientID $ weekday :$20. score;
	cards;
101	Friday    15
163	Wednesday 11
104	Friday    23
163	Thursday  13
123	Tuesday	10
104	Monday	20
157	Friday	21
101	Monday	10
112	Tuesday	11
157	Tuesday	10
123	Monday	9
123	Friday	9
101	Tuesday	11
112	Monday	9
157	Thursday  18
174	Monday	12
;

proc format;
    invalue weekdayfmt
        'Monday' = 1
        'Tuesday' = 2
        'Wednesday' = 3
        'Thursday' = 4
        'Friday' = 5;
run;

data docvisits_sorted;
    set docvisits;
    daynum = input(weekday, weekdayfmt.); 
run;


proc sql nowarn;
   select distinct weekday, count(distinct patientID) as Total_Patients
   from docvisits_sorted
   group by weekday
   order by daynum;
quit;