BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sam369
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

4 REPLIES 4
Haikuo
Onyx | Level 15

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
Super User

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
Lapis Lazuli | Level 10

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 875 views
  • 8 likes
  • 4 in conversation