LIBNAME XLSX Options on SAS Grid

gewing — Tue, 31 Jan 2017 15:57:49 GMT

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;

Re: LIBNAME XLSX Options on SAS Grid

dgritt — Tue, 03 Apr 2018 15:53:20 GMT

Can you provide an example spreadsheet?

Re: LIBNAME XLSX Options on SAS Grid

ChrisNZ — Wed, 04 Apr 2018 04:19:46 GMT

Not too sure about the libname, but for sql you could do:

proc sql;

create table CHGS as

select ID length=11 label=' ', QUESTID length=7 label=' '

%LET index = 1;
%LET newVar = %SCAN(&newVars., &index.);
%DO %WHILE ("&newVar." ~= "");
,&newVar. label=' '
%LET index = %EVAL(&index. + 1);
%LET newVar = %SCAN(&newVars., &index.);
%END;

from edited."&sheet."n
order by &sort;

quit;

topic LIBNAME XLSX Options on SAS Grid in SAS Programming

LIBNAME XLSX Options on SAS Grid

Re: LIBNAME XLSX Options on SAS Grid

Re: LIBNAME XLSX Options on SAS Grid