Desktop productivity for business analysts and programmers

Losing Months When Aggregating in EG

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Losing Months When Aggregating in EG

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.


Accepted Solutions
Solution
‎01-04-2013 03:29 PM
PROC Star
Posts: 1,146

Re: Losing Months When Aggregating in EG

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

View solution in original post


All Replies
Solution
‎01-04-2013 03:29 PM
PROC Star
Posts: 1,146

Re: Losing Months When Aggregating in EG

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

Contributor
Posts: 23

Re: Losing Months When Aggregating in EG

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!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 143 views
  • 0 likes
  • 2 in conversation