I have a PROC SQL - GROUP BY query that isn't performing as expected.

[kenmeaton]

The PROC SQL statement is as follows:

CREATE TABLE Falls_by_month_req_hosp AS
SELECT month,
                COUNT(*) AS med_help
FROM Falls_log
GROUP BY month
ORDER BY month;

The Falls_log table looks like this:

guid	followup_datetime	fl_instance	Outpatient	ER	Hospitalized	Surgery	Institutionalized	month
PDBR-707-ZR5	09SEP24:15:51:00	1	1	0	0	0	0	2024/09
PDEC-217-LX6	20MAY24:11:50:00	1	1	0	0	0	0	2024/05
PDLR-732-JK8	01AUG24:15:26:00	1	1	0	0	1	0	2024/08
PDPV-423-CNM	18JUN24:13:41:00	5	1	0	0	0	0	2024/06
PDRX-646-FDU	11JUL24:11:21:00	4	1	0	0	0	0	2024/07
PDVB-896-HH5	29MAY24:14:02:00	1	1	0	0	0	0	2024/05
PDVX-313-DWN	05AUG24:15:44:00	2	1	0	0	0	0	2024/08
PDWW-140-KR1	26MAR24:15:41:00	1	0	1	0	0	0	2024/03
PDWW-140-KR1	07AUG24:16:24:00	5	1	0	0	0	0	2024/08
PDXM-135-BPM	18JAN24:13:43:00	4	1	0	0	0	0	2024/01
PDXM-135-BPM	29AUG24:09:48:00	11	1	0	0	0	0	2024/08
PDZV-131-KU3	08FEB24:11:24:00	1	1	0	0	0	0	2024/02

The resulting table from the PROC SQL looks like this:

month	med_help
2024/01	1
2024/02	1
2024/03	1
2024/05	1
2024/05	1
2024/06	1
2024/07	1
2024/08	1
2024/08	1
2024/08	1
2024/08	1
2024/09	1

I would expect the GROUP BY to consolidate 2024/05 into one row with a med_help of 2 and 2024/08 into one row with a med_help of 4. Instead, it doesn't group any of the rows. I have two other GROUP BY queries that are correctly consolidating the rows on the month column from different tables. The attributes of all of the month columns is identical with a format of YYMMS7.

[PaigeMiller]

I do not observe this problem when I run your code on your data. I see the expected results. However, my attempts at re-creating your data may in fact have created a different data set than the one you are using, which you have provided via a copy/paste from Excel. We need a SAS data set as working SAS data step code (examples and instructions) and not in any other format.

[ballardw]

By any chance is that "month" variable a character variable?

If so, then this behavior may indicate that you have some values with leading spaces or other non-printed characters.

Try this and see if you get different values for month that appear the same:

proc sql;
   create table work.months as
   select distinct month
   from Falls_log
   order by month
   ;
quit;

Or if

proc sql;
   CREATE TABLE Falls_by_month_req_hosp AS
   SELECT strip(month),
                   COUNT(*) AS med_help
   FROM Falls_log
   GROUP BY strip(month)
   ;
quit;

yields more expected results. If the above result looks "right" then you have leading spaces and the strip function removed them.

---

@kenmeaton wrote:

The PROC SQL statement is as follows:

CREATE TABLE Falls_by_month_req_hosp AS
SELECT month,
                COUNT(*) AS med_help
FROM Falls_log
GROUP BY month
ORDER BY month;

The Falls_log table looks like this:

guid	followup_datetime	fl_instance	Outpatient	ER	Hospitalized	Surgery	Institutionalized	month
PDBR-707-ZR5	09SEP24:15:51:00	1	1	0	0	0	0	2024/09
PDEC-217-LX6	20MAY24:11:50:00	1	1	0	0	0	0	2024/05
PDLR-732-JK8	01AUG24:15:26:00	1	1	0	0	1	0	2024/08
PDPV-423-CNM	18JUN24:13:41:00	5	1	0	0	0	0	2024/06
PDRX-646-FDU	11JUL24:11:21:00	4	1	0	0	0	0	2024/07
PDVB-896-HH5	29MAY24:14:02:00	1	1	0	0	0	0	2024/05
PDVX-313-DWN	05AUG24:15:44:00	2	1	0	0	0	0	2024/08
PDWW-140-KR1	26MAR24:15:41:00	1	0	1	0	0	0	2024/03
PDWW-140-KR1	07AUG24:16:24:00	5	1	0	0	0	0	2024/08
PDXM-135-BPM	18JAN24:13:43:00	4	1	0	0	0	0	2024/01
PDXM-135-BPM	29AUG24:09:48:00	11	1	0	0	0	0	2024/08
PDZV-131-KU3	08FEB24:11:24:00	1	1	0	0	0	0	2024/02

The resulting table from the PROC SQL looks like this:

month	med_help
2024/01	1
2024/02	1
2024/03	1
2024/05	1
2024/05	1
2024/06	1
2024/07	1
2024/08	1
2024/08	1
2024/08	1
2024/08	1
2024/09	1

I would expect the GROUP BY to consolidate 2024/05 into one row with a med_help of 2 and 2024/08 into one row with a med_help of 4. Instead, it doesn't group any of the rows. I have two other GROUP BY queries that are correctly consolidating the rows on the month column from different tables. The attributes of all of the month columns is identical with a format of YYMMS7.

---

[Tom]

What type of variable is MONTH?

Does it have a display FORMAT attached to it?  If so what format?

I would expect that type of behavior if MONTH has actual DATE or DATETIME values and was just using a display format to make two different dates look the same.  

Note if you use PROC FREQ or PROC SUMMARY instead then it will group by the formatted values.

Example

data have;
  do date='29JAN2024'd to '02FEB2024'd;
    month=date;
    output;
  end;
  format date yymmdd10. month yymmd7. ;
run;

proc sql;
select month,count(*) as nobs
 from have
 group by month
;
quit;

proc freq data=have ;
 tables month;
run;

[kenmeaton · Accepted solution]

Tom,

Thanks! You pointed me in the right direction and I have it working. The data is pulled from other tables and the two that worked previously had the conversion:

put(input(gw_call_date,mmddyy10.),yymms7.) AS month

The one that didn't work  was coming from a DATETIME16. format and was converted as follows:

datepart(ffup_dttm) format=yymms7. as month

Changing that to the following fixed the problem"

put(datepart(ffup_dttm),yymms7.) as month