topic Re: & in a xlsx file upload in SAS become & in SAS Programming

& in a xlsx file upload in SAS become &

CaroleBPRI — Mon, 17 Jul 2023 12:16:32 GMT

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.

Re: & in a xlsx file upload in SAS become &

Tom — Mon, 17 Jul 2023 12:35:16 GMT

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

Re: & in a xlsx file upload in SAS become &

CaroleBPRI — Mon, 17 Jul 2023 13:10:16 GMT

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;

Re: & in a xlsx file upload in SAS become &

Quentin — Mon, 17 Jul 2023 13:17:24 GMT

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.

Re: & in a xlsx file upload in SAS become &

Tom — Mon, 17 Jul 2023 13:22:30 GMT

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 &amp; 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

Re: & in a xlsx file upload in SAS become &

CaroleBPRI — Mon, 17 Jul 2023 15:23:33 GMT

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

topic Re: & in a xlsx file upload in SAS become &amp; in SAS Programming

& in a xlsx file upload in SAS become &

Re: & in a xlsx file upload in SAS become &

Re: & in a xlsx file upload in SAS become &

Re: & in a xlsx file upload in SAS become &

Re: & in a xlsx file upload in SAS become &

Re: & in a xlsx file upload in SAS become &

topic Re: & in a xlsx file upload in SAS become & in SAS Programming