topic Re: problem with calculated variable in proc sql in SAS Procedures

problem with calculated variable in proc sql

deleted_user — Wed, 04 Aug 2010 18:07:15 GMT

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

Re: problem with calculated variable in proc sql

sbb — Wed, 04 Aug 2010 18:17:53 GMT

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.

Re: problem with calculated variable in proc sql

deleted_user — Wed, 04 Aug 2010 19:39:13 GMT

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

Re: problem with calculated variable in proc sql

sbb — Wed, 04 Aug 2010 19:44:33 GMT

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.

Re: problem with calculated variable in proc sql

SASKiwi — Wed, 04 Aug 2010 22:00:22 GMT

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.