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.
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
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
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.