I hope I've posted this in the right place. I'm a first time user of the forums.
I have a background in SQL and am trying to migrate a SQL case statement using a sub-query into SAS as the tables that I will use to generate the data sit in a SAS dataset. The table I am querying contains client financial transactions and I need to sum them by the SWN identifier, debt identifier, year, month, and transaction type.
The code is as follows:
select t2.swn,
t2.transaction_debt_number,
t2.original_debt_id,
t2.transaction_year,
t2.transaction_month,
sum(case
when t2.transaction_type in ('Debt Recovery - cash receipt', 'Debt Recovery - Offset') then transaction_value
else 0
end) as DEBT_REC,
sum(case
when transaction_type = 'Debt Adjustment' then transaction_value
else 0
end) as DEBT_ADJ,
sum(case
when transaction_type = 'Debt Created' then transaction_value
else 0
end) as DEBT_EST,
sum(case
when transaction_type = 'Debt Writeoff' then transaction_value
else 0
end) as DEBT_WO,
sum(case
when transaction_type in ('Debt Recovery - cash receipt', 'Debt Recovery - Offset') then transaction_value
else 0
end) as DEBT_REC,
t2.transaction_service_code,
t2.transaction_debt_type
from
(SELECT t1.swn,
t1.transaction_debt_number,
t1.original_debt_id,
t1.transaction_value,
t1.transaction_type,
year(t1.transaction_date) as transaction_year,
month(t1.transaction_date) as transaction_month,
t1.transaction_service_code,
t1.transaction_debt_type
FROM CTATFULL.DEBT_RECOVERY_TIMESERIES t1)
as act_data t2
(transaction swn,
transaction_debt_number,
original_debt_id,
transaction_value,
transaction_type,
transaction_year,
transaction_month,
transaction_service_code,
transaction_debt_type)
group by t2.swn,
t2.original_debt_id,
t2.year,
t2.month
;
QUIT;
The sub-query, when run on it's own, works. But when I try to use it as a sub-query, I get an error in the following log:
PROC SQL;
CREATE TABLE WORK.FILTER_FOR_DEBT_RECOVERY_TIMESER AS
select t2.swn,
t2.transaction_debt_number,
t2.original_debt_id,
t2.transaction_year,
t2.transaction_month,
sum(case
when t2.transaction_type in ('Debt Recovery - cash receipt', 'Debt Recovery - Offset') then transaction_value
else 0
end) as DEBT_REC,
sum(case
when transaction_type = 'Debt Adjustment' then transaction_value
else 0
end) as DEBT_ADJ,
sum(case
when transaction_type = 'Debt Created' then transaction_value
else 0
end) as DEBT_EST,
sum(case
when transaction_type = 'Debt Writeoff' then transaction_value
else 0
end) as DEBT_WO,
sum(case
when transaction_type in ('Debt Recovery - cash receipt', 'Debt Recovery - Offset') then transaction_value
else 0
end) as DEBT_REC,
t2.transaction_service_code,
t2.transaction_debt_type
from
(SELECT t1.swn,
The SAS System 10:38 Wednesday, April 11, 2018
t1.transaction_debt_number,
t1.original_debt_id,
t1.transaction_value,
t1.transaction_type,
year(t1.transaction_date) as transaction_year,
month(t1.transaction_date) as transaction_month,
t1.transaction_service_code,
t1.transaction_debt_type
FROM CTATFULL.DEBT_RECOVERY_TIMESERIES t1)
as act_data t2
__
22
ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.
ERROR 76-322: Syntax error, statement will be ignored.
(transaction swn,
transaction_debt_number,
original_debt_id,
transaction_value,
transaction_type,
transaction_year,
transaction_month,
transaction_service_code,
transaction_debt_type)
order by swn
;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds
Any help would be greatly appreciated as I've been searching the forums for how to fix this but haven't been able to find a solution that works. Thanks in advance!
Cheers,
Jill
