SAS tips: converting the data between SAS data sets and Excel

Converting SAS data sets to Excel Book

 In clinical trials, the database may be stored in SAS data set format. Sometimes, there is a need to convert multiple SAS data sets into an excel book. The following program can be easily modified to serve this purpose. With the small macro using Proc Export, SAS data sets can be converted into Excel book with multiple tabs (each tab is corresponding to a SAS data set). 

libname aa "c:\Data\CRF Data\Final Data\";

%macro export(dst=);
PROC EXPORT DATA= aa.&dst

            OUTFILE= "c:\Data\CRF Data\Final Data\excel\ExcelData.xls"

            LABEL DBMS=xls REPLACE;  

   SHEET="&dst";
RUN;
%mend;

%export(dst=IE);
%export(dst=DM);
%export(dst=MH);
%export(dst=VS);
%export(dst=PE);
%export(dst=CLAB);
%export(dst=PREG);
%export(dst=DRUG);
%export(dst=AE);
%export(dst=CM);
%export(dst=COM);

In the macro above, the keyword 'LABEL' is important. With 'LABEL', the SAS variable label will be used as the column header. Without 'LABEL', the SAS variable name will be used as the column header. The keyword 'LABEL' must be placed before the keyword 'DBMS' in order to be effective.
DBMS=xls indicates that the output data file will be an excel book (no version number is needed). other options for DBMS are csv,  dlm, tab, jmp. Check SAS manual for detail. 

If you run into an error due to the Excel version issue, you may try to use xlsx engine.

%macro export(dst=);
PROC EXPORT DATA= aa.&dst

            OUTFILE= "c:\Data\CRF Data\Final Data\excel\ExcelData.xlsx"

            LABEL DBMS=xlsx REPLACE; 

   SHEET="&dst";
RUN;
%mend;

Converting Excel Book to SAS data sets

The opposite way is to convert the Excel book (with multiple table) into different SAS data sets. The program below can be modified to fulfill this task.

libname aa "c:\temp\";

%macro import(dst=);
PROC IMPORT DATAFILE= "C:\Dengc\ExcelData.xls"  OUT= aa.&dst
            DBMS=xls REPLACE;
     SHEET="&dst";
     GETNAMES=YES;
RUN;
%mend;

%import(dst=primary);
%import(dst=secondary);
%import(dst= tertiary);

In the macro above, GETNAMES=YES indicates that the first row from excel spreadsheet will be used as the SAS variable name.


Other References:

• How do I read/write Excel files in SAS?
• SAS Proc Import
• SAS Proc Export

Switching from non-inferiority to superiority - is multiplicity adjustment needed?

For a non-inferiority trial, after the non-inferiority is shown, one will typically try to show the non-inferiority. People may argue that the multiplicity adjustment arise in this situation. This can be seen in a presentation of "Branching tests in clinical trials with multiple objectives" by Alex Dmitrienko and Brian Wiens. In their presentation, the multiplicity adjustment is considered for switching from non-inferiority test to superiority test as part of the gatekeeping methods. 

However, the regulatory guidelines clearly stated that no multiplicity adjustment is needed when interpreting a non-inferiority trial as a superiority trial. In EMA's guidance "Point to consider on switching between superiority and non-inferiority", the following statement is stated:

"if the 95% confidence interval for the treatment effect no only lies entirely above -delta but also above zero then there is evidence of superiority in terms of statistical significance at the 5% level (p<0.05). In this case it is acceptable to calculate the p-value associated with a test of superiority and to evaluate whether this is sufficiently small to reject convincingly the hypothesis of no difference. There is no multiplicity argument that affects this interpretation because, in statistical terms, it corresponds to a simple closed test procedure. Usually this demonstration of a benefit is sufficient on its own, provided the safety profiles of the new agent and the comparator are similar...."

In FDA's guidance "Non-inferiority clinical trials", similar statements are included:

"In some cases, a study planned as an NI study may show superiority to the active control. ICH E-9 and FDA policy has been that such a superiority finding arising in an NI study can be interpreted without adjustment for multiplicity. Showing superiority to an active control is very persuasive with respect to the effectiveness of the test drug, because demonstrating superiority to an active drug is much more difficult than showing superiority to placebo. Similarly, a finding of less than superiority, but with a 95% CI upper bound for C-T considerably smaller than M2, is also statistically persuasive."

 The multiplicity adjustment is now everywhere. It is good to know that there is no need to do the multiplicity adjustment in the situation of interpreting a non-inferiority study as a superiority.