Proc import variable name mm/dd/yy

[hhchenfx]

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;

[art297]

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

[hhchenfx]

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

[Patrick]

@hhchenfx 

Memname is always uppercase in the dictionary tables. 

...
memname eq 'CLIENT' and
...

[Tom · Accepted solution]

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.

[hhchenfx]

Thank you both for your help!

It works now.

HHCFX