Desktop productivity for business analysts and programmers

How to summarize data by month

Posts: 24

How to summarize data by month

I'm a programmer trying to convert to EG for some projects where I start with reasonably clean data. In this case, I have patient level data that I want to summarize and report by facility and month.

I've got a query that pulls the variables I want and calculates 2 new columns. I include fac_id (facility) and disdate (discharge date) under "select data" and selected them as Summary groups. However, my selected table only reformats the discharge date to monyy5. Basically I want the format applied at the group-by line so that I only have

What do I need to do? Is it possible to summarize by month at this step or do I have to add another task to do this? (below is my generated SQL)

CREATE TABLE WORK.Query1_for_analysis200808 AS SELECT analysis200808.fac_id,
analysis200808.DISDATE FORMAT=MONYY5.0 label="Discharge Month",
(MEAN(analysis200808.Nurse_Courtesy ,
analysis200808.Nurse_CallButton ,
analysis200808.Nurse_Attitude ,
analysis200808.Nurse_Attention ,
analysis200808.Nurse_KeptInformed ,
analysis200808.Nurse_Skill )) FORMAT=5.2 label="Nursing Section Score" AS Nurse_section,
(MEAN(analysis200808.Doc_TimeSpent ,
analysis200808.Doc_ConcernQuest ,
analysis200808.Doc_KeptInformed ,
analysis200808.Doc_Courtesy ,
analysis200808.Doc_Skill )) FORMAT=5.2 label="Physician Section Score" AS Doc_section,
analysis200808.Overall_Likelihood FORMAT=5.2 label="Overall Likelihood to Recommend"
FROM EC100035.ANALYSIS200808 AS analysis200808
WHERE analysis200808.type = "E"
GROUP BY analysis200808.fac_id, analysis200808.DISDATE;
Super User
Posts: 5,919

Re: How to summarize data by month

Posted in reply to JenHarper
I'm sitting on a swedish version of EG, so I'm not sure of the exact names of menu choices etc in english. But here we go. I assume that you user "filter and query".

First remove DISDATE from "Select data".
Then create a calculated column by using put(analysis200808.DISDATE,MONYY5.) as expression. Add it to "select data".
Then select the "Edit groups" button, and select your newly calculated column.

Hope this helps/works.

Data never sleeps
Posts: 24

Re: How to summarize data by month

Thank you Linus. That did both help and work. I was so puzzled by Enterprise Guide and SQL that I overlooked the obvious SAS function I knew I wanted.
Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation