Hi,
I import an xlsx file with column is day of month say, 12/1/2019 , 12/2/2019...
The SAS data then has variable name of _2_1_2019 , _2_2_2019.
So the first number (1) is gone.
Can you help me to fix it to make SAS variable name 12_1_2019?
Thank you.
HHCFX
proc import datafile="C:\Users\HPNEW\Dropbox\report_V1.xlsx"
out=client dbms=Excel replace;
sheet ='raw Data' ;
getnames=yes;
run;
What version of SAS are you using?
Let's make a sample XLSX file.
data have ;
input (v1-v4) (:$20.);
cards;
Name 12/01/2018 01/01/2019 02/01/2019
AAA 10 20 30
BBB 40 50 60
;
filename xlsx temp;
proc export data=have outfile=xlsx dbms=xlsx replace;
putnames=no;
run;
And read it with PROC IMPORT.
605 %put &=sysvlong ; SYSVLONG=9.04.01M5P091317 606 options validvarname=v7 ; 607 proc import dbms=xlsx datafile=xlsx out=want replace ; 608 getnames=yes; 609 run; NOTE: Variable Name Change. 12/01/2018 -> _12_01_2018 NOTE: Variable Name Change. 01/01/2019 -> _01_01_2019 NOTE: Variable Name Change. 02/01/2019 -> _02_01_2019 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. NOTE: The import data set has 2 observations and 4 variables.
Using SAS University Edition, 12/1/2019 imports as _12_1_2019. That, methinks, is reasonable since a normal SAS variable name can't start with a number, and can only include numbers, letters and the underscore character.
However, if your version of SAS loses the first digit, you could always use something like:
options validvarname=any;
proc import datafile="/folders/myfolders/test.xlsx"
out=have replace dbms=xlsx;
getnames=yes;
sheet='sheet1';
run;
proc sql noprint;
select catx('=',catt("'",name,"'n"),catt('_',translate(name,'_','/')))
into :varnames separated by ' '
from dictionary.columns
where libname eq 'WORK' and
memname eq 'HAVE' and
anydigit(substr(name,1,1))
;
quit;
data want;
set have (rename=(&varnames.));
run;
options validvarname=v7;
Art, CEO, AnalystFinder.com
Thank you for your help.
Somehow SAS return a notice for the SQL as below.
HHCFX
113 proc sql noprint;
114 select catx('=',catt("'",name,"'n"),catt('_',translate(name,'_','/')))
115 into :varnames separated by ' '
116 from dictionary.columns
117 where libname eq 'WORK' and
118 memname eq 'Client' and
119 anydigit(substr(name,1,1))
120 ;
NOTE: No rows were selected.
121 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
What version of SAS are you using?
Let's make a sample XLSX file.
data have ;
input (v1-v4) (:$20.);
cards;
Name 12/01/2018 01/01/2019 02/01/2019
AAA 10 20 30
BBB 40 50 60
;
filename xlsx temp;
proc export data=have outfile=xlsx dbms=xlsx replace;
putnames=no;
run;
And read it with PROC IMPORT.
605 %put &=sysvlong ; SYSVLONG=9.04.01M5P091317 606 options validvarname=v7 ; 607 proc import dbms=xlsx datafile=xlsx out=want replace ; 608 getnames=yes; 609 run; NOTE: Variable Name Change. 12/01/2018 -> _12_01_2018 NOTE: Variable Name Change. 01/01/2019 -> _01_01_2019 NOTE: Variable Name Change. 02/01/2019 -> _02_01_2019 NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. NOTE: The import data set has 2 observations and 4 variables.
Thank you both for your help!
It works now.
HHCFX
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.