I need to generate excel file with multiple worksheets such
that each worksheet corresponds to one member of a particular PDS.
Assume PDS, has 5 members in it, then it should genearate 5
worksheets within one excel file. This number, 5, is a varying
I'm using ODS XML spreadsheet to solve this, but it is not
substituting the actual value for the &&mem&i, therefore it throws
the error that 'Physical File doesn't exist'.
here is the program:
Any proc template for defining the tagset; /*tagset excel82*/
%do i=1 %to &count;
filename in1 'AV76762.SAS.DATA(&&mem&i)';
INPUT MEMNO $ 1-8 TYPE $ 11 STATUS $13-15 HISTORY $17-30;
Ods Markup anchor="sql";
select * from EXTERNAL;
ods markup file="/u/userid/attach.xls" tagset=excel82;
filename indd 'userid.sas.data' disp=shr;
filename out '&temp';
proc source indd=indd nodata noprint dirdd=out;
input member $8. @;
select count(member) into: count from test;
select member into :mem1 - :mem%left(&count) from test;
ods markup close ;
If i directly substitute the member name directly, it generates
the worksheet properly for that member. But i need to repeat the
process for all the members within a PDS.
I'm using SAS 8.2 and Excel XP. Can you please help me to solve the
Thanks in anticipation.