topic Re: Split a table into multiple Files based on the distinct values of a column in SAS Programming

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

daniele_1306 — Fri, 05 Feb 2021 10:18:29 GMT

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?

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

PeterClemmensen — Fri, 05 Feb 2021 11:10:34 GMT

Do you know the distinct values in advance?

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

Ksharp — Fri, 05 Feb 2021 11:16:06 GMT

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;

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

PeterClemmensen — Fri, 05 Feb 2021 11:30:50 GMT

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;

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

daniele_1306 — Fri, 05 Feb 2021 13:11:32 GMT

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

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

daniele_1306 — Fri, 05 Feb 2021 14:10:54 GMT

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

thank you very much...

topic Re: Split a table into multiple Files based on the distinct values ​​of a column in SAS Programming

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

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

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

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

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

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

topic Re: Split a table into multiple Files based on the distinct values of a column in SAS Programming

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

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

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

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

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

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