Export each row in excel

[JT99]

Hi!
I have a table with 10 rows. I want to export each row in excel so I will have 10 excel files. Any help will be appreciated.

[maguiremq]

Could you explain how you want to separate each row into a separate spreadsheet? Like what determines which row goes to which sheet?

If you have some code, that would help too.

[Cynthia_sas]

Hi:

  If you want one workbook with 10 sheets, you'd have to use the sheet_interval suboption. But that would also require having some condition that you could use for BY group processing like a unique ID number or the data would need to have a row number that you could use.

  If you want to have 10 separate workbooks, one for each row, then you'd have to either use a Macro program with ODS EXCEL or you'd have to use one of the ODS destinations that supported the NEWFILE= option (like ODS CSV or ODS CSVALL).

  Here are some examples. Examples 1 and 2 make one workbook with 10 sheets, one for every row; Examples 3 and 4 make one workbook for every row.

data class10;
  infile datalines dlm=' ';
  input name $ age height weight ;
  rownum=_n_;
return;
datalines;
Alice 14 69.0 112.5 
Bob 13 56.5 84.0 
Carol 13 65.3 98.0 
Dave 14 62.8 102.5 
Eliza 14 63.5 102.5 
Frank 12 57.3 83.0 
Gina 12 59.8 84.5 
Hans 15 62.5 112.5 
Irene 13 62.5 84.0 
John 12 59.0 99.5 
;
run;


** Example 1 and 2 create 1 workbook with 10 sheets;
** Example 1: Have unique value, like name;
proc sort data=class10 out=class10;
by name;
run;

options nobyline;
ods excel file='c:\temp\Example1.xlsx'
    options(sheet_name='Name is #byval1' sheet_interval='bygroup');
proc print data=class10;
  by name;
  var name age height weight;
run;
ods excel close;

** Example 2: Have Row Number variable in data or created in DATA step;
proc sort data=class10 out=userownum;
by rownum;
run;

options nobyline;
ods excel file='c:\temp\Example2.xlsx'
    options(sheet_name='Row is #byval1' sheet_interval='bygroup');
proc print data=userownum noobs;
  by rownum;
  var rownum name age height weight;
run;
ods excel close;

** Example 3 and 4 make 10 separate files, one for each row;
  
** Example 3: Use NEWFILE= with ODS CSVALL Make 10 separate CSV files, one for each row;
ods csvall file='c:\temp\SepCSV1.csv' newfile=bygroup;
proc print data=userownum noobs;
  by rownum;
  var rownum name age height weight;
run;
ods csvall close;

** Example 4: Make 10 separate Excel workbooks, one for each row;
** Use Macro program because NEWFILE= is not an option for ODS EXCEL;

%macro makewb(want=);
options nobyline;
ods excel file="c:\temp\Name_&want..xlsx" 
    options(sheet_name="Name is &want");
proc print data=class10;
  where name = "&want";
  var name age height weight;
run;
ods excel close;
%mend makewb;
 
%makewb(want=Alice)
%makewb(want=Bob)  
%makewb(want=Carol)  
%makewb(want=Dave)  
%makewb(want=Eliza)   
%makewb(want=Frank)  
%makewb(want=Gina)  
%makewb(want=Hans)   
%makewb(want=Irene)  
%makewb(want=John)  

options byline;

I did not make a very fancy macro program. There are ways to automate generating the macro calls, but I did not want to overcomplicate the example. 

 Hope this helps,

Cynthia

[PaigeMiller]

UNTESTED CODE

proc import data="your_excel_file_name.xlsx" dbms=excel out=have;
run;
data _null_;
    set have;
    call execute(cat('ods excel file="excelfile',trim(left(_n)),'.xlsx;"',
         'proc print data=have(obs=',trim(left(_n_)),' firstobs=',trim(left(_n_)),'); run;',
         'ods excel close;'));
run;

I have to admit, this is a rather bizarre request and I'm curious. If you have an Excel file with 10 rows, what is the benefit of turning it into 10 Excel files with 1 row each? What can you do with 10 Excel files with one row that you can't do with 1 Excel file and 10 rows?

[JT99]

Thank you all for the quick replies.

So this is what my code looks like.

data null;
 set all;
 call symputx('nobs',_n_);
run;

data xmy_data2;set all;
 row_number = _N_;
 /*exportname=cats("&name",'_',substr(ReportedDate,3,2),'_',substr(ReportedDate,7,2)); */
run;

%put &nobs.;
%macro export_data;
    %do i=1 %to &nobs ;
        proc export data=my_data2(where=(row_number=&i))
            outfile="/location/sample_&i"
            dbms=xlsx  replace;putnames=NO;run;
    %end;
%mend;
%export_data;

Is there a way for me not to add an additional variable like row_number ? 

Because I don't want this to be included in my export file.

I also want the name of the excel outputs to be based on a value of a variable date. 

Like this:  /*exportname=cats("&name",'_',substr(ReportedDate,3,2),'_',substr(ReportedDate,7,2)); */

Is there a way for me to do this?

[Tom · Accepted solution]

As suggested before use the OBS= and FIRSTOBS= dataset options:

proc export data=all(obs=1 firstobs=1)
...
proc export data=all(obs=2 firstobs=2)
...