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!
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.
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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.