topic solution for loop in SAS Procedures

solution for loop

sam369 — Thu, 12 Jul 2012 15:21:37 GMT

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

Re: solution for loop

Haikuo — Thu, 12 Jul 2012 16:09:44 GMT

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

Re: solution for loop

Reeza — Thu, 12 Jul 2012 16:10:04 GMT

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;

Re: solution for loop

Linlin — Thu, 12 Jul 2012 16:10:19 GMT

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

Re: solution for loop

sam369 — Thu, 12 Jul 2012 17:55:10 GMT

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