& in a xlsx file upload in SAS become &

[CaroleBPRI]

Hello SAS community,

When I import a xlsx file with SAS 9.4, I 've got an issue with a variable name in a column.

'EQ & PPR' in the xlsx file become 'EQ & PPR' in the SAS table imported.

Is it a bug from SAS? Are there any other misread special characters?

Thank you very much.

[Tom]

How was the source file created?

If I make an XLSX with SAS I do not have that problem.

data test;
  infile cards dsd truncover;
  input (x1-x3) (:$20.);
cards;
id,EQ & PR,x
1,2,3
4,5,6
;

filename xlsx temp;
proc export data=test file=xlsx dbms=xlsx ;
  putnames=NO;
run;

options validvarname=v7;
proc import file=xlsx dbms=xlsx out=test2 replace;
run;

proc contents data=test2 varnum;
run;

                 Variables in Creation Order

#    Variable    Type    Len    Format    Informat    Label

1    id          Char      1    $1.       $1.         id
2    EQ___PR     Char      1    $1.       $1.         EQ & PR
3    x           Char      1    $1.       $1.         x

[CaroleBPRI]

This is my original Code to import the xlsx file :

proc import out=lst_pci
datafile="V:\3613_DEPT_CONTROLE_DE_GESTION\Transverse\ANAPLAN\TBB Clientèle - Eléments hors SI.xlsx"
dbms=XLSX replace;
Sheet='REF PCI';
run;

[Tom]

You could probably open a support ticket with SAS to have them actually look at that file and see what is happening.

If you just want to change the variable names to replace HTML code like & with its decode should be able to just use the HTMLDECODE() function.

Here is an example:

options validvarname=any ;
data lst_pci ;
  length id name 'EQ & PPR'n 8;
run;

proc sql noprint;
select catx('=',nliteral(name),nliteral(htmldecode(name))) 
  into :renames separated by ' '
  from dictionary.columns
  where libname='WORK'
    and memname='LST_PCI'
    and name ne htmldecode(name)
;
quit;

proc datasets nolist lib=work;
  modify lst_pci;
  rename &renames ;
  run;
quit;

proc contents data=lst_pci varnum;
run;

 Variables in Creation Order

#    Variable    Type    Len

1    id          Num       8
2    name        Num       8
3    EQ & PPR    Num       8

[Quentin]

If you run PROC CONTENTS on the data, what do you see for the variable name?

I can't tell where the image comes from (is it a query builder?).  I"m wondering if whatever tool that is generating the image is the problem, not the dataset itself.

[CaroleBPRI]

Ok I will test this point. Is there still a misread when the table is exported. Thank you