Split SAS Output into multiple Sheets

vomer · Posted 03-01-2012 11:03 AM

Hi Guys,

I have a dataset with ~70,000 + rows. My version of excel cannot handle this amount but I still need to have the dataset exported to excel.

Is there any way / code that I can use to get sas to split the data output across multple sheets in exel after it hits row limit?

art297 · Posted 03-01-2012 11:23 AM

Using ods, dde or proc export?

vomer · Posted 03-01-2012 11:31 AM

I would liek to know about both DDE and proc export if possible

Hima · Posted 03-01-2012 11:41 AM

proc export data = dataset_name

outfile = 'C:\ Temp\test.xls"

dbms = excel replace;

sheet = 'Test';

run;

art297 · Posted 03-01-2012 11:41 AM

A couple of possibilities are offered in the thread at: http://www.tek-tips.com/viewthread.cfm?qid=1335588

Haikuo · Posted 03-01-2012 01:46 PM

I am aware that you are more keen on something else, however, ODS tagsets.excelxp is very straighforward to use. I have had trouble using Proc export doing multi-sheets, also, DDE will ask more labor on coding. Here is the example of ODS, which you may be able to use it directly, change destination folder to match what you have on your computer:

/*Dummy table with 200K records*/

data h;

do i=1 to 200000;

output;

end;

run;

/*Determine how many sheets you will need, 65k records per sheet*/

data _null_;

call symput ('pages',ceil(nobs/65000));

if 0 then set h nobs=nobs;

run;

%macro mult_sht;;

ods listing close;

ods tagsets.excelxp

file="h:\want.xml" ;

%do i=1 %to &pages;

ods tagsets.excelxp

options ( sheet_name="output_page&i" );

proc print data=h (firstobs=%eval((&i.-1)*65000+1) obs=%eval(&i.*65000));

run;

%end;

ods tagsets.excelxp close;

%mend;

%mult_sht

Regards,

Haikuo

dav_amol · Posted 03-01-2012 12:09 PM

Some time back I have created this macro to address similar kind of need in our project . This macro takes below given parameters

1. Libname is source library

2. SAS_Data : Source SAS Table

3. Output_Name : Excelsheet Name

4. Sheet_Category : If you want to create sheets based on some criterian , use Classification variable as Sheet_Category

options mprint mlogic symbolgen;

%macro Send_to_Excel( libname =,

SAS_data=,

output_name = ,

Sheet_category =

);

proc sql noprint;

select type

into :cat_type

from DICTIONARY.COLUMNS

where upcase(name) = upcase("&Sheet_category.")

and upcase(libname) = upcase("&libname.")

and upcase(memname) = upcase("&SAS_data.")

;

quit;

proc sql noprint;

/* determine total no of sheets required to be created */

select strip(put(count(distinct(&Sheet_category)),best2.))

into :tot_cat_cnt

from &libname..&sas_data;

select distinct &Sheet_category

into :idcat1 - :idcat&tot_cat_cnt

from &libname..&sas_data;

%let catcnt = &sqlobs;

quit;

/* Create Excel file in DMM_TEMP Folder */

%do i = 1 %to &tot_cat_cnt;

proc export data=&libname..&sas_data (where=(&Sheet_category =

%if &cat_type = char %then

"&&idcat&i";

%else

&&idcat&i;

))

OUTFILE="&output_name..xls"

DBMS= xls

REPLACE;

sheet = "A_&&idcat&i";

RUN;

%end;

%mend Send_to_Excel;

%Send_to_Excel(libname = sashelp,

SAS_data=class,

output_name = Amol,

Sheet_category = sex

);

FriedEgg · Posted 03-01-2012 01:13 PM

If you license the SAS product: SAS/ACCESS Interface for PC Files the you could also use the EXCEL Libname engine.

*default ver=97 which is limited to ~65k rows. Use 2002 instead to increase sheet limit to ~1million;

libname xcell excel '/nas/sasbox/users/mkastin/excel_workbook.xls' ver=2002;

*create two worksheets in above workbook by sex from sashelp.call;

data xcell.males xcell.females;

set sashelp.class;

select(sex);

when ('M') output xcell.males;

when ('F') output xcell.females;

end;

run;

Another thing you can do in excel itself to import large files is to use the follow macro (for excel)

Sub LargeFileImport()

Dim ResultStr As String

Dim FileName As String

Dim FileNum As Integer

Dim Counter As Double

FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

If FileName = "" Then End

FileNum = FreeFile()

Open FileName For Input As #FileNum

Application.ScreenUpdating = False

Workbooks.Add template:=xlWorksheet

Counter = 1

Do While Seek(FileNum) <= LOF(FileNum)

Application.StatusBar = "Importing Row " & _

Counter & " of text file " & FileName

Line Input #FileNum, ResultStr

If Left(ResultStr, 1) = "=" Then

ActiveCell.Value = "'" & ResultStr

Else

ActiveCell.Value = ResultStr

End If

If ActiveCell.Row = 65536 Then

ActiveWorkbook.Sheets.Add

Else

ActiveCell.Offset(1, 0).Select

End If

Counter = Counter + 1

Loop

