Yamini,

Would something like the following help?

data one; input a $ b; cards; a 1 a 2 a 3 b 4 b 5 b 6 ; run; %global valuelist; proc sql; select distinct(a) into: valuelist separated by " " from one; quit; %put valuelist= &valuelist; %macro splitfile; %let i=1; %do %while (%scan(&valuelist.,&i.,' ')^=); call symput("currentvalue",%scan(&valuelist.,&i.,' '); data part; set one; %put &currentvalue.; if a eq "&currentvalue."; c=''; /* create new blank variable c */ d=''; /* create new blank variable d */ run; PROC EXPORT DATA= WORK.part OUTFILE= "c:\test&currentvalue..xls" DBMS=EXCEL REPLACE; RUN; %let i=%eval(&i+1); %end; %mend splitfile; %splitfile

Art -------- On Fri, 13 May 2005 13:33:22 -0700, ysk <ysk.kan@GMAIL.COM> wrote:

>Thanks for the macro. >I am still trying to expand on that. >But i am not sure if it will help me generate seperate xls file for >each group value...bcaz my group value are strings. >I realized that i cannot use a list of string values in %do like do >i='a' 'b' 'c'...