2

I'm looking to be able to perform the equivalent of a count if on a data set similar to the below. I found something similar here, but I'm not sure how to translate it into Enterprise Guide. I would like to create several new columns that count how many date occurrences there are for each primary key by year, so for example:

PrimKey   Date
1         5/4/2014
2         3/1/2013
1         10/1/2014
3         9/10/2014

To be this:

PrimKey   2014   2013
1         2      0
2         0      1
3         1      0

I was hoping to use the advanced expression for calculated fields option in query builder, but if there is another better way I am completely open. Here is what I tried (and failed):

CASE  
   WHEN Date(t1.DATE) BETWEEN Date(1/1/2014) and Date(12/31/2014)
   THEN (COUNT(t1.DATE))
END

But that ended up just counting the total date occurrences without regard to my between statement.

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
Jamison
  • 73
  • 2
  • 8

1 Answers1

3

Assuming you're using Query Builder you can use something like the following:

I don't think you need the CASE statement, instead use the YEAR() function to calculate the year and test if it's equal to 2014/2013. The test for equality will return a 1/0 which can be summed to the total per group. Make sure to include PrimKey in your GROUP BY section of query builder.

sum(year(t1.date)=2014) as Y2014,
sum(year(t2.date)=2013) as Y2013,

I don't like this type of solution because it's not dynamic, i.e. if your years change you have to change your code, and there's nothing in the code to return an error if that happens either. A better solution is to do a Summary Task by Year/PrimKey and then use a Transpose Task to get the data in the structure you want it.

Reeza
  • 20,510
  • 4
  • 21
  • 38
  • This is fantastic, thank you so much! When I added the as Y2014, it threw errors but I'm assuming that's because I put it in a calc column and it adds "as Calculation" to the end. I just set up a different calc column for each year and it worked great. I had no idea this would work like a sumif. Thanks again! – Jamison Feb 06 '15 at 16:51
  • I'm completely ignorant of the query builder, but a dynamic solution would be to `select primkey, year(date), sum(1)` and `group by primkey, calculated year`. But since sas sql doesn't use `pivot`, you'd have to add a `proc transpose` as a second step. EDIT: And rereading the end of your comment, maybe that's the same thing as what you already suggested. – DWal Feb 06 '15 at 17:00