After ten minutes of Mike staring at the screen unmoving, Jill sighed. As the newly minted tech lead, she knew that she had to help unwedge problems. But she also knew that Mike had been tasked with a task which should have been simple, had the project been developed in their team. Alas, it had been thrown over the wall for them to maintain, as a “reward” for cleaning up so much else.

The calculations for generating quotes for customers were, for some complex scenarios, quoting too low; they were missing some items. The originators of the quote system had used Excel, so there was probably a hard-coded range to be updated. She’d asked Mike to fix it, create a playbook for this system and create a ticket sketching out what might be needed to make the system more resilient and maintainable. Excel would still be required, for the actuaries wouldn’t hand over formulae in anything else, but perhaps some jiggling into independent sheets and just running summaries over “the whole sheet” would reduce the pain. Jill had thoroughly cautioned Mike that he did not want, at this point, to discover the paperwork and bureaucracy to change how the calculations were performed, so the only changes to be made in this first approach were to be the minimum necessary, while keeping the style and solution intact.

What Jill did not expect as the first question from Mike, when she went over, was an inquiry into just what the bureaucracy would be like, to bite the bullet immediately. Humoring him with an explanation, she was somewhat more taken aback when, after only brief cussing, he started pulling up the forms to do so.

Once she saw the code being maintained, and Mike’s proposed replacement, she signed off immediately on the paperwork parts she was authorized to approve, and went off with a printed copy to get the co-signatures needed. By the time they were done, the time taken for this override process set a new institutional record for “fastest approval”. Sometimes, lumbering bureaucracies could move quickly; dodging a bullet during a budgetary crunch with no funding available for empire expansion will do that.

The code, and Mike’s replacements, went into training materials for the team. The original code may be found below; it can be summarized as:

  1. Open an instance of Excel and open up a spreadsheet containing details of a single quote
  2. For each of ~160 manually specified cells (almost all from one table on the same sheet in the workbook):
  3. Read the cell contents into a new SAS dataset (as a string of length 50, regardless of what’s in the cell).
  4. Merge the new dataset with the one created after processing the previous cell, overwriting the previous one.
  5. Append the dataset for this excel file to the one created for the previous excel file, overwriting the previous one, and close excel.

Mike’s replacement: 

libname quote excel "\\network\share\folder\file1.xls";
data quote1;
 set quote.summary_table;
run;
libname quote clear;

In a further institutional surprise, when signing off on the new approach, her department head authorized fallback cost-sharing for the internal bean-counting required to pay for the feature license required to make this approach work. She was just asked to not draw attention to this, so that the other group might continue paying themselves, otherwise, they’d be stuck with the original code: 

/* scraping macros */
%macro ReadCell(File=,Tab=Quality,Ref=,Col=,FMT=);

 data temp_read;
 run;

 filename ReadC dde "excel|[&file.]&tab.!&Ref." lrecl=500 ;
 data temp_read;
 infile ReadC truncover;
 input &Col. &FMT. ;
 output;
 run;
 filename ReadC clear;

%mend;

%macro MergeNewCell(In=, Out=, New=);
 data &out.;
 merge &in. &new.;
 run;
%mend;
%macro ReadCellMerge(file=,Tab=Quality,Ref=,Col=,FMT=);
 %ReadCell(file=&file.,Tab=&tab.,Ref=&ref.,Col=varname,FMT=&fmt. );
 %MergeNewCell(In=work.temp_scrape, Out=work.temp_scrape, New=temp_read);
%mend;
%macro FindVersion(file=);
 %global QuoteVer;
 %ReadCell(file=&file.,Tab=Version Control,Ref=r9c2,Col=varname,FMT=5.2 );
 data _null_;
 set temp_read;
 call symput("QuoteVer", trim(left(version)));
 run;
%mend;
;

%macro ScrapeQuote(Path=, File=);

 filename ddesys DDE "Excel|system";

/* data _null_;*/
/* file ddesys;*/
/* PUT "[open("'"'"&Path.\&file."'"'",,TRUE)]";*/
/* run;*/

 %OpenExcel( &path.,&file.);

 data work.temp_scrape;
 filename="&path.\&file.";
 run;

 /* quote calculator version */
/* %FindVersion(file=&file.);*/

/* DONT NEED THIS?*/
/* %if &QuoteVer.^=. %then %do; */
/* %ReadCellMerge(file=&file.,Tab=Version Control,Ref=r9c2,Col=varname,FMT=5.2 );*/

 /* SUMMARY */
 %ReadCellMerge(File=&file.,Tab=Summary,Ref=r3c3,Col=varname1,FMT=%nrstr($50.) );
 %ReadCellMerge(File=&file.,Tab=Summary,Ref=r3c4,Col=varname2,FMT=%nrstr($50.) );
 %ReadCellMerge(File=&file.,Tab=Summary,Ref=r3c5,Col=varname3,FMT=%nrstr($50.) );
 %ReadCellMerge(File=&file.,Tab=Summary,Ref=r4c3,Col=varname4,FMT=%nrstr($50.) );
 /*Snipped ~150 similar lines...*/
 %ReadCellMerge(File=&file.,Tab=Summary,Ref=r34c5,Col=varname160,FMT=%nrstr($50.) );

 /* close excel file */
 data _null_;
 file ddesys;
 put "[close("'"'"&Path.\&file."'"'", FALSE)]";
 run;
 filename ddesys clear;

 /* append data */
 data work.scrape_data;
 set work.scrape_data
 work.temp_scrape;
 run;

 /* clean up data from this scrape */
 %symdel QuoteVer;
 data work.temp_scrape;
 if _n_=1 then delete;
 run;

%mend;

/* Scrape!!! */
%ScrapeQuote(Path=%nrbquote(\\network\share\folder), File=%nrbquote(file1.xls));
%ScrapeQuote(Path=%nrbquote(\\network\share\folder), File=%nrbquote(file2.xls));
%ScrapeQuote(Path=%nrbquote(\\network\share\folder), File=%nrbquote(file3.xls));
/*Snipped a large number of manually-written macro calls*/
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!