BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasspan
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

2 REPLIES 2
TomKari
Onyx | Level 15

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

sasspan
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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