Split a table into multiple Files based on the distinct values ​​of a column

[daniele_1306]

I have a table like this:

Name	Eyes_Colour
Daniele	Brown
Luca	Blue
Francesca	Brown
Antonio	Blue
Stefano	Brown
Viviana	Green

i want to split this table in 3 table based on Eyes_Colour

Table Brown		Table Blue		Table Green
Name	Eyes_Colour		Name	Eyes_Colour		Name	Eyes_Colour
Daniele	Brown		Luca	Blue		Viviana	Green
Francesca	Brown		Antonio	Blue
Stefano	Brown

and i want to export this tables in 3 files xls.

can you help me? 

[PeterClemmensen]

Do you know the distinct values in advance?

[Ksharp]

If you don't have a big table .

data have;
 set sashelp.heart;
run;


proc freq data=have noprint;
table sex/out=temp;
run;
data _null_;
 set temp;
 call execute(cat('data ',sex,';set have;if sex="',sex,'";run;'));
run;

[PeterClemmensen]

If you only want the Excel workbooks and do not need the individual SAS data sets, you could do something like this

data have;
input Name $9. Eyes_Colour $;
datalines;
Daniele   Brown
Luca      Blue 
Francesca Brown
Antonio   Blue 
Stefano   Brown
Viviana   Green
;

proc sql noprint;
   select distinct Eyes_Colour into :e1 -
   from have;
quit;

%macro odse;

   proc sql noprint;
      select distinct Eyes_Colour into :e1 -
      from have;
   quit;

   %do i = 1 %to &sqlobs.;
      ods excel file = "YourPathHere\&&e&i...xlsx" ;
      
      proc print data = have(where=(Eyes_Colour = "&&e&i"));
      run;

      ods excel close;
   %end;
%mend;

%odse;

[daniele_1306]

Excellent, thank you very much! however, in doing so it is first necessary to download the xls (and therefore to open them) and then to save them. it is not possible to save them automatically in the path I indicated

[daniele_1306]

can you help me again? I want to automatically export the files without having to download them first.

thank you very much...