SAS export subset of column to worksheet with a column name

Question

Given a SAS dataset with columns named n1,n2,..nN.

Is there a simple way to export common set of columns and unique subset of columns to a workbook, where each column is exported to the worksheet with the same name as the last column name?

Example:

For the SAS dataset above, the columns:

• n1, n2, n5 -> Worksheet n5
• n1, n2, n9 -> Worksheet n9
• n1, n2, n13 -> Worksheets n13

are exported to a Excel workbook, with worksheets named as above.

Appreciate any suggestions.

Answer 1

Use the SHEET= statement in a Proc EXPORT step.

For example:

filename myxl 'c:\temp\sandbox.xlsx';

proc export replace file=myxl dbms=excel 
  data=sashelp.class (keep=name)
;
  sheet='Name';
run;

proc export replace file=myxl dbms=excel
  data=sashelp.class (keep=name age weight) 
;
  sheet='Weight';
run;

A macro can be coded to generate repetitive parts

%macro excel_push (file=, data=, always=, each=);
  %local i n var;
  %let n = %sysfunc(countw(&each));
  %do i = 1 %to &n;
    %let var = %scan(&each,&i);

    proc export replace file=&file dbms=excel 
      data=&data(keep=&always &var)      
    ;
      sheet="&var";
    run;

  %end;

%mend;

options mprint;

filename myxl2 'c:\temp\sandbox2.xlsx';

%excel_push (
  file=myxl2,
  data=sashelp.class,
  always=name age sex,
  each=height weight
)

If you open the Excel output, leave it open and rerun the code, you will get an error, albeit slightly obscure:

ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: The Microsoft Access database
       engine could not find the object ********. Make sure the object exists and that you spell
       its name and the path name correctly. If ******** is not a local object, check your
       network connection or contact the server administrator..

Answer 2

I guess, What I understood from your question is how to have sheet name with last variable of SAS dataset. One way to do this is to use dictionary.columns and find which column position (varnum in dictionary.columns)is max in a dataset which will give the last variable and you can make a macro variable out of this and use this for sheet in proc export.

/* pick up the last variable*/
 proc sql ;
select name into :mysheet TRIMMED from dictionary.columns
where libname = "SASHELP"
and memname = "CLASS"
and  varnum = (select max(varnum) from dictionary.columns
             where libname = "SASHELP"
            and memname = "CLASS");

/* use the macrovariable in your sheet statement*/

 PROC EXPORT DATA= Sashelp.Class  /*Sheet 1*/
     outfile= "/folders/myfolders/class.xlsx "
     dbms=xlsx replace;
     sheet="&mysheet";
run;