· 4 years ago · Feb 24, 2021, 12:04 AM
1/****************************************************************************/
2/* Program Name : ImportDataFile */
3/* Purpose : Import data file as fixed width character data with */
4/* automatic input variable naming. */
5/* */
6/*--------------------------------------------------------------------------*/
7/* Output(s) : SAS dataset, macro variable &listHeader */
8/* Usage : dirData= Directory containing data file. */
9/* fileName= Filename including file extension. Must be */
10/* .csv, .txt, .tsv, .xls, or .xlsx. */
11/* dataset= Name of dataset output to WORK library. */
12/* delimiter= (optional) Delimiting string given in */
13/* quotes. Default for CSV is a comma, for */
14/* TXT/TSV a tab. This parameter may not be */
15/* set for Excel files. Doing so generates a */
16/* warning. */
17/* overOption= (optional) INFILE option. Default is */
18/* MISSOVER. Other choices are FLOWOVER, */
19/* STOPOVER, TRUNCOVER, or SCANOVER. */
20/* headerRow= (optional) Row corresponding to header in */
21/* an Excel file. Accepts R#C#:R#C#, but */
22/* should be given as R#. Default is R1. */
23/* sheet= Name of worksheet. Required for XLS or XLSX.*/
24/* range= Range of spreadsheet to be imported. */
25/* Required for XLS and XLSX. Use form */
26/* R#C#:R#C#. See example below. */
27/* prefix= (optional) String to append to beginning of */
28/* each variable name. Default is no prefix. */
29/* case= (optional) Toggle mix case variable naming. */
30/* Must be lower/upper/mixed. Default is */
31/* lower. */
32/* defLength= (optional) Character field length. Default */
33/* value is 100. */
34/* Structure : --Utilities-- */
35/* ClearFileRef */
36/* CompareVariablesToDDERange */
37/* EstablishSystemLink */
38/* EstablishWorkbookLink */
39/* IsEmpty */
40/* IsFileRef */
41/* IsFilePath */
42/* GetObsCount */
43/* GetVarCount */
44/* ListLength */
45/* ListElement */
46/* RemoveAllFormattingFromSheet */
47/* SetSystemOptions */
48/* */
49/* --Main/Sub Macros-- */
50/* ImportDataFile */
51/* EstablishDDELink */
52/* ReadHeaderIntoList */
53/* CreateLengthStatement */
54/* */
55/*--------------------------------------------------------------------------*/
56/* Example(s) : */
57/* %ImportDataFile( */
58/* dirData= &dirBasePath. */
59/* , fileName= &xlFile. */
60/* , dataset= xl_import */
61/* , prefix= raw_ */
62/* , sheet= &xlSheet. */
63/* , range= R2C1:R13C18 */
64/* , defLength= 100 */
65/* , overOption= MISSOVER */
66/* ); */
67/* */
68/* Template : */
69/* %ImportDataFile( */
70/* dirData= */
71/* , fileName= */
72/* , dataset= */
73/* , delimiter= */
74/* , overOption= */
75/* , headerRow= */
76/* , sheet= */
77/* , range= */
78/* , prefix= */
79/* , case= */
80/* , defLength= */
81/* ); */
82/* */
83/*--------------------------------------------------------------------------*/
84/* Note(s) : Although similar to PROC IMPORT, %ImportDataFile offers */
85/* greater control and a cleaner import with a uniform */
86/* data flow. Data is imported as fixed width character */
87/* data with no meta information (no formats, informats, */
88/* or labels). It also has greater flexibility in regards */
89/* to special characters within the data. This allows the */
90/* user to construct their data from a neutral starting */
91/* point as they see fit. */
92/* */
93/* %ImportDataFile assumes data contains headers. For */
94/* CSV/TXT/TSV data, it is assumed these headers are */
95/* in the first row and that data begins on the second */
96/* row. These assumptions are not made for Excel files. */
97/* */
98/* In order to import an Excel file, said Excel file must */
99/* be opened prior to running %ImportDataFile. This may */
100/* be handled via %ExcelOpen. %ExcelOpen is not included */
101/* here for simplicity and separation of responsibility. */
102/* */
103/* Sometimes when reading a workbook from a network drive, */
104/* program execution continues before the workbook is */
105/* loaded and a link can be established, causing errors. */
106/* This program ensures that this doesn't happen through */
107/* use of %EstablishDDELink. */
108/* */
109/* Sometimes a workbook will open with 'PROTECTED VIEW' */
110/* toggled on. When workbook protection is enabled, a */
111/* DDE link cannot be established. In the workbook, a */
112/* gold status bar will appear beneath the ribbon with a */
113/* 'Enable Editing' button. Click this button to disable */
114/* the 'PROTECTED VIEW'. This should only need to be done */
115/* a single time. */
116/* */
117/* Development : At heart, this program is an automation of a data step */
118/* import. %EstablishDDELink is used to ensure that */
119/* the execution timing of Excel does not interfere with */
120/* the overall process. %ReadHeaderIntoList does the */
121/* heavy lifting of automatically reading in the headers */
122/* and making sure they are turned into a valid length */
123/* statement. Aside from the actual creation of the */
124/* length statement, some fiddling occurs regarding the */
125/* INFILE statement, as this varies slightly depending on */
126/* the data type being imported. Documentation is provided */
127/* below for %ReadHeaderIntoList and %EstablishDDELink. */
128/* Please see these for more details. */
129/* */
130/* This process is composed in three primary parts: */
131/* - Establish a DDE link, if necessary */
132/* - Get data variables by reading in header */
133/* - Read in the remaining data */
134/* The remainder of this program consists of support */
135/* functions (with about 25% alone being validation). */
136/* Each individual part can exist independently of this */
137/* program. However, each has been optimized to work */
138/* within the current context, such as removing redundant */
139/* validations and assigning default values once. */
140/* */
141/* This program relies on Excel 4.0 macro functions. The */
142/* macrofun.hlp file can be downloaded from Microsoft */
143/* here: */
144/* */
145/* https://www.microsoft.com/en-us/download/ */
146/* details.aspx?displaylang=en&id=1465 */
147/* */
148/* In order to view the help file, it may be necessary */
149/* to also install WinHlp32.exe: */
150/* */
151/* https://support.microsoft.com/en-us/help/917607/ */
152/* error-opening-help-in-windows-based-programs- */
153/* feature-not-included-or-h */
154/* */
155/* (Yes, that address just ends in an 'h') Relevant */
156/* excerpts have been included at the bottom of this */
157/* program in the event that the given links go dead. */
158/* */
159/****************************************************************************/
160
161/****************************************************************************/
162/* Program Name : EstablishDDELink */
163/* Purpose : Macro utility to establish DDE Link with Excel before */
164/* allowing program execution to continue. Terminates */
165/* program if connection is not established within 10 */
166/* seconds. */
167/* */
168/*--------------------------------------------------------------------------*/
169/* Input(s) : Optional inputs marked with *. */
170/* fileRef = File reference. Cannot exceed 8 characters.*/
171/* *dirData = Directory of workbook. */
172/* *fileName = Name of workbook, including file extension.*/
173/* *sheetName = Name of worksheet to connect with. */
174/* *range = Range within workhseet, given as R#C#:R#C# */
175/* Output(s) : None */
176/* Usage : Two types of DDE links can be established: System and */
177/* Workbook. A system link requires only the fileRef */
178/* input. A workbook link requires all inputs. */
179/* Macro(s) : ClearFileRef, EstablishSystemLink, */
180/* EstablishWorkbookLink, SetSystemOptions */
181/* */
182/*--------------------------------------------------------------------------*/
183/* Example : System Link: */
184/* */
185/* %EstablishDDELink(fileRef=xlDDE); */
186/* */
187/* data _null_; */
188/* file xlDDE; */
189/* put '[QUIT()]'; */
190/* run; */
191/* */
192/* Workbook Link: */
193/* */
194/* %EstablishDDELink( fileRef= xlBook */
195/* , dirData= C:\temp */
196/* , fileName= example.xlsx */
197/* , sheetName= Sheet1 */
198/* , range= R2C1:R400C5 */
199/* ); */
200/* */
201/* data _raw_example; */
202/* infile xlBook dlm='09'x notab missover; */
203/* length */
204/* var1 $ 100 */
205/* var2 $ 100 */
206/* var3 8 */
207/* ; */
208/* input (_all_) (:) ; */
209/* run; */
210/* */
211/*--------------------------------------------------------------------------*/
212/* Note : Sometimes when reading a workbook from a network drive */
213/* program execution continues before the workbook is */
214/* loaded and a link can be established, causing errors. */
215/* This program ensures that this doesn't happen. */
216/* */
217/* Sometimes a workbook will open with 'PROTECTED VIEW' */
218/* toggled on. When workbook protection is enabled, a */
219/* DDE link cannot be established. In the workbook, a */
220/* gold status bar will appear beneath the ribbon with a */
221/* 'Enable Editing' button. Click this button to disable */
222/* the 'PROTECTED VIEW'. This should only need to be done */
223/* a single time. */
224/* */
225/* Developer : */
226/* */
227/****************************************************************************/
228
229/****************************************************************************/
230/* Program Name : ReadHeaderIntoList */
231/* Purpose : Reads header from a data file and outputs a macro */
232/* variable list of valid SAS variable names. */
233/* */
234/*--------------------------------------------------------------------------*/
235/* Input(s) : reference= Either a SAS fileref (such as DDE link) or */
236/* an absolute file path (C:\test\file.csv). */
237/* delimiter= Delimiter string. */
238/* prefix= (optional) String to be prefixed to each */
239/* header variable name. Default is nothing. */
240/* case= (optional) Set case of output headers to */
241/* upper, lower, or mixed. Default is mixed. */
242/* Output(s) : Global macro variable &listHeader */
243/* Macro(s) : IsEmpty, IsFileRef, IsFilePath, ListLength, */
244/* SetSystemOptions */
245/* */
246/*--------------------------------------------------------------------------*/
247/* Example(s) : %ReadHeaderIntoList( */
248/* reference= xlDDE */
249/* , delimiter= '09'x */
250/* , case= lower */
251/* , prefix= raw_ */
252/* ); */
253/* */
254/*--------------------------------------------------------------------------*/
255/* Notes(s) : */
256/* */
257/* Development : Reads in the first row of data as a single string, then */
258/* creates a pipe-delimited list by replacing the */
259/* delimiter string. All further processing is handled */
260/* via a pipe-delimited macro list. */
261/* */
262/* Ideally, we would want ReadHeaderIntoList to */
263/* behave as a macro function and return the list. The */
264/* problem is that */
265/* */
266/* "You cannot use a macro variable reference to */
267/* retrieve the value of a macro variable in the same */
268/* program (or step) in which SYMPUT creates that */
269/* macro variable and assigns it a value." */
270/* - SAS 9.3 Macro Language Reference: */
271/* CALL SYMPUT Rountine */
272/* */
273/* If we try to return listHeader as the last line of the */
274/* macro, SAS generates errors. If we try to use PROC SQL */
275/* to assign listHeader, the same problem occurs. Since */
276/* data can only be read in using the DATA step and the */
277/* only way to access the header outside of the data step */
278/* is to use SYMPUT, listHeader must be global. */
279/* */
280/* The NOQUOTELENMAX option must be toggled because of the */
281/* lines: */
282/* */
283/* header_i = scan("%superq(rawListHeader)", i, '|', 'm'); */
284/* element_i = scan("&listValid.", i, '|'); */
285/* */
286/* The macro variables are being called within a DATA */
287/* step and so must be quoted. However, this triggers */
288/* the quote warning. */
289/* */
290/* Sometimes when importing an excel spreadsheet, extra */
291/* blank columns will be imported. This is due to */
292/* trailing tab characters in subsequent columns. One */
293/* method of correcting this is to DELETE these columns */
294/* in Excel. Or, the blank columns can be retained and */
295/* removed during a cleaning process. */
296/* */
297/****************************************************************************/
298
299********************************************************************
300** Utilities / Sub Macros
301********************************************************************;
302%macro ClearFileRef(fileRef);
303 filename &fileRef. clear;
304%mend;
305
306%macro CompareVariablesToDDERange();
307 %local columnIndex numberOfDDEColumns;
308
309 %let columnIndex = %eval(%sysfunc(findc(&range., 'C', ib)) + 1);
310 %let numberOfDDEColumns = %sysfunc(substr(&range., &columnIndex));
311 %if %ListLength(&listHeader) ^= &numberOfDDEColumns %then
312 %put WARNING: [MACRO] Data file contains %ListLength(&listHeader) variables. RANGE argument has &numberOfDDEColumns columns.;
313%mend;
314
315%macro EstablishSystemLink(fileRef);
316 filename &fileRef. dde 'excel|system';
317%mend;
318
319%macro EstablishWorkbookLink(fileRef, dirData, fileName, sheetName, range);
320 filename &fileRef. dde "excel|&dirData.\[&fileName.]&sheetName.!&range.";
321%mend;
322
323%macro IsEmpty(macroVariable);
324 %sysevalf(%superq(¯oVariable)=, boolean)
325%mend;
326
327%macro IsFileRef(reference);
328 %local fileRefExists externalFileExists returnValue;
329
330 %let fileRefExists = %sysfunc(fexist(&reference.));
331 %let externalFileExists = %sysfunc(fileexist(&reference.));
332 %if &fileRefExists. = 1 and &externalFileExists. = 0 %then %let returnValue = 1;
333 %else %let returnValue = 0;
334 &returnValue
335%mend;
336
337%macro IsFilePath(reference);
338 %local fileRefExists externalFileExists returnValue;
339
340 %let fileRefExists = %sysfunc(fexist(&reference.));
341 %let externalFileExists = %sysfunc(fileexist(&reference.));
342 %if &fileRefExists. = 0 and &externalFileExists. = 1 %then %let returnValue = 1;
343 %else %let returnValue = 0;
344 &returnValue
345%mend;
346
347%macro GetObsCount(dataset);
348 %local exists returnValue closed;
349
350 %let exists = %sysfunc(open(&dataset));
351 %if &exists. %then %do;
352 %let returnValue = %sysfunc(attrn(&exists, nobs));
353 %let closed = %sysfunc(close(&exists));
354 %end;
355 %else %do;
356 %put ERROR: [&SYSMACRONAME.] Dataset %upcase(&dataset) does not exist.;
357 %abort cancel;
358 %end;
359 &returnValue
360%mend;
361
362%macro GetVarCount(dataset);
363 %local exists varCount closed;
364
365 %let exists = %sysfunc(open(&dataset));
366 %if &exists. %then %do;
367 %let varCount = %sysfunc(attrn(&exists, nvars));
368 %let closed = %sysfunc(close(&exists));
369 %end;
370 %else %do;
371 %put ERROR: [&SYSMACRONAME.] Dataset %upcase(&dataset) does not exist.;
372 %abort cancel;
373 %end;
374 &varCount
375%mend;
376
377%macro ListLength(list);
378 %local count;
379
380 %if %sysevalf(%superq(list)=, boolean) %then %let count = 0;
381 %else %let count = %eval(%sysfunc(countc(&list., |)) + 1);
382 &count
383%mend;
384
385%macro ListElement(list, n);
386 %local nthElement;
387
388 %let nthElement = %sysfunc(scan(%superq(&list.), &n., |, m));
389 &nthElement
390%mend;
391
392%macro RemoveAllFormattingFromSheet(fileRef, sheet);
393 data _null_;
394 file &fileRef.;
395 /* Select sheet of interest */
396 put "[WORKBOOK.ACTIVATE(""&sheet."")]";
397 /* Select first cell */
398 put '[FORMULA.GOTO("R1C1")]';
399 /* Apply dummy filter of ">2" to first column */
400 put '[FILTER(1, ">2")]';
401 /* Disable filters */
402 put '[FILTER()]';
403 /* Select all */
404 put '[SELECT("R[0]C[0]:R[1048575]C[16383]", "R[0]C[0]")]';
405 /* Unhide rows */
406 put '[ROW.HEIGHT(,,TRUE, 2)]';
407 /* Unhide columns */
408 put '[COLUMN.WIDTH(,,TRUE, 2)]';
409 /* Remove all formatting */
410 put '[CLEAR(2)]';
411 /* Autofit column width */
412 put '[COLUMN.WIDTH(,,TRUE, 3)]';
413 run;
414%mend;
415
416%macro SetSystemOptions(opt1, opt2, opt3);
417 options &opt1. &opt2. &opt3.;
418%mend;
419
420%macro ImportDataFile(dirData=, fileName=, dataset=, delimiter=, overOption=MISSOVER, headerRow=R1, sheet=, range=, prefix=, case=lower, defLength=100) / minoperator mindelimiter=',';
421%put NOTE: [MACRO] Executing: ImportDataFile(dirData=&dirData, fileName=&fileName, dataset=&dataset, delimiter=&delimiter, overOption=&overOption, headerRow=&headerRow, sheet=&sheet, range=&range, prefix=&prefix, case=&case, defLength=&defLength);
422
423 %local
424 macroStart
425 case
426 extension
427 HeaderRef
428 lengthStatement
429 delimiter
430 InfileRef
431 infileStatement
432 numberOfRecords
433 numberOfVars
434 duration
435 ;
436
437 %global
438 listHeader
439 originalNOTES
440 originalQUOTELENMAX
441 ;
442
443 %let macroStart = %sysfunc(datetime());
444 %let originalNOTES = %sysfunc(getoption(notes));
445 %let originalQUOTELENMAX = %sysfunc(getoption(noquotelenmax));
446
447 %SetSystemOptions(nonotes);
448
449********************************************************************
450** Validation
451********************************************************************;
452 %if %IsEmpty(dirData) %then %do;
453 %put ERROR: [&SYSMACRONAME.] DIRDATA argument is blank.;
454 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
455 %abort cancel;
456 %end;
457
458 %if %IsEmpty(fileName) %then %do;
459 %put ERROR: [&SYSMACRONAME.] FILENAME argument is blank.;
460 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
461 %abort cancel;
462 %end;
463
464 %if %IsEmpty(dataset) %then %do;
465 %put ERROR: [&SYSMACRONAME.] DATASET argument is blank.;
466 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
467 %abort cancel;
468 %end;
469
470 %if not(%IsEmpty(prefix)) and not(%sysfunc(nvalid(&prefix, v7))) %then %do;
471 %put ERROR: [&SYSMACRONAME.] Invalid PREFIX="&prefix.";
472 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
473 %abort cancel;
474 %end;
475
476 %let case = %upcase(&case.);
477
478 %if not(&case. in (LOWER, UPPER, MIXED)) %then %do;
479 %put ERROR: [&SYSMACRONAME.] Invalid case option: &case. Must be LOWER, UPPER, MIX.;
480 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
481 %abort cancel;
482 %end;
483
484 %let extension = %upcase(%scan(&fileName., 1, '.', b));
485
486 %if not(&extension. in (TXT, TSV, CSV, XLS, XLSX)) %then %do;
487 %put ERROR: [&SYSMACRONAME.] Invalid file type: &extension. Must be TXT, TSV, CSV, XLS, XLSX.;
488 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
489 %abort cancel;
490 %end;
491
492 %if &extension. in (XLS, XLSX) and %IsEmpty(sheet) %then %do;
493 %put ERROR: [&SYSMACRONAME.] SHEET argument undefined.;
494 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
495 %abort cancel;
496 %end;
497
498 %if &extension. in (XLS, XLSX) and %IsEmpty(range) %then %do;
499 %put ERROR: [&SYSMACRONAME.] RANGE argument undefined.;
500 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
501 %abort cancel;
502 %end;
503
504 %if not(&extension. in (XLS, XLSX)) and not(%IsEmpty(sheet)) %then %do;
505 %put ERROR: [&SYSMACRONAME.] SHEET argument only valid for XLS or XLSX files.;
506 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
507 %abort cancel;
508 %end;
509
510 %if not(&extension. in (XLS, XLSX)) and not(%IsEmpty(range)) %then %do;
511 %put ERROR: [&SYSMACRONAME.] RANGE argument only valid for XLS or XLSX files.;
512 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
513 %abort cancel;
514 %end;
515
516**********************************
517*** Define delimiter
518**********************************;
519 %if %IsEmpty(delimiter) %then %do;
520 %if &extension. in (XLS, XLSX) %then %let delimiter = '09'x;
521 %else %if &extension. = CSV %then %let delimiter = ',';
522 %else %if &extension. in (TXT, TSV) %then %let delimiter = '09'x;
523 %else %do;
524 %put ERROR: [&SYSMACRONAME.] Delimiter error.;
525 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
526 %abort cancel;
527 %end;
528 %end;
529
530 %if &extension. in (XLS, XLSX) and &delimiter ^= '09'x %then %do;
531 %put WARNING: [&SYSMACRONAME.] Delimiter for Excel file must be '09'x.;
532 %put WARNING: [&SYSMACRONAME.] Delimiter set to '09'x.;
533 %let delimiter = '09'x;
534 %end;
535
536********************************************************************
537** Prep Excel Worksheet
538********************************************************************;
539 %if &extension. in (XLS, XLSX) %then %do;
540 %let DDECommandRef = DDEcmd;
541 %EstablishDDELink(fileRef=&DDECommandRef.);
542 %RemoveAllFormattingFromSheet(fileRef=&DDECommandRef., sheet=&sheet.);
543 %end;
544
545********************************************************************
546** Get header
547********************************************************************;
548
549**********************************
550*** Define file reference
551**********************************;
552 %if &extension. in (XLS, XLSX) %then %do;
553 %let HeaderRef = DDEHead;
554 %EstablishDDELink(
555 fileRef= &HeaderRef.
556 , dirData= &dirData.
557 , fileName= &fileName.
558 , sheetName= &sheet.
559 , range= &headerRow.
560 );
561 %end;
562 %else %if &extension. in (CSV, TXT, TSV) %then
563 %let HeaderRef = %sysfunc(dequote(&dirData.))\&fileName.;
564
565 %ReadHeaderIntoList(reference=&HeaderRef., delimiter=&delimiter., prefix=&prefix., case=&case.);
566
567********************************************************************
568** Create length statement
569********************************************************************;
570 %let lengthStatement = %CreateLengthStatement(&listHeader., &defLength.);
571
572********************************************************************
573** Import data
574********************************************************************;
575
576**********************************
577*** Define infile statement
578**********************************;
579 %if &extension. in (XLS, XLSX) %then %do;
580 %let InfileRef = DDESheet;
581 %EstablishDDELink(
582 fileRef= &InfileRef.
583 , dirData= &dirData.
584 , fileName= &fileName.
585 , sheetName= &sheet.
586 , range= &range.
587 );
588 %let infileStatement = infile &InfileRef. dlmstr=&delimiter. dsd notab &overOption.;
589 %CompareVariablesToDDERange();
590 %end;
591 %else %if &extension. in (CSV, TXT, TSV) %then %do;
592 %let InfileRef = %sysfunc(dequote(&dirData.))\&fileName.;
593 %let infileStatement = infile "&InfileRef." dlmstr=&delimiter. dsd &overOption. firstobs = 2 end=last_record;
594 %end;
595
596**********************************
597*** Perform import
598**********************************;
599 data &dataset.;
600 &infileStatement.;
601
602 length &lengthStatement. ;
603
604 input (_all_) (:) ;
605
606 run;
607
608********************************************************************
609** Housekeeping
610********************************************************************;
611 %let numberOfRecords = %GetObsCount(&dataset.);
612 %let numberOfVars = %GetVarCount(&dataset.);
613
614 %SetSystemOptions(notes);
615
616 %put;
617 %put NOTE: [MACRO] The dataset WORK.%upcase(&dataset.) has &numberOfRecords. observations and &numberOfVars. variables.;
618 %put NOTE: [MACRO] IMPORTDATAFILE macro used (Total process time):;
619
620 %let duration = %sysfunc(putn(%sysevalf(%sysfunc(datetime()) - ¯oStart.), time12.3));
621 %if %sysfunc(minute("&duration."t)) > 0 %then %do;
622 %put NO%str(TE-) real time %substr(&duration., 3, 8);
623 %end;
624 %else %do;
625 %put NO%str(TE-) real time %substr(&duration., 6, 5) seconds;
626 %end;
627
628 %put;
629
630 %SetSystemOptions(&originalNotes., &originalQUOTELENMAX.);
631
632%mend;
633
634%macro EstablishDDELink(fileRef, dirData, fileName, sheetName, range);
635%put NOTE: [&SYSMACRONAME] Executing: EstablishDDELink(fileRef=&fileRef, dirData=&dirData, fileName=&fileName, sheetName=&sheetName, range=&range);
636
637 %local dirData linkConnection stopTime closeReturnCode;
638
639********************************************************************
640** Validate arguments
641********************************************************************;
642 %if %IsEmpty(fileRef) %then %do;
643 %put ERROR: [&SYSMACRONAME] fileRef is blank.;
644 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
645 %abort cancel;
646 %end;
647
648 %if %length(&fileRef.) > 8 %then %do;
649 %put ERROR: [&SYSMACRONAME] Fileref &fileRef exceeds 8 character limit.;
650 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
651 %abort cancel;
652 %end;
653
654 %if not %IsEmpty(dirData) %then %let dirData = %sysfunc(dequote(&dirData.));
655
656********************************************************************
657** Assign fileref according to link type
658********************************************************************;
659 %if %IsEmpty(dirData)
660 and %IsEmpty(fileName)
661 and %IsEmpty(sheetName)
662 and %IsEmpty(range) %then %EstablishSystemLink(&fileRef.);
663 %else %EstablishWorkbookLink(&fileRef., &dirData., &fileName., &sheetName., &range.);
664
665********************************************************************
666** Check that link has been established
667********************************************************************;
668 %let linkConnection = %sysfunc(fopen(&fileRef, S));
669
670 %if not (&linkConnection. > 0) %then %do;
671
672 /*Run until either Excel opens (linkConnection > 0)
673 or until 10 seconds have passed.*/
674 %let stopTime = %sysevalf(%sysfunc(datetime()) + 10);
675
676 %do %until (&linkConnection. > 0);
677 %if (%sysfunc(datetime()) >= &stopTime.) %then %do;
678 %put ERROR: [&SYSMACRONAME] DDE system link was not established. Operation timed out.;
679 %ClearFileRef(fileRef.);
680 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
681 %abort cancel;
682 %end;
683
684 %let linkConnection = %sysfunc(fopen(&fileRef, S));
685 %end;
686 %end;
687
688********************************************************************
689** Housekeeping
690********************************************************************;
691 %let closeReturnCode = %sysfunc(fclose(&linkConnection));
692
693%mend;
694
695%macro ReadHeaderIntoList(reference, delimiter, prefix, case) / minoperator mindelimiter=',';
696%put NOTE: [MACRO] Executing: ReadHeaderIntoList(reference=&reference, delimiter=&delimiter, prefix=&prefix, case=&case);
697
698 %local fileSpecification notab delimiter;
699 %global listHeader;
700
701 %SetSystemOptions(nonotes);
702
703 %if %IsEmpty(reference) %then %do;
704 %put ERROR: [&SYSMACRONAME.] REFERENCE argument is blank.;
705 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
706 %abort cancel;
707 %end;
708
709********************************************************************
710** Determine infile statement options
711********************************************************************;
712 /*SAS filerefs exist only for Excel files*/
713 %if %IsFileRef(&reference.) %then %do;
714 %let fileSpecification = &reference.;
715 %let notab = notab;
716 %end;
717 /*Absolute references only for CSV,TXT,TSV files*/
718 %else %if %IsFilePath(&reference.) %then %do;
719 %let fileSpecification = "&reference.";
720 %let notab = ;
721 %let extension = %upcase(%scan(&reference., 1, '.', b));
722 %end;
723 %else %do;
724 %put ERROR: [&SYSMACRONAME.] Invalid input REFERENCE: [&reference.];
725 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
726 %abort cancel;
727 %end;
728
729********************************************************************
730** Read in header
731********************************************************************;
732 data _null_;
733 infile &fileSpecification. dlmstr = '```#@' ¬ab. obs = 1 lrecl = 32767 ;
734 length
735 raw_header_line $ 32767
736 raw_with_pipes $ 32767
737 ;
738 input raw_header_line;
739
740 raw_with_pipes = tranwrd(raw_header_line, &delimiter., '|');
741 call symput('rawListHeader', strip(raw_with_pipes));
742 run;
743
744********************************************************************
745** Transform headers into valid variable names
746********************************************************************;
747 %SetSystemOptions(noquotelenmax);
748 data _null_;
749 length
750 i 8
751 listLength 8
752 header_i $ 32767
753 temp_i $ 32767
754 listValid $ 32767
755 ;
756 listLength = %ListLength(%superq(rawListHeader));
757
758 do i = 1 to listLength;
759 header_i = scan("%superq(rawListHeader)", i, '|', 'm');
760
761**********************************
762*** Apply prefix
763**********************************;
764 if not missing(header_i) then prefixed_i = cats("&prefix.", header_i);
765 else prefixed_i = header_i;
766
767**********************************
768*** Apply case
769**********************************;
770 if "&case." = "LOWER" then cased_i = lowcase(prefixed_i);
771 else if "&case." = "UPPER" then cased_i = upcase(prefixed_i);
772 else cased_i = prefixed_i;
773
774**********************************
775*** Keep valid otherwise correct
776**********************************;
777 if nvalid(cased_i, 'v7') then do;
778 if i = 1 then listValid = cased_i;
779 else listValid = catx('|', listValid, cased_i);
780 end;
781 else do;
782
783**********************************
784*** Fill in blank headers
785**********************************;
786 if missing(cased_i) and "&case." = "UPPER" then temp_i = "%upcase(&prefix.)NO_HEADER";
787 else if missing(cased_i) then temp_i = "&prefix.no_header";
788
789**********************************
790*** Replace blanks with _ and
791*** Remove invalid characters
792**********************************;
793 else do;
794 replaced_space_with_underscore = tranwrd(strip(cased_i), ' ', '_');
795 temp_i = compress(replaced_space_with_underscore, '_', 'kin');
796 end;
797
798**********************************
799*** Make first char _ if digit
800**********************************;
801 if anydigit(temp_i) = 1 then temp_i = cats('_', temp_i);
802
803**********************************
804*** Trim length to 32
805**********************************;
806 if length(temp_i) > 32 then temp_i = substr(temp_i, 1, 32);
807
808**********************************
809*** Verify valid V7 name
810**********************************;
811 if not nvalid(temp_i, 'v7') then do;
812 put 'ERROR: [&SYSMACRONAME.] Error cleaning header ' i +(-1) '. Invalid SAS name.';
813 call execute('
814 %SetSystemOptions(&originalNOTES., &originalQUOTELENMAX.);
815 data _null_;
816 abort cancel nolist;
817 run;');
818 stop;
819 end;
820
821 if i = 1 then listValid = temp_i;
822 else listValid = catx('|', listValid, temp_i);
823 end;
824
825 output;
826 end;
827 call symput('listValid', strip(listValid));
828 run;
829
830********************************************************************
831** Append repeated headers with incremented value
832********************************************************************;
833 /*Use hash table with key being each header and value
834 corresponding to the number of occurences. Create new
835 header list as follows: If first occurence of a header,
836 add to list. If not first occurence, ruthlessly append
837 occurence number (ensuring validity) and add to list.
838 Beware: SAS documentation for hashes contains syntax
839 errors.*/
840 data _null_;
841 length
842 element_i $ 32
843 item $ 32
844 occurrences 8
845 new_list $ 32767
846 ;
847
848 declare hash h();
849 h.defineKey('item');
850 h.defineData('item', 'occurrences');
851 h.defineDone();
852 call missing(item, occurrences);
853
854 listLength = input("%ListLength(&listValid.)", 8.);
855 do i = 1 to listLength;
856 element_i = scan("&listValid.", i, '|');
857
858 if not (h.find(key: element_i) = 0) then do;
859 h.add(key: element_i, data: element_i, data: 1);
860 new_list = catx('|', new_list, element_i);
861 end;
862 else do;
863 occurrences + 1;
864 h.replace(key: element_i, data: element_i, data: occurrences);
865
866 len = length(element_i);
867 digits = ceil(log10(occurrences + 1));
868
869 if (len + digits) > 32 then
870 new_element = cats(substr(element_i, 1, len - digits), occurrences);
871 else new_element = cats(element_i, occurrences);
872
873 new_list = catx('|', new_list, new_element);
874 end;
875 end;
876
877 call symput('listHeader', strip(new_list));
878 run;
879%mend;
880
881%macro CreateLengthStatement(listHeader, defLength);
882 %local lengthStatement header_h;
883
884 %let lengthStatement=;
885 %do h = 1 %to %ListLength(&listHeader.);
886 %let header_h = %ListElement(listHeader, &h);
887 %if &h. = 1 %then %let lengthStatement = &header_h. $ &defLength. ;
888 %else %let lengthStatement = &lengthStatement. &header_h. $ &defLength. ;
889 %end;
890 %let lengthStatement = &lengthStatement;
891 &lengthStatement
892%mend;
893
894/****************************************************************************/
895/* */
896/* WORKBOOK.ACTIVATE */
897/* */
898/* Macro Sheets Only */
899/* Equivalent to activating a worksheet by clicking on its tab. */
900/* */
901/* Syntax */
902/* */
903/* WORKBOOK.ACTIVATE(sheet_name) */
904/* Sheet_name is the name of the document you want to activate within */
905/* the active workbook. */
906/* */
907/****************************************************************************/
908
909/****************************************************************************/
910/* */
911/* FORMULA.GOTO */
912/* */
913/* Macro Sheets Only */
914/* Equivalent to choosing the Go To command from the Edit menu or to */
915/* pressing F5. Scrolls through the worksheet and selects a named area or */
916/* reference. Use FORMULA.GOTO to select a range on any open workbook; */
917/* use SELECT to select a range on the active workbook. */
918/* */
919/* Syntax */
920/* */
921/* FORMULA.GOTO(reference, corner) */
922/* FORMULA.GOTO?(reference, corner) */
923/* */
924/* Reference specifies where to scroll and what to select. */
925/* */
926/* Reference should be either an external reference to a workbook, an */
927/* R1C1-style reference in the form of text (see the second example */
928/* following), or a name. If the Go To command has already been carried */
929/* out, reference is optional. If reference is omitted, it is assumed to */
930/* be the reference of the cells you selected before the previous Go To */
931/* command or FORMULA.GOTO macro function was carried out. This feature */
932/* distinguishes FORMULA.GOTO from SELECT. */
933/* */
934/* Corner is a logical value that specifies whether to scroll through */
935/* the window so that the upper-left cell in reference is in the upper- */
936/* left corner of the active window. If corner is TRUE, Microsoft */
937/* Excel places reference in the upper-left corner of the window; */
938/* if FALSE or omitted, Microsoft Excel scrolls through normally. */
939/* */
940/* Tip Microsoft Excel keeps a list of the cells you've selected with */
941/* previous FORMULA.GOTO functions or Go To commands. When you use */
942/* FORMULA.GOTO with GET.WORKSPACE(41), which returns a horizontal array */
943/* of previous Go To selections, you can backtrack through multiple */
944/* previous selections. See the last example below. */
945/* */
946/* Remarks */
947/* */
948/* * If you are recording a macro when you choose the Go To command, the */
949/* reference you enter in the Reference box of the Go To dialog box is */
950/* recorded as text in the R1C1 reference style. */
951/* */
952/* * If you are recording a macro when you double-click a cell that has */
953/* precedents on another worksheet, Microsoft Excel records a */
954/* FORMULA.GOTO function. */
955/* */
956/* Examples */
957/* */
958/* Each of the following macro formulas goes to cell A1 on the active */
959/* worksheet: */
960/* */
961/* FORMULA.GOTO(!$A$1) */
962/* FORMULA.GOTO("R1C1") */
963/* */
964/* */
965/* Each of the following macro formulas goes to the cells named Sales on */
966/* the active worksheet and scrolls through the worksheet so that the */
967/* upper-left corner of Sales is in the upper-left corner of the window: */
968/* */
969/* FORMULA.GOTO(!Sales, TRUE) */
970/* FORMULA.GOTO("Sales", TRUE) */
971/* */
972/* */
973/* The following macro formula goes to the cells that were selected by */
974/* the third most recent FORMULA.GOTO function or Go To command: */
975/* */
976/* FORMULA.GOTO(INDEX(GET.WORKSPACE(41), 1, 3)) */
977/* */
978/****************************************************************************/
979
980/****************************************************************************/
981/* */
982/* FILTER */
983/* */
984/* Filters lists of data one column at a time. Only one list can be */
985/* filtered on any one sheet at a time. */
986/* */
987/* Syntax */
988/* */
989/* FILTER(field_num, criteria1, operation, criteria2) */
990/* FILTER?(field_num, criteria1, operation, criteria2) */
991/* */
992/* Field_num is the number of the field that you want to filter. */
993/* Fields are numbered from left to right starting with 1. */
994/* Criteria1 is a text string specifying criteria for filtering a list, */
995/* such as ">2". If you want to include all items in the list,*/
996/* omit this argument. */
997/* Operation is a number that specifies how you want criteria2 used */
998/* with criteria1: */
999/* */
1000/* Number Operation Used */
1001/* */
1002/* 1 or omitted AND */
1003/* 2 OR */
1004/* */
1005/* Criteria2 is a text string specifying criteria for filtering a list, */
1006/* such as ">2". If you include this argument, operation is */
1007/* required. */
1008/* */
1009/* Remarks */
1010/* */
1011/* If you omit all arguments, FILTER toggles the display of filter arrows. */
1012/* */
1013/****************************************************************************/
1014
1015/****************************************************************************/
1016/* */
1017/* SELECT Syntax 1 */
1018/* */
1019/* Macro Sheets Only */
1020/* */
1021/* Equivalent to selecting cells or changing the active cell. There are */
1022/* three syntax forms of SELECT. Use syntax 1 to select a cell on a */
1023/* worksheet or macro sheet; use one of the other syntax forms to select */
1024/* worksheet or macro sheet objects or chart items. */
1025/* */
1026/* Syntax */
1027/* */
1028/* SELECT(selection, active_cell) */
1029/* Selection is the cell or range of cells you want to select. */
1030/* Selection can be a reference to the active worksheet, */
1031/* such as !$A$1:$A$3 or !Sales, or an R1C1-style reference */
1032/* to a cell or range relative to the active cell in the */
1033/* current selection, such as "R[-1]C[-1]:R[1]C[1]". The */
1034/* reference must be in text form. If selection is */
1035/* omitted, the current selection is used. */
1036/* Active_cell is the cell in selection you want to make the active */
1037/* cell. Active_cell can be a reference to a single cell */
1038/* on the active worksheet, such as !$A$1, or an R1C1-style */
1039/* reference relative to the active cell, such as */
1040/* "R[-1]C[-1]". The reference must be in text form. If */
1041/* active_cell is omitted, SELECT makes the cell in the */
1042/* upper-left corner of selection the active cell. */
1043/* */
1044/* Remarks */
1045/* */
1046/* * Active_cell must be within selection. If it is not, an error message */
1047/* is displayed and SELECT returns the #VALUE! error value. */
1048/* * If you are recording a macro using relative references, Microsoft */
1049/* Excel records the action using R1C1-style relative references in */
1050/* the form of text. */
1051/* * If you are recording using absolute references, Microsoft Excel */
1052/* records the action using R1C1-style absolute references in the form */
1053/* of text. */
1054/* * You cannot give an external reference to a specific sheet as the */
1055/* selection argument. The sheet on which you want to make a selection */
1056/* must be active when you use SELECT. Use FORMULA.GOTO to make a */
1057/* selection on a sheet or macro sheet in an external workbook. */
1058/* */
1059/* Tip You can enter data in a cell without selecting the cell by using */
1060/* the reference arguments to the CUT, COPY, or FORMULA functions. */
1061/* */
1062/* Examples */
1063/* */
1064/* The following macro formula selects cells C3:E5 on the active worksheet */
1065/* and makes C5 the active cell: */
1066/* */
1067/* SELECT(!$C$3:$E$5, !$C$5) */
1068/* */
1069/* */
1070/* If the active cell is C3, the following macro formula selects cells */
1071/* E5:G7 and makes cell F6 the active cell in the selection: */
1072/* */
1073/* SELECT("R[2]C[2]:R[4]C[4]", "R[1]C[1]") */
1074/* */
1075/* */
1076/* You can also make multiple nonadjacent selections with SELECT. */
1077/* The following macro formula selects a number of nonadjacent ranges: */
1078/* */
1079/* SELECT("R1C1, R3C2:R4C3, R8C4:R10C5") */
1080/* */
1081/* */
1082/* The following sequence of macro formulas moves the active cell right, */
1083/* left, down, and up within the selection, just as TAB, SHIFT+TAB, ENTER, */
1084/* and SHIFT+ENTER do: */
1085/* */
1086/* SELECT(, "RC[1]") */
1087/* SELECT(, "RC[-1]") */
1088/* SELECT(, "R[1]C") */
1089/* SELECT(, "R[-1]C") */
1090/* */
1091/* */
1092/* Use SELECT with the OFFSET function to select a new range a specified */
1093/* distance away from the current range. For example, the following macro */
1094/* formula selects a range that is the same size as the current range, one */
1095/* column over: */
1096/* */
1097/* SELECT(OFFSET(SELECTION(), 0, 1)) */
1098/* */
1099/****************************************************************************/
1100
1101/****************************************************************************/
1102/* */
1103/* ROW.HEIGHT */
1104/* */
1105/* Macro Sheets Only */
1106/* */
1107/* Equivalent to choosing the Height command on the Row submenu of */
1108/* the Format menu. Changes the height of the rows in a reference. */
1109/* */
1110/* Syntax */
1111/* */
1112/* ROW.HEIGHT(height_num, reference, standard_height, type_num) */
1113/* ROW.HEIGHT?(height_num, reference, standard_height, type_num) */
1114/* */
1115/* Height_num specifies how high you want the rows to be in */
1116/* points. If standard_height is TRUE, height_num is */
1117/* ignored. */
1118/* Reference specifies the rows for which you want to change the */
1119/* height. */
1120/* */
1121/* * If reference is omitted, the reference is assumed to be the */
1122/* current selection. */
1123/* * If reference is specified, it must be either an external */
1124/* reference to the active worksheet, such as !$2:$4 or !Database, */
1125/* or an R1C1-style reference in the form of text or a name, such */
1126/* as "R1:R3", "R[-4]:R[-2]", or Database. */
1127/* * If reference is a relative R1C1-style reference in the form of */
1128/* text, it is assumed to be relative to the active cell. */
1129/* */
1130/* Standard_height is a logical value that sets the row height as */
1131/* determined by the font in each row. */
1132/* */
1133/* * If standard_height is TRUE, Microsoft Excel sets the row height */
1134/* to a standard height that may vary from row to row depending on */
1135/* the fonts used in each row, ignoring height_num. */
1136/* * If standard_height is FALSE or omitted, Microsoft Excel sets the */
1137/* row height according to height_num. */
1138/* */
1139/* Type_num is a number from 1 to 3 corresponding to selecting the */
1140/* Hide, Unhide, or AutoFit commands from the Row submenu. */
1141/* */
1142/* Type_num Action taken */
1143/* */
1144/* 1 Hides the row selection by setting the row height to 0 */
1145/* 2 Unhides the row selection by setting the row height to the */
1146/* value set before the selection was hidden */
1147/* 3 Sets the row selection to an AutoFit height, which varies from */
1148/* row to row depending on how large the font is in any cell in */
1149/* each row or on how many lines of text are wrapped */
1150/* */
1151/* Remarks */
1152/* */
1153/* * If any of the argument settings conflict, such as when */
1154/* standard_height is TRUE and type_num is 3, Microsoft Excel uses */
1155/* the type_num argument and ignores any arguments that conflict */
1156/* with type_num. */
1157/* * If you are recording a macro while using a mouse, and you change */
1158/* row heights by dragging the row border, Microsoft Excel records */
1159/* the reference of the rows using R1C1-style references in the */
1160/* form of text. If Uses Relative References is selected, Microsoft */
1161/* Excel uses R1C1-style relative references. If Uses Relative */
1162/* References is not selected, Microsoft Excel uses R1C1-style */
1163/* absolute references. */
1164/* */
1165/****************************************************************************/
1166
1167/****************************************************************************/
1168/* */
1169/* COLUMN.WIDTH */
1170/* */
1171/* Macro Sheets Only */
1172/* */
1173/* Equivalent to choosing the Width command on the Column submenu of the */
1174/* Format menu. Changes the width of the columns in the specified */
1175/* reference. */
1176/* */
1177/* Syntax */
1178/* */
1179/* COLUMN.WIDTH(width_num, reference, standard, type_num, standard_num) */
1180/* COLUMN.WIDTH?(width_num, reference, standard, type_num, standard_num) */
1181/* */
1182/* Width_num specifies how wide you want the columns to be in units of */
1183/* one character of the font corresponding to the Normal */
1184/* cell style. Width_num is ignored if standard is TRUE or */
1185/* if type_num is provided. */
1186/* */
1187/* Reference specifies the columns for which you want to change the */
1188/* width. */
1189/* */
1190/* * If reference is specified, it must be either an external reference */
1191/* to the active worksheet, such as !$A:$C or !Database, or an */
1192/* R1C1-style reference in the form of text, such as "C1:C3", */
1193/* "C[-4]:C[-2]", or "Database". */
1194/* * If reference is a relative R1C1-style reference in the form of */
1195/* text, it is assumed to be relative to the active cell. */
1196/* * If reference is omitted, it is assumed to be the current */
1197/* selection. */
1198/* */
1199/* Standard is a logical value corresponding to the Standard Width */
1200/* command from the Column submenu on the Format menu. */
1201/* */
1202/* * If standard is TRUE, Microsoft Excel sets the column width to the */
1203/* currently defined standard (default) width and ignores width_num. */
1204/* * If standard is FALSE or omitted, Microsoft Excel sets the width */
1205/* according to width_num or type_num. */
1206/* */
1207/* Type_num is a number from 1 to 3 corresponding to the Hide, Unhide, */
1208/* or AutoFit Selection commands, respectively, on the Column */
1209/* submenu of the Format menu. */
1210/* */
1211/* Type_num Action taken */
1212/* */
1213/* 1 Hides the column selection by setting the column width to 0 */
1214/* 2 Unhides the column selection by setting the column width to the */
1215/* value set before the selection was hidden */
1216/* 3 Sets the column selection to a best-fit width, which varies from */
1217/* column to column depending on the length of the longest data */
1218/* string in each column */
1219/* */
1220/* Standard_num specifies how wide the standard width is, and is */
1221/* measured in points. If standard_num is omitted, the */
1222/* standard width setting remains unchanged. */
1223/* */
1224/* Remarks */
1225/* */
1226/* * Changing the value of standard_num changes the width of all */
1227/* columns except those that have been set to a custom value. */
1228/* * If any of the argument settings conflict, such as when standard is */
1229/* TRUE and type_num is 3, Microsoft Excel uses the type_num argument */
1230/* and ignores any arguments that conflict with type_num. */
1231/* * If you are recording a macro while using a mouse and you change */
1232/* column widths by dragging the column border, Microsoft Excel */
1233/* records the references of the columns using R1C1-style references */
1234/* in the form of text. */
1235/* */
1236/****************************************************************************/
1237
1238/****************************************************************************/
1239/* */
1240/* CLEAR */
1241/* */
1242/* Macro Sheets Only */
1243/* */
1244/* Equivalent to choosing the Clear command from the Edit menu. Clears */
1245/* contents, formats, notes, or all of these from the active worksheet or */
1246/* macro sheet. Clears series or formats from the active chart. */
1247/* */
1248/* Syntax */
1249/* */
1250/* CLEAR(type_num) */
1251/* CLEAR?(type_num) */
1252/* */
1253/* Type_num is a number from 1 to 4 specifying what to clear. Only values */
1254/* 1, 2, and 3 are valid if the selected item is a chart. */
1255/* */
1256/* On a worksheet or macro sheet, or if an entire chart is selected, the */
1257/* following occurs. */
1258/* */
1259/* Type_num Clears */
1260/* */
1261/* 1 All */
1262/* 2 Formats (if a chart, clears the chart format or clears pictures) */
1263/* 3 Contents (if a chart, clears all data series) */
1264/* 4 Notes (including sound notes; this does not apply to charts) */
1265/* */
1266/* On a chart, if a single point, an entire data series, error bars, or a */
1267/* trend line is selected, the following occurs. */
1268/* */
1269/* Type_num Clears */
1270/* */
1271/* 1 Selected series, error bars, or trend line */
1272/* 2 Format in the selected point, series, error bars, or trend line */
1273/* */
1274/* If type_num is omitted, the default values are set as shown in the */
1275/* following table. */
1276/* */
1277/* Active sheet Type_num */
1278/* */
1279/* Worksheet 3 */
1280/* Macro sheet 3 */
1281/* Chart (with no selection) 1 */
1282/* Chart (with item selected) Deletes the selected item */
1283/* */
1284/****************************************************************************/
1285