topic Re: I have a PROC SQL - GROUP BY query that isn't performing as expected. in SAS Programming

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

kenmeaton — Wed, 09 Oct 2024 16:48:10 GMT

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.

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

PaigeMiller — Wed, 09 Oct 2024 17:10:31 GMT

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.

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

ballardw — Wed, 09 Oct 2024 17:06:43 GMT

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.

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

Tom — Wed, 09 Oct 2024 17:22:45 GMT

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;

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

kenmeaton — Wed, 09 Oct 2024 17:47:52 GMT

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