SAS to Excel: Mislabeling sheets and other display issues

[mae_day]

Hi all,

Thanks to all of your help with my previous issue I have now been assigned a similar QA automation task, but this time for our company’s regional offices. So each region will receive an Excel workbook with 6 sheets (one for each type of issue that must be resolved). Each sheet is essentially the same and includes the factory, account type, date of transaction, and account number. The data is set up like this:

Region	Factory	Date	Account_type	Account_No	Error_Type
A	Bluth	12/11/20	1	10293645102945	1
A	Sitwell	6/26/20	2	27381946520473	1
C	Austero	1/3/21	1	35258352849104	6

Below is a portion my code:

/*Create output based on region*/
%macro output(region);

ods excel file= "pathway\ &Region report.xlsx" style=excel;
ods excel options(embedded_titles='yes' frozen_headers='4' flow='tables' sheet_name='Error 1’);

data _null_;
 set error1 end=done;
/* where problem=1;*/
 where region = "&region";
 if _n_ eq 1 then do;
 declare odsout t();
  	t.title(text: 'Amount owed exceeds account limits’, style_attr: 'fontsize=12pt fontweight=bold just=center vjust=center'); 
	t.title(data: region, style_attr: 'fontsize=10pt fontweight=bold just=center vjust=center', start: 2);
	t.table_start();
		t.row_start(type: 'H');
				t.format_cell(text: 'Factory', overrides: 'fontweight=bold just=center vjust=center');
				t.format_cell(text: 'Account Type', overrides: 'fontweight=bold just=center vjust=center');
				t.format_cell(text: 'Date', overrides: 'fontweight=bold just=center vjust=center');
				t.format_cell(text: 'Account No.', overrides: 'fontweight=bold just=center vjust=center');
			t.row_end();
		end;
			t.row_start();
				t.format_cell(data: factory, format: '$factory.');
				t.format_cell(data: account_type, format: 'acctyp.');
				t.format_cell(data: %ExcelDateAdj(date), style_attr: 'tagattr="format:mm/dd/yyyy"');
				t.format_cell(data: account_no);
			t.row_end();
		if done then t.table_end();

ods excel options(embedded_titles='yes' frozen_headers='4' flow='none' sheet_name='Error 2’);
data _null_;
 set error2 end=done;
/* where problem=2;*/
 where region = "&region";
 if _n_ eq 1 then do;
 declare odsout t();
  	t.title(text: 'Inventory refund never issued’, style_attr: 'fontsize=12pt fontweight=bold just=center vjust=center'); 

………
	if done then t.table_end();
run;
ods _all_ close;

%mend output;

/*Now output all region Excel workbooks*/
data _null_;
  set error1-error6;
  by region;
  if first.region;
  call execute(cats('%nrstr(%output)(',region,')'));
run;

I am running into two issues:

1) Some regions do not have all 6 types of issues, so there are varying number of sheets. This is causing the sheet names to be inaccurate for some reason (e.g. type 4 errors are output to the sheet named “Error 5”). This is also causing discrepancies with the titles. Ideally, I would like to display a message like “There are no records to display” to ensure the sheet ordering does not change. I tried using proc sql to select nobs and integrate it into the report using if-then statements, but this output Excel workbooks with no data.

2) The account number is formatted by Excel as scientific notation. I have tried to address this by including 

style_attr: 'tagattr="format: text ”

but this results in the account numbers appearing as hashes (regardless of the column width). I also tried to create a new character variable doing this:

an1 = strip(put(input(account_no,best.),best.));
an=cat(" ", an1);

But the account number is truncated to 12 digits instead of 14 digits, even after ensuring the variable length was great enough. Am I resigned to fixing the formatting in Excel after outputting the reports?

Thank you!

[Reeza · Accepted solution]

Your first issue is easily solvable. Create a dataset that has no information or has a single variable that is "This data set is empty".

Use the logic illustrated here to check the data set and then print the data set only if it exists and is not empty. Instead of the data _null_ print your empty data set though. That should also fix your issue with the sheet naming. You could also conditionally create a sheet entirely if it exists only and otherwise skip that sheet entirely.
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/mcrolref/p011imau3tm4jen1us2a45cyenz9.htm

There's a small bug in your code, you have a curly quote at the end of your ODS EXCEL file statement (around Error 1)....not sure if that's in your actual code but it caught my eye.

For the Excel format, once you have the value in Excel, see what the format is applied to that cell showing it the way you want and then extract that to use in SAS. Function in Excel to use is CELL, which returned G (# ?/? or # ??/??) for me, see table at the bottom of this link https://support.microsoft.com/en-us/office/cell-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

[ballardw]

Why?

an1 = strip(put(input(account_no,best.),best.));
an=cat(" ", an1);

This implies that you have a character value that you turn into numeric, write the numeric (with the wrong format because it is trucating) to  new character value. 

Why that convoluted approach???

Provide an example or three of account_no and what the desired AN variable should look like.