topic Re: Error in when running the Proc sql. in SAS Procedures

Error in when running the Proc sql.

deleted_user — Sun, 26 Sep 2010 09:13:06 GMT

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

Re: Error in when running the Proc sql.

sbb — Sun, 26 Sep 2010 13:49:18 GMT

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.

Re: Error in when running the Proc sql.

Ksharp — Mon, 27 Sep 2010 03:07:33 GMT

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.

Re: Error in when running the Proc sql.

deleted_user — Mon, 27 Sep 2010 07:42:28 GMT

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

Re: Error in when running the Proc sql.

deleted_user — Wed, 29 Sep 2010 21:24:47 GMT

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

Re: Error in when running the Proc sql.

art297 — Thu, 30 Sep 2010 00:16:01 GMT

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