topic with xlsx engine, couldn't get all variables in SAS Programming

with xlsx engine, couldn't get all variables

su35 — Wed, 26 Aug 2020 14:21:17 GMT

Hi, there

When I use the libref with xlsx engine to access the data, I couldn't get all the variables.

x "&pout.vardict.xlsx";

If I access the data

libname templib xlsx "&pout.vardict.xlsx";
proc sql;
    select name
    from dictionary.columns 
    where libname="TEMPLIB";
quit;

the variable description was disappeared

Although I can use other approaches to read the data, I want to know what is wrong.

Thanks

Re: with xlsx engine, couldn't get all variables

Kurt_Bremser — Wed, 26 Aug 2020 15:08:35 GMT

What is contained in the macro variable &pout, and what is the actual path to the Excel file?

Re: with xlsx engine, couldn't get all variables

ballardw — Wed, 26 Aug 2020 16:41:10 GMT

What is your setting for VALIDVARNAME?

Does the source file have any merged cells in the column header? Hidden columns?

I am not sure how well the dictionary tables work with some of the stuff that appears in Excel. You may need to copy the sheet to an actual SAS data set to get a better idea of the contents.

Re: with xlsx engine, couldn't get all variables

RichardDeVen — Wed, 26 Aug 2020 17:06:36 GMT

What version of SAS are you using ?

I find your assertion is incorrect for my installation.

This example demonstrates creating a worksheet that has a column named "description", and when read via the XLSX engine the description column is present in the metadata table DICTIONARY.COLUMNS result set.

data have;
  length variable $32 type 8 class $8 length apply id 8 description $200;
  input (variable--description) (&);
datalines;
fubar  1  magic  2  3  4  This is fubar check  
run;

proc export data=have dbms=xlsx replace file='c:\temp\mighty-data.xlsx';
run;

libname checkxl xlsx 'c:\temp\mighty-data.xlsx'; 


ods html file='xlsx-lib-meta.html' style=plateau;
proc sql;
  select * from dictionary.columns
  where libname='CHECKXL'
  ;
ods html close;

proc sql;


libname checkxl;

%sysexec start "auto launch" "c:\temp\mighty-data.xlsx";

Perhaps your description column is empty in a way that prevents the engine from processing it (i.e. consider the speculation that the engine thinks your description column is width 0 and discards it)

Re: with xlsx engine, couldn't get all variables

su35 — Wed, 26 Aug 2020 18:30:10 GMT

Thanks replies,
According to ballardw's remending, I save the xlsx file to csv file, and re-save the csv file to xlsx file. Then the problem gone