Hi All, Thanks to everyone. Just changed filename statement to dquotes, and it worked fine.

I like to do some refinements to this program.

Firstly, is it possible to generate dynamic INPUT statement. Since, the members within PDS can have a different record structure, i want to prepare a dynamic input depending upon 4-7th character of the member name.

So, when i coded this, i'm getting the error 'A character operand was found in the %EVAL function or %IF condition where a numeric operand is required'.

%macro exm; %do i=1 %to &count; filename in1 "AV76762.IEBV2.OUTPUT(&&mem&i)"; DATA EXTERNAL; INFILE IN1; %if substr(&&mem,4,4) = 1385 %then INPUT sno 1-2 memno $4-12 status $15-21 ; %else INPUT sno 1-2 memno $4-12 status $15-21 gsam $24 reason $27-37; %end; %mend;

Secondly, instead of having sno field in the input field, i can use OBS to generate the seqeuence number. But while i do like that, my field name will be 'OBS'. Is it possible to change the field name to our own name like in this case as 'sno'.

Can you please help me to overcome the above problems. Thanks in anticipation.

Regards, Narra.

Quoting "Fehd, Ronald J." <RJF2@CDC.GOV>:

> this > filename in1 'AV76762.SAS.DATA(&&mem&i)'; > need dQuotes: > filename in1 "AV76762.SAS.DATA(&&mem&i)"; > > same here: > > filename out '&temp'; > > filename out "&temp"; > > > small detail, but reads better > this: > > proc sql; > > select count(member) into: count from test; > > select member into :mem1 - :mem%left(&count) from test; > > run; > > quit; > > can be reduced to: > proc sql;select member > into :mem1 - :mem9999 > from test > ;quit; > > SQL will create only the number of members in the macro array > > Ron Fehd the macro maven CDC Atlanta GA USA > > > > From: KHANNA NARRAVULA > > Sent: Thursday, October 21, 2004 12:56 PM > > To: SAS-L@LISTSERV.UGA.EDU > > Subject: Generate Dynamic Excel Worksheets from a PDS. > > > > > > Hi All, > > > > 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 attribute. > > > > 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*/ > run; > > > > %macro exm; > > %do i=1 %to &count; > > filename in1 'AV76762.SAS.DATA(&&mem&i)'; > > > > DATA EXTERNAL; > > INFILE IN1; > > INPUT MEMNO $ 1-8 TYPE $ 11 STATUS $13-15 HISTORY $17-30; > > %PUT _USER_; > > Ods Markup anchor="sql"; > > proc sql; > > select * from EXTERNAL; > > run; > > %end; > > %mend; > > > > options nobyline; > > 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; > > data test; > > infile out; > > file print; > > input member $8. @; > > > > proc sql; > > select count(member) into: count from test; > > select member into :mem1 - :mem%left(&count) from test; > > run; > > quit; > > > > %exm; > > 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 above problem. > > > > Thanks in anticipation. > > > > > > Regards, > > Narra. > > >