Close

Application.StatusBar = False

End Sub

This excel macro takes a text file (say a csv from SAS, for example) and reads it in chunks into multiple sheets of the compatible length for your version.

SASKiwi · Posted 03-01-2012 02:09 PM

A simple solution using EXPORT:

proc export data = dataset_name (obs = 64000)

outfile = 'C:\ Temp\test.xls"

dbms = excel replace;

sheet = 'Test';

run;

proc export data = dataset_name (firstobs = 64001)

outfile = 'C:\ Temp\test.xls"

dbms = excel replace;

sheet = 'Test2';

run;

felicity1212 · Posted 06-20-2012 11:00 AM

Is there anyway that I can tweek it to export not by observation but by a column name in my dataset. The column name I am trying to export by is called "provname" and there are 74 different providernames I want to export every group of observations related to one provnam onto one spreasdheet.

Cynthia_sas · Posted 06-20-2012 03:31 PM

Hi:

Try this simplified example with just BY AGE (using SASHELP.CLASS). It makes one separate worksheet for each value of the AGE variable, automatically. I limited it to only 3 values for AGE, since showing 3 sheets makes the point just as well as having 6 sheets.

cynthia

proc sort data=sashelp.class out=class;

by age;

where age in (12,13,14);

run;

ods listing close;

ods tagsets.excelxp file='c:\temp\class_by.xml' style=sasweb

options(sheet_interval='bygroup' doc='Help');

proc print data=class noobs;

by age;

var name sex age height weight;

run;

ods tagsets.excelxp close;

QZone · Posted 09-12-2012 01:33 PM

Cynthia

How can we do a similar thing inside SAS AddIn for excel ?

I need multiple sheets not necessarily using by groups.

Here is the code I was using .

The file = _webout doesnt seem to work.

I get an error message saying "....The SAS report results could not be processed.The 'CSV' satrt tag on line 24 doesnot match endtag of 'Data'. Line 25, position 3."

ods tagsets.Myexcelxp file = _webout style= ReportTemplate

options(sheet_name='Summary Store Ct' absolute_column_width='15,15,15,15,15,15,15,15,15,15'

center_Horizontal='yes' center_vertical='yes' orientation='landscape' frozen_headers='1' fittopage='yes' pages_fitheight='40' pages_fitwidth='1' );

proc report data= Summary_final;

**specifying columns;

column family POSITION_redzone_conv POSITION_redzone_hme POSITION_redzone_fs POSITION_redzone_back POSITION_1 POSITION_2 POSITION_3 POSITION_4 POSITION_5 POSITION_6;

define family / display 'Family' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

							style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};
	define POSITION_redzone_conv / display 'RedZone Front' style(column)={just=center borderbottomwidth=1 bordertopwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}
							style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_redzone_hme / display 'RedZone Right' style(column)={just=center borderbottomwidth=1 bordertopwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_redzone_fs / display 'RedZone Left' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_redzone_back / display 'RedZone Back' style(column)={just=center borderbottomwidth=1 bordertopwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_1 / nozero 'Window Position 1' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_2 / nozero 'Window Position 2' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_3 / nozero 'Window Position 3' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_4 / nozero 'Window Position 4' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_5 / nozero 'Window Position 5' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define POSITION_6 / nozero 'Window Position 6' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

run;

/*************Missing Positions*****************************/

ods tagsets.Myexcelxp options(sheet_name='Missing Positions' absolute_column_width='10,20'

center_Horizontal='yes' center_vertical='yes' orientation='landscape' frozen_headers='1' fittopage='yes' pages_fitheight='40' pages_fitwidth='1' );

proc report data=missing_position;

**specifying columns;

column store OIL_LOCATI;

define store / display 'Store' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define OIL_LOCATI / display 'Position' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

run;

/*************RedZone Front*****************************/

ods tagsets.Myexcelxp options(sheet_name='RedZone Front' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Redzone_front_final missing;

**specifying columns;

column store position family rank_1 rank_2 rank_3 rank_4 rank_5 rank_6 rank_7 rank_8 rank_9 rank_10 rank_11 rank_12 rank_13 rank_14 rank_15;

define store / display 'Store' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define position / display 'Position' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define family / display 'Family' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_1 / 'Rank 1' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_2 / 'Rank 2' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_3 / 'Rank 3' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_4 / 'Rank 4' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_5 / 'Rank 5' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_6 / 'Rank 6' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_7 / 'Rank 7' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_8 / 'Rank 8' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_9 / 'Rank 9' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_10 / 'Rank 10' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_11 / 'Rank 11' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_12 / 'Rank 12' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_13 / 'Rank 13' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_14 / 'Rank 14' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

define rank_15 / 'Rank 15' style(column)={just=center bordertopwidth=1 borderbottomwidth=1 borderleftwidth=2 borderrightwidth=2 font=("Arial",8pt)}

style( header) = {borderleftwidth=2 borderrightwidth=2 borderbottomwidth=2 font=("Arial",8pt,Bold)};

run;

/*************RedZone Left*****************************/

ods tagsets.Myexcelxp options(sheet_name='RedZone Left' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Redzone_fs_final missing;