How to write a nested ROW_NUMBER() OVER (PARTITION BY ...) and CASE WHEN statement in SAS?

[ysmkc]

Hello, I’m working on a project where I’m trying to replicate a table in SAS that I originally created in SQL. I’ve already completed the necessary joins and filters using Query Builder. However, I’m having trouble with the following column that I need to include in my SELECT statement and save as  tkz_count:     



SELECT
................,
(CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.POLICY_ENDORSEMENT_SK, cc.TKZ_ID ORDER BY 1) = 1 AND e.POLICY_STATUS_ID = 5 THEN 1

WHEN ROW_NUMBER() OVER (PARTITION BY a.POLICY_ENDORSEMENT_SK, cc.TKZ_ID ORDER BY 1) = 1 AND e.POLICY_STATUS_ID = 3 THEN -1

WHEN ROW_NUMBER() OVER (PARTITION BY a.POLICY_ENDORSEMENT_SK, cc.TKZ_ID ORDER BY 1) = 1 AND a.ENDORSEMENT_TYPE_CODE = 28 THEN 1

ELSE 0 END) TKZ_COUNT
FROM ........

I tried using computed columns in query builder and proc sql, but I couldn’t get it to work. Any help would be greatly appreciated. Thanks!

[SASKiwi]

Please post some test data including what you expect TKZ_COUNT to be. It's impossible to give you logic to derive TKZ_COUNT when we have no idea what your SAS version of the data is. It looks like at least three tables are involved in your calculation. 

[Patrick]

The SAS SQL flavour doesn't support windowing functions. 

If your source data all exists in SQL server and you can connect to it via SAS then you could still use your existing code via explicit pass-through SQL and then just load the result set back to SAS to create the target table. 

If that's not possible then I believe you need some SAS data step post processing to replicate this logic.

Below an attempt to replicate such logic - but of course untested so might have gotten it wrong.

proc sql;
  create table blah as ....
  ....
  order by a.POLICY_ENDORSEMENT_SK, cc.TKZ_ID;
quit;


data blah;
  set blah;
  by POLICY_ENDORSEMENT_SK TKZ_ID; 
  TKZ_COUNT=0;
  if first.TKZ_ID then
    do;
      if POLICY_STATUS_ID = 5 THEN TKZ_COUNT=1;
      else if POLICY_STATUS_ID = 3 THEN TKZ_COUNT=-1;
      else if ENDORSEMENT_TYPE_CODE = 28 THEN TKZ_COUNT=1;
    end;
run;

[Tom]

PROC SQL in SAS does not support windowing functions.  So please explain WHAT you are trying to do (instead of just sharing one particular implementation).

Also I am a little confused by your window function.  What does this mean?

ROW_NUMBER() OVER (PARTITION BY a.POLICY_ENDORSEMENT_SK, cc.TKZ_ID ORDER BY 1) 

What does the 1 in there mean? Is it the actual constant number one?  If so what would be the purpose of ordering observations by a constant value?  Or is it a reference to the first column listed in your SELECT statement?  What column was that?