Create different excel sheets by variable

[Zax7]

Hi All,

I have to make an excel file with several sheets from a SAS dataset. The dataset contains some variable including USERNAME. I'd like to distribute observations by variable USERNAME. The output xls must contain the mentioned observations: one username for one sheet. The number of the different usernames not constant. The name of the excel sheets must be the username.

I tried ODS Excel, data step and using macro, but I have no success.

My solution: a macro is called in data step iterations which reads each username.

ods excel file="filepath\filename";

%macro create_sheets(username=);
ods excel options(sheet_name="&username");

proc print data HAVE noobs;
where USRNAME="&username";
run;
%mend;

data _NULL_;
set HAVE;

call symput('transfer', strip(USRNAME));
%create_sheets(username=&transfer);
output;
run;

ods excel close;

This code runs successful but the output excel file contains one sheet with the observations of last username.

I don't know what is the mistake. Please help me.

Thank you

[RW9 · Accepted solution]

There are many examples on here of this.  For instance one simple way:

proc sort data=have out=loop nodupkey;
  by usrname;
run;

ods excel file=".../want.xlsx";

data _null_;
  set loop;
  call execute('ods excel options(sheet_name="',strip(usrname),'");');
  call execute('proc report data=have; where usrname="',strip(usrname),'"; run;');
run;

ods excel close;

This will, from the data null, create one proc report/sheet name for each unique usrname (as found from the original proc sort nodupkey).

Do avoid using uppercase in your code, we don't need shouting at!

[Zax7]

Thank you for solution.

But there are some errors: a critical memory shortage occured while extending a crossing table.

The size of the original sas7bdat file is ca. 30MB and contains ca. 130k observations and 35 different username.

The excel workbook conatins only 17 sheets however these are perfect.

[RW9]

Sounds like your running out of memory doing the operation.  Not really surprising, Excel output is all done in memory.  Extend the amount of memory available to SAS.  Or, and far better, so not use Excel.  130k observations in a workbook, nobody is ever going to want to look at that, and Excel as a data transfer medium is terrrible.  Use CSV, XML or one of the open, cross system compatible, plain text file formats.  In fact, unless the recipient actually demands Excel I would avoid it totally.

[Zax7]

Yes, I'm running out of memory.

I have added the -memsize max option to sasv9.cfg.

After this the process ran successfully.

Thank you again.

[NataljaK]

Hi,

The option sheet_interval="table" should be added to get the tables on the different sheets.
Unfortunately I don’t no the solution for the other problem – name of sheets

Kind regards

Natalja