Many, many thanks to PGStats, who gave me the inspiration with the catx() above.
I am using this macro as part of a 'canned' water quality retrieval from our main Oracle database. It is run in the early morning by batch jobs, and creates SAS datasets and exports subsets to Excel. Macro variables used in the retrieval determine the locations, WQ parameters of interest, etc. and the current number of columns varies from 100 to 400 or so, depending on what is requested.
Currently, PROC EXPORT to Excel is limited to 255 columns, and the %split255 macro will split the output over as many tabs as needed in the same workbook. Also, Excel does not accept column headers containing a dot. Thus, the need to translate labels. I also wanted to add and use row number as a column across tabs in the workbook, because the selections may not all have a common set of columns.
I'm sure this can be done better - I had to add ROW to the second LABEL statement to account for cases where none of the original labels contained dots. Originally, I did not have the WHERE restriction in the SQL for the labels, but it was much slower, and I did not want to hit the limit on macro variable length. I tinkered with a count of the labels containing . and some %if %then, but couldn't quite get it to work.
Thanks again for the help, and any suggestions for improvements would be very welcome!
Wendy T
My additions to the %split255 macro:
I set some of the macro variables in the original to values I will typically use.
Locally define two macro variables (and set to null):
&ORLABEL to hold the original label values with .
&RELABEL to hold the transformed label values with #
Set the dataset to itself, and generate the row number with _N_ as ROW to use as a common column on all sheets, and LABEL ROW and &RELABEL to get # labels for export to Excel.
Then run the original %split255 macro code with the LABEL option added to PROC EXPORT.
At the end of the original code, set the dataset to itself again, LABEL ROW and &ORLABEL to return . to the labels. Then drop ROW to have the original dataset again.
----------------
%macro split255(mylibref=,sasdsname=,tempds=,xlsname=,sheetname=,deleteit=,varcom=,comname=);
*set some of the macro variable values *;
%LET tempdir=D:\SASData\&SYSUSERID ;
%LET tempds=subgroup ;
%LET sheetname=datasheet ;
*define local macro variables for original and transformed labels*;
%LOCAL RELABEL ORLABEL ;
%LET RELABEL= ;
%LET ORLABEL= ;
*obtain values for original and transformed labels*;
proc sql noprint;
select catx(" ", NAME, "=", quote(trim(label)))
into :orlabel separated by " "
from dictionary.columns
where libname=upcase("&mylibref.") and memname=upcase("&sasdsname.") AND LABEL CONTAINS '.' ;
%PUT &ORLABEL ;
select catx(" ", NAME, "=", quote(translate(trim(label),"#",".")))
into :relabel separated by " "
from dictionary.columns
where libname=upcase("&mylibref.") and memname=upcase("&sasdsname.") AND LABEL CONTAINS '.' ;
%PUT &RELABEL ;
quit ;
RUN ;
*set dataset to itself generate row number and add ROW label and relabel if needed* ;
DATA &mylibref..&sasdsname ; SET &mylibref..&sasdsname ;
ROW=_N_ ;
LABEL ROW='Row in SAS dataset' &RELABEL ;
RUN ;
*begin original macro code*;
proc sql;
create table testcols as
select * from dictionary.columns
where libname=upcase("&mylibref") and memname=upcase("&sasdsname");
run;
****** etc********
%end;
%mend loopit;
%loopit;
*end of original macro code ;
*set dataset back to itself then label ROW and back to original label(s) containing . *;
*label for ROW is only present to placehold if &ORLABEL is null* ;
*drop ROW to get original dataset* ;
DATA &mylibref..&sasdsname ; SET &mylibref..&sasdsname ;
LABEL ROW='Row in SAS dataset' &ORLABEL ;
DROP ROW ;
RUN ;
%mend split255;
then I use
%split255(mylibref=&DATA_LIB, sasdsname=&DSN_OVERALL_MATRIX, xlsname=&OUTDIR\&DSN_OVERALL_MATRIX..xlsx, deleteit=destroy, varcom=1, comname=ROW );
to use ROW as the common column on the individual tabs.
