topic Re: Losing Months When Aggregating in EG in SAS Enterprise Guide

Losing Months When Aggregating in EG

sasspan — Fri, 04 Jan 2013 18:50:16 GMT

Hello,

I am trying to get totals by month, when I aggregate in the query builder by cl_id and month, if a claimant has no transactions in a month they do not show up in the data set for the month. Let's say I am looking for the whole year of 2012, and a claimant has transactions in month 1-10, I want to see the lines for month 11 and 12, with values as zero. Having a hard time figuring out a good case statement for this. Let me know if you can help.

Re: Losing Months When Aggregating in EG

TomKari — Fri, 04 Jan 2013 20:29:37 GMT

The bad news, I don't know of an easy way to do it in query builder, which is based on SQL.

The good news, there's an easy way to do it in "Summary Statistics" (PROC MEANS):

1. Start a Summary Statistics task against your table.

2. Use CL_ID and Month as your Classification variables, and your quantitative variables of interest as your Analysis variables.

3. In Statistics, select whichever statistics are of interest. Don't select any plots, as you're not going to print anything.

4. In Results, turn ON "Save statistics to dataset", and turn OFF "Show statistics". This is a great feature of this task that will give you a summary dataset, without a report. If you don't like the name SAS picks for the dataset, you can change it.

5. You shouldn't need to change Titles or Properties.

Now the fun part! If you run it now, your result will have exactly the same problem. To fix it, we need to use a PROC MEANS feature that isn't built into EG.

i) Click Preview Code

ii) On the preview screen, click Insert Code

iii) Find the line that begins "PROC MEANS DATA= blah, blah, blah". This should be the start of a number of lines ending with a semicolon. Double click the "<double-click to enter code>" item BEFORE the semicolon, and type COMPLETETYPES

This will tell PROC MEANS to give you the full set of CLASS crossings.

Hit OK to finish inserting code, close the preview window, and let 'er rip!

Tom

Re: Losing Months When Aggregating in EG

sasspan — Fri, 04 Jan 2013 20:37:35 GMT

Thanks Tom! I did brute force 20 case statements instead while waiting for a reply, but I will use this in the future, and really appreciate your time!