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,
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
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.
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.
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;
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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.