BookmarkSubscribeRSS Feed
Azrel
Calcite | Level 5

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 

5 REPLIES 5
kiranv_
Rhodochrosite | Level 12

issue with your code is at below line

 

as act_data t2

 

change to 

 

as t2

 

 

Azrel
Calcite | Level 5

Hi, thanks for your response.

 

I tried making the change, but got the following error:

 

63 FROM CTATFULL.DEBT_RECOVERY_TIMESERIES t1)

64 as t2

65 (transaction swn,

___

78

ERROR 78-322: Expecting a ','.

 

Any ideas?

kiranv_
Rhodochrosite | Level 12

i tried similar query and it works without error

proc sql;
create table want as
select age, sex, count(*) from
(select age, sex from sashelp.class) as t2(age, sex)
group by t2.sex, t2.age
order by t2.age;
Azrel
Calcite | Level 5

My organisation is on SAS 7.11 - would this affect the ability to run the code I'm trying to run?

kiranv_
Rhodochrosite | Level 12

I guess you mean SAS EG version. It should not have impact. 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 5 replies
  • 563 views
  • 0 likes
  • 2 in conversation