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.
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
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
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.