Error in when running the Proc sql.

[deleted_user]

Hi,

When I ran the Proc sql In the SAS program in th UNIX Environment I got the below error.

"ERROR: Specified resource already exists."

In the log I got this error before the quit statemnt.

Can any one please give me the solution.

Thanks
Sreenivas

[sbb]

Share the SAS code, revealed in your SAS log, and past the log as a post-reply to this forum for a most effective reply. Without seeing all of your SAS code (as executed), it would be a guessing-game.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Please find the below log for the error.Here we are also using SAS with SPDS .

292
293 data t.lab_&pid;
294 merge t.lab_d_&pid(in=a) lab_c(in=c);
295 by CL_HE_SEQ_ID;
296 if c;
297 run;

NOTE: There were 1719863 observations read from the data set T.LAB_D_1.
NOTE: There were 815693 observations read from the data set WORK.LAB_C.
NOTE: The data set T.LAB_1 has 815693 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 7.96 seconds
cpu time 6.34 seconds


298
299 data t.lab1_&pid;
300 set t.lab_&pid;
301 set lab_mbr_latest key= member_card_code/unique;
302 if _iorc_ ne 0 then state='';
303 run;

NOTE: There were 815693 observations read from the data set T.LAB_PRE_FINAL_1.
NOTE: The data set T.LAB1_1 has 815693 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 1:09.17
cpu time 11.85 seconds
9 The SAS System 10:02 Saturday, September 25, 2010



304
305 %let spdssize=128;
306
307 proc sql;
308
309 create table medmart.U_LAB_NW_&fromdt(type=DATA) as
310 select
311 SUBM_PATIENT_ID as PatientID
312 from t.lab1_&pid;;
ERROR: Specified resource already exists.

313 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 9.84 seconds
cpu time 5.68 seconds

[art297]

Sreenivas,

I've never worked with SPDS, thus I'm not sure if that is what is causing the problem. As someone mentioned, with SPDS, the problem could be due to existing files/permissions.

The following test of your code works for me .. but not using SPDS:

%let spdssize=128;
%let pid=1;
%let fromdt=29SEP2010;
libname medmart "k:\art";
libname t "k:\art";
data t.lab_d_&pid (drop=height age);
set sashelp.class (rename=(
name=CL_HE_SEQ_ID
weight=SUBM_PATIENT_ID));
run;

data lab_c (drop=weight age);
set sashelp.class (rename=(
name=CL_HE_SEQ_ID));
run;

data t.lab_&pid;
merge t.lab_d_&pid(in=a) lab_c(in=c);
by CL_HE_SEQ_ID;
if c;
run;

data lab_mbr_latest (drop=weight height);
set sashelp.class (rename=(
name=member_card_code));
run;

proc datasets library=work;
modify lab_mbr_latest;
index create member_card_code;
run;

data t.lab1_&pid;
set t.lab_&pid;
set lab_mbr_latest key= member_card_code/unique;
if _iorc_ ne 0 then state='';
run;

proc sql;
create table medmart.U_LAB_NW_&fromdt(type=DATA) as
select SUBM_PATIENT_ID as PatientID
from t.lab1_&pid;;
quit;

Some things you could try to clean up are:
(1) your second line from the bottom has 2 semicolons where only one is needed
(2) you could put periods after each time you use a macro variable
(3) is your spdssize macro variable set to a large enough number>

Other than that, I'd think that the problem is SPDS specific. Have you asked SAS Technical Support?

HTH,
Art

[Ksharp]

Do you use SAS/EG, if it is SAS/EG.
For example, if the dataset has opened in SAS/EG , Open this dataset again,
then you would get this message.
"ERROR: Specified resource already exists."
You can close this dataset.

[deleted_user]

It might be a long shot, but if the sas table currently exists (from a previous try) then it'll need deleting before running the code again.

Mike