On SAS Grid, LIBNAME EXCEL doesn't work, so I'm stuck using LIBNAME XSLX. There's one page on the SAS site that says that the XLSX engine has limited data set options as opposed to the EXCEL engine. I've inherited a program that uses the data set options DBSASTYPE to set a group of variables later defined in array as numeric and DBSASLABEL=none. I've tried using DBTYPE, and that also doesn't work. The error is 'Invalid option'. I can't find documentation ANYWHERE for the XLSX engine's available DATA step options, and I'd like to keep the program as close to the original, because it's quite elegant and is called by another program.
Does anyone have a workaround besides manually redoing everything? I've considered swapping the code for PROC SQL, but not 100% sure how I'd incorporate the code below. I don't want to do it variable by variable because I call this macro multiple times for sheets with different 'NEW_' variables.
libname edited XLSX "&file";
*Get names of new variables;
*Assuming new variables are named new_<oldvarname> in excel;
proc contents data=edited."&sheet."n out=varnames(keep=name) noprint; run;
data _null_;
length newvars oldvars $ 5000;
set varnames end=last;
retain newvars oldvars;
if upcase(scan(name,1,'_'))='NEW' then do;
oldvars=catx(' ',oldvars,scan(name,2,'_'));
newvars=catx(' ',newvars,name);
end;
if last then do;
call symput('NewVars',trim(newvars));
call symput('OldVars',trim(oldvars));
end;
run;
PROC SORT DATA=edited."&sheet."n(keep=id questid rostnumb new_: dbsaslabel=none dbsastype=(id='CHAR(11)' questid='CHAR(7)' *These options don't work.;
%LET index = 1;
%LET newVar = %SCAN(&newVars., &index.);
%DO %WHILE ("&newVar." ~= "");
&newVar.='NUMERIC'
%LET index = %EVAL(&index. + 1);
%LET newVar = %SCAN(&newVars., &index.);
%END;
))
out=chgs; BY &sort.;
RUN;
