Desktop productivity for business analysts and programmers

How to summarize data by month

Reply
Contributor
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)

[pre]
PROC 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;
QUIT;
[/pre]
Esteemed Advisor
Posts: 5,198

Re: How to summarize data by month

Hi,
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.

Linus
Data never sleeps
Contributor
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
  • 219 views
  • 0 likes
  • 2 in conversation