export tables according to a column

[Geo-]

Hi, I have a table like:

a	b	c
a101	b1	c1
a102	b2	c2
a103	b3	c3

and will create 3 excel files like:

table1_b2
a	b	c
a102	b2	c2
table_b1
a	b	c
a101	b1	c1
table_b3
a	b	c
a103	b3	c3

how to use macro to create and export tables according to  distinct value of column b and create table_&bi,thanks!

[ChrisNZ]

You don't need macros.

Something like this works:

data _null_;
  set TABLE(keep=B);
  by B;
  if first.B then call execute ('ods excel .....sheet_name= ....    '
                             || 'proc print data=TABLE noobs; where B='||quote(B)||';run;'
                             || 'ods ..... ');
run;

[Geo-]

macro is needed..if column B have hundreds of values,this way could not work

[ChrisNZ]

And you know this because? Hundreds should work.

[Geo-]

data _null_;
  set TABLE(keep=company);
  by company;
  if first.company then call execute ('ods excel file="c:\temp.xlsx"  sheet_name="sales report"    '
                             || 'proc print data=TABLE noobs; where company='||quote(company)||';run;'
                             || 'ods excel close ');
run;

Hi expert ,would you please  check where is wrong with the code

[ChrisNZ]

1. Missing semicolons

2. You overwrite the same file over and over