with xlsx engine, couldn't get all variables

[su35]

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

[Kurt_Bremser]

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

[ballardw]

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.

[RichardDeVen]

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)

[su35 · Accepted solution]

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