Help using Base SAS procedures

solution for loop

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

solution for loop

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


Accepted Solutions
Solution
‎07-12-2012 12:10 PM
Super Contributor
Posts: 1,636

Re: solution for loop

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: solution for loop

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

Super User
Posts: 19,770

Re: solution for loop

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;

Solution
‎07-12-2012 12:10 PM
Super Contributor
Posts: 1,636

Re: solution for loop

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

Regular Contributor
Posts: 168

Re: solution for loop

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 266 views
  • 7 likes
  • 4 in conversation