|
AD15 One common programming task in the pharmaceutical industry is to transfer external data such as lab, ECG, PK, and other non-case-report-form data to SAS from Excel. Spreadsheets can be read with the SAS import wizard or procedures such as PROC ACCESS and PROC IMPORT, but often must be modified by adding or editing column names, formatting cells, deleting blank columns or rows, or moving to different platforms. However, the responsibility of the owner of original file will be lost if any modification to th e file is made. Also the audit trail process required by 21 CFR Part 11 can not be simplified if such data transfers are made with the non-original spreadsheets. This paper presents a SAS macro, %Qexcel, which has the capability and flexibility to han dle all types of external data to meet the challenges. The macro provides total control over the Excel import with the following features: (1) automatically detecting the worksheet name and cell-range, (2) allowing the user to select any row for data vari able names and labels, (3) accepting user-defined variable names and data types (numeric or character), (4) identifying the starting row for the first observation, if not specified, (5) enabling the use of SAS formats and informats to define data fields, ( 6) changing cell’s format to 'general' to import the original, unformatted data, (7) transforming out-of-range single cell(s) to be field(s) in a SAS dataset, and (8) uploading the converted SAS data to other platforms (e.g., UNIX) via SAS/CONNECT. The pa per, which is suitable for all SAS skill levels, discusses the methodology of %Qexcel design with SAS code, then shows example calls to the macro. |