BookmarkSubscribeRSS Feed
vomer
Obsidian | Level 7

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?

15 REPLIES 15
art297
Opal | Level 21

Using ods, dde or proc export?

vomer
Obsidian | Level 7

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

Hima
Obsidian | Level 7

proc export data = dataset_name

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

dbms = excel replace;

sheet = 'Test';

run;

art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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
Calcite | Level 5

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
SAS Employee

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
PROC Star

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
Calcite | Level 5

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
SAS Super FREQ

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
Calcite | Level 5

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;

**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 Right' 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_hme_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 Back*****************************/

ods tagsets.Myexcelxp  options(sheet_name='RedZone Back' 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_back_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;

/*************Position 1*****************************/

ods tagsets.Myexcelxp  options(sheet_name='Position 1' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Pos_1_final missing;

**specifying columns;

column store oil_location 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;

/*************Position 2*****************************/

ods tagsets.Myexcelxp  options(sheet_name='Position 2' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Pos_2_final missing;

**specifying columns;

column store oil_location 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;

/*************Position 3*****************************/

ods tagsets.Myexcelxp  options(sheet_name='Position 3' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Pos_3_final missing;

**specifying columns;

column store oil_location 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;

/*************Position 4*****************************/

ods tagsets.Myexcelxp  options(sheet_name='Position 4' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Pos_4_final missing;

**specifying columns;

column store oil_location 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;

/*************Position 5*****************************/

ods tagsets.Myexcelxp  options(sheet_name='Position 5' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Pos_5_final missing;

**specifying columns;

column store oil_location 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;

/*************Position 6*****************************/

ods tagsets.Myexcelxp  options(sheet_name='Position 6' absolute_column_width='10,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=Pos_6_final missing;

**specifying columns;

column store oil_location 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;

/** closing excel file;*/

ods tagsets.Myexcelxp close;

ods listing;

Cynthia_sas
SAS Super FREQ

Hi: 
  
  Generally, I recommend that you do NOT add an essentially new question onto an old question. Sometimes I miss these. If you have a question related to a previous post, it's more straightforward to just reference the post (by giving a link to it) and then ask your question.
   
  But it is nearly impossible to answer your question -- first of all -- without your data, no one can run your code. Your program is very specific to your data, you have a long program and it's hard to read that much code and provide any help.
      
  Second, not only do you have a custom STYLE template (style=ReportTemplate) but you appear to have a custom tagset template (tagsets.Myexcelxp) -- without knowing or having those exact templates, nobody can run your code.
 
  Third, the final reason why no one can run your code is that your reference to _WEBOUT and the SAS Add-in imply that this is a stored process that is trying to return results to a client app. So, your  question is complicated by using _WEBOUT and a stored process and the SAS Add-in for Microsoft Office with your custom TAGSET.TEMPLATE.
      
  The Add-in for Excel can ONLY, ONLY, ONLY receive 3 kinds of results from stored processes: CSV, HTML or SASReport XML. SASReport XML is NOT the same as TAGSETS.EXCELXP -- so right off the bat, I'd guess that the reason that things aren't working are because:
1) _WEBOUT is an advanced way of sending output from a stored process and the Add-in doesn't "receive" what you're sending
2) Generally, when you do have a stored process that uses _WEBOUT, you are using the HTTP protocol to send streaming results to a web client (like the Info Delivery Portal, or the Stored Process Web App) And this means that you usually have to change the STPSRV_HEADER for your _WEBOUT Stream which is why it won't work with the Add-in -- usually, the SAS Add-in does not receive streaming results. Other folks who have posted many, many questions in the stored process forum have discovered that TAGSETS.EXCELXP must be used with either the Portal or the SPWA -- can't use TAGSETS.EXCELXP with the Add-in.
3) Your style template, if involved in the problem, must be in a location known to the stored process server (and so must your tagset template -- but I doubt your tagset template is ever going to be used by the Add-in)
  
  So, my recommendation is that you should work with Tech Support. They have to look at all your code, including the template code, get a sample of your data and try to run as a stored process. However, as I said above -- you can ONLY send HTML, CSV or SASReport XML results from a stored process to the Add-in -- so basically, multi-sheet reports using a tagset template are only achievable using the IDP or the SPWA -- if you search in the stored process forum, you'll find lots of examples over there.
  
cynthia


jdmarino
Fluorite | Level 6

When I try this, I get my file with only the last-written sheet.  (In this case Test2.)  To be fair, I'm using dbms=xlsx because I have 64-bit SAS and 32-bit Excel 2010.

naniadabala
Calcite | Level 5

Working :

 

 

proc sql;
select count(*)into:cnt from dataset_name;
quit;
%put &cnt;

 

%macro sep();


%do j=1 %to &cnt %by 800000;
%let last=%eval(&j+799999);
%put &j &last ;


proc export data=dataset_name(firstobs=&j obs=&last)
outfile="D:\required_path\workbook_name.xlsx" dbms=xlsx replace;
sheet="A&j";

run;


%end;
%mend;
%sep;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 28154 views
  • 0 likes
  • 13 in conversation