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 = "®ion";
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 = "®ion";
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!
... View more