solution for loop

[sam369]

Hi All,

I have a dataset with different group.  Each individual group need to be export into  excel in multiple tabs(multilple sheet).I am doing this in mcaro beacues i have more that 200 datasets with different groups

data have;

input  group1 $ count1;

cards;

aaa             100

aaa              120

aaa            130

bbb              99

ccc              150

bbb               349

bbb          500

ccc             130

ccc               160

ddd             150

cc aa           130

ddd              190

ddd             180

cc aa           450

cc aa          690

cc aa            145

;

run;

proc sql noprint;

select distinct group1 into: grps separated by  ' ' from have ;quit;

%put &grps

%let sqlobs=&sqlobs;

%macro test;

data test;

set have;

do i=1 to &sqlobs;

   if group1 eq scan(&grps,i);

end;

run;

proc export data=test outfile="testfile.xls"

                                     dbms=excel replace;

                                 sheet="scan(&grps,&sqlobs);

run;

%mend;

%test;

I dont know how to deal with it

Any thing will be highly appreciated

Thanks

Sam

[Haikuo]

I would suggest using Macro only when you absolutely have to:

data have;

input  group1 $& count1;

cards;

aaa             100

aaa              120

aaa            130

bbb              99

ccc              150

bbb               349

bbb          500

ccc             130

ccc               160

ddd             150

ccaa           130

ddd              190

ddd             180

ccaa           450

ccaa          690

ccaa            145

;

proc sort data=have;

by group1;

run;

ods _all_ close;

ods tagsets.ExcelXP path='c:\temp' file='want.xml' style=statistical;

ODS tagsets.ExcelXP options (suppress_bylines='YES'

sheet_label=' '

sheet_interval='BYGROUP'

autofit_height='YES');

Proc PRINT data=have;

by group1;

run;

ods tagsets.ExcelXP close;

ods listing;

Regards,

Haikuo

[Reeza]

If speed isn't an issue you can use tagsets.excelxp

proc sort data=sashelp.class out=class; by age; run;

ods listing close;

ods tagsets.excelxp file="C:\temp\sample_output.xls" options(sheet_interval='bygroup') style=journal;

proc print data=class noobs;

by age;

run;

ods tagsets.excelxp close;

ods listing;

[Linlin · Accepted solution]

or

data have;
input  group1 $ count1;
cards;
aaa             100
aaa              120
aaa            130
bbb              99
ccc              150
bbb               349
bbb          500
ccc             130
ccc               160
ddd             150
;

run;

proc sql noprint;
select distinct group1 into: grps separated by  ' ' from have ;quit;
%put &grps;
%let sqlobs=&sqlobs;
%macro test;
%do i=1 %to &sqlobs;
%let dsn=%scan(&grps,&i);
  data &dsn;
  set have(where=(group1="&dsn"));
run;
proc export data=&dsn outfile="testfile.xls"
                                 dbms=excel ;
                                 sheet="&dsn";
run;
%end;
%mend;
%test

Linlin

[sam369]

Hi All,

Thank you So much,.... Good thing to know about tagset concept....

I learn one new thing today!!!!!!

Thanks LIN,Hai and reeza

Sam