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;
Can you provide an example spreadsheet?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: