problem with calculated variable in proc sql

[deleted_user]

I have the following codes but coudn't get it to work. Can anyone please help?

proc sql ;
create table a as
(select
case
when temp_id in ('01997','01998','01999','02001') then 'P'
else 'N'
end as pa_ind,

sum(amount) as amount_tot

from member a
left outer join group g
on a.member_id=g.member_id

where status_cd = 'Y'

group by calculated pa_ind );
quit ;

I kept getting an error message like "An unexpected toke "pa_ind" was found following "group by calculated". Expected token may include: "end-of-statement".

Can anybody help? Thanks

[sbb]

For the most useful feedback from the forum, suggest you share the exact SAS-generated log (with the embedded SAS diagnostic messages) rather than your "pasted" code-snippet.

Scott Barry
SBBWorks, Inc.

[deleted_user]

log file******************************************

proc sql ;
create table a as
(select
case
when temp_id in ('01997','01998','01999','02001') then 'P'
else 'N'
end as pa_ind,

sum(amount) as amount_tot

from member a
left outer join group g
on a.member_id=g.member_id

where status_cd = 'Y'

group by calculated pa_ind );
quit ;

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "pa_ind" was found following "group by
calculated". Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

[IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "pa_ind" was found following "group by calculated". Expected tokens
may include: "END-OF-STATEMENT". SQLSTATE=42601

quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 44.57 seconds
user cpu time 0.04 seconds
system cpu time 0.02 seconds
Memory 406k
OS Memory 6948k
Timestamp 8/04/2010 3:18:26 PM
Page Faults 0

[sbb]

where are the SAS-assigned line numbers? I have to wonder whether you have pasted the "executed" SAS code directly from the log, to go along with the diagnostic messages shared.

Scott Barry
SBBWorks, Inc.

[SASKiwi]

I agree with Scott. The log indicates you are running a query in a DB2 AIX database but your log does not show how you are connecting to that database. If you are using passthru SQL then I would expect a CONNECT statement. If you are using SAS SQL then I would expect a LIBNAME and LIBREFs referencing the database. Please clarify.