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