BookmarkSubscribeRSS Feed
DeepSiv
Fluorite | Level 6

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.

2 REPLIES 2
Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
DeepSiv
Fluorite | Level 6

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.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 2 replies
  • 798 views
  • 0 likes
  • 2 in conversation