Excel creation issues in SAS

[DeepSiv]

Hi all

I am having some formatting issues with the excel created by SAS. I need to split a single column name (separated by a space) in to two separate parts and display it on the excel as subsequent records in excel. Also I need to print the color for the excel field name.

I have attached both the excel files for your referece. The expected_output.xls contains the actual way I want the excel. The current_output.xls contains the output created by SAS.

These are the codes that I tried:

PROC EXPORT DATA = XLSOPT OUTFILE="TEST.XLS";

RUN;

I tried it using proc report with ods tagsets.excelxp. I was able to get the header color, but I could not split the header. Also the major issue I had with proc report was that the the entire borders of excel got disappeared and the borders were present only in the data region. I have attached excel file (proc_report_test.xls) for reference.

This is the proc report code used:

ods tagsets.excelxp file="proc_report_test.xls";

proc report data = xlsopt style(header)={foreground=blue};

column emp_name emp_id prv_cd pre_cd;

define emp_name/center;

define emp_id/center;

define prv_cd/center;

define pre_cd/center;

run;

ods tagsets.excelxp close;

Any help in solving any of the issue - be it adding color without affecting the entire excel as well as splitting the column name would be greatly appreciated.

[Jagadishkatam]

Hi Deepsiv,

Please try the below code,

/*code to import the data from excel to the dataset*/

filename have "path\current_output.xls";

proc import datafile=have out=hav dbms=excel replace;

run;

/*code to output the dataset to excel and get the headers to split*/

ods tagsets.excelxp file="path\_output.xls";

proc report data = hav headline headskip split='_' nowindows missing style(header)={foreground=blue};

column id_num emp_num emp_first emp_last;

define id_num/left width=40 format=best.;

define emp_num/left width=40 format=best.;

define emp_first/center width=40;

define emp_last/center width=40;

run;

ods tagsets.excelxp close;

Thanks,

Jagadish

[DeepSiv]

Hi Jagadish

It helps to an extent. The header split needs to happen as two different columns. But still this solution helps to an extent.

But one of the major problem is that the excel created by proc report still has all the borders suppressed everywhere except the area where data is present. If that can be solved, it would be great. Please refer my attachment proc_report_output.xls

Thanks.