topic Re: Why is SAS 9.4 converting variable to VAR8? in SAS Programming

Why is SAS 9.4 converting variable to VAR8?

inquistive — Wed, 27 Jul 2022 15:57:04 GMT

Hi experts,

I have imported an excel file and the log prints this:

453 proc import out= info
454 datafile= "H:\Details.xls"
455 dbms = xls replace;
456 getnames= yes;
457 run;

NOTE: Variable Name Change. student first name -> student_first__name
NOTE: Variable Name Change. student last name -> student_last_name
NOTE: Variable Name Change. stream code -> stream_code
NOTE: Variable Name Change. gender code -> gender_code
NOTE: Variable Name Change. status code -> status_code
NOTE: Variable Name Change. online or/onsite -> online_or_onsite
NOTE: Variable Name Change. permit to work -> permit_to_work
NOTE: Variable Name Change. std code AM/AS/AF/EU/SA/GF -> VAR8
NOTE: Variable Name Change. comments
work permit applicabl -> comments___work_permit_applicabl
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 193 observations and 9 variables.
NOTE: WORK.INFO data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds

Could you please help me understand why SAS is converting 'std code AM/AS/AF/EU/SA/GF' variable to 'VAR8'?How can I force SAS not to do this?

Re: Why is SAS 9.4 converting variable to VAR8?

ballardw — Wed, 27 Jul 2022 16:25:30 GMT

Save XLS or XLSX to CSV, write a data step to read that format.

You don't have much control over Proc Import. The internal rules in some cases appear to be mystifying at times.

I have seen similar behavior when there were two or more variables that exceeded 32 characters and the column headings were identical for the first 32 characters.

By any chance is that column something like a check box or other than standard numeric or character values?

Re: Why is SAS 9.4 converting variable to VAR8?

RichardDeVen — Wed, 27 Jul 2022 16:26:44 GMT

When an Excel file (XLS) column header contains three or more /'s (or special characters I suppose) the SAS Excel parser 'gives up' and just names the imported column VAR<N>. The variables labels will maintain the original value of the header cell.

You can import the columns as-is by using

OPTION VALIDVARNAME=ANY;

before the PROC IMPORT.

Handwriting code for such an imported data set will be aggravating because you will need to reference variables with name-literals. For example:

'std code AM/AS/AF/EU/SA/GF'N

If you use only Enterprise Guide or some other UI client, name-literals will be less of an issue.

Re: Why is SAS 9.4 converting variable to VAR8?

inquistive — Thu, 28 Jul 2022 20:41:29 GMT

It's a standard character variable.

Thanks,

Re: Why is SAS 9.4 converting variable to VAR8?

Tom — Fri, 29 Jul 2022 19:45:03 GMT

That is some decision that PROC IMPORT is making that you have no control over.

You can always fix it yourself after the fact. Here is an example for how to implement the algorithm used by https://github.com/sasutils/macros/blob/master/csv2ds.sas to create unique standard SAS names from column headers on the results of PROC IMPORT of an Excel file.

Since you didn't provide any example input I made up one:

proc import file='c:\downloads\test1.xlsx' dbms=xlsx
  out=xlsx replace
;
run;

proc transpose data=xlsx(obs=0) out=names;
  var _all_;
run;

data fixed;
  length varnum 8 name upcase $32 suffix 8 label $256 _name_ $32 _label_ $256 ;
  set names ;
  varnum+1;
  label=compbl(translate(_label_,'   ','090A0D'x));
  if _n_=1 then do;
    declare hash h ();
    h.definekey('upcase','suffix');
    h.definedone();
  end;
  upcase=cats('VAR',varnum);
  if nvalid(coalescec(name,label,upcase)) then name=coalescec(name,label,upcase);
  else do;
* Replace adjacent non-valid characters with single underscore ;
    name=translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,coalescec(name,label,upcase))),' _','_ ');
    name=prxchange('s/(^[0-9])/_$1/',1,name);
  end;
  upcase=upcase(name);
  do suffix=0 to 1E4 while( h.add()
     or (suffix and not h.check(key:substrn(catx('_',upcase,suffix),1,32),key:0)))
  ;
     upcase=substr(upcase,1,32-length(cats(suffix))-1);
  end;
  if suffix then substr(name,lengthn(upcase)+1)=cats('_',suffix);
  if name=label then label=' ';
run;

proc sql noprint;
  select case when (upcase(name)=upcase(_name_)) then ' '
         else catx('=',nliteral(_name_),nliteral(name)) end
       , catx('=',nliteral(_name_),quote(trim(label),"'"))
    into :rename separated by ' ' 
       , :label separated by ' '
    from fixed
  ;
%let nrename=&sqlobs;
quit;

proc datasets nolist lib=work;
  modify xlsx ;
    label &label;
    rename &rename;
  run;
quit;

proc contents data=xlsx varnum;
run;

                                   Variables in Creation Order

#    Variable                           Type    Len    Format    Label

1    student_first_name                 Num       8    BEST.     student first name
2    student_last_name                  Num       8    BEST.     student last name
3    stream_code                        Num       8    BEST.     stream code
4    gender_code                        Num       8    BEST.     gender code
5    status_code                        Num       8    BEST.     status code
6    online_or_onsite                   Num       8    BEST.     online or/onsite
7    permit_to_work                     Num       8    BEST.     permit to work
8    std_code_AM_AS_AF_EU_SA_GF         Num       8    BEST.     std code AM/AS/AF/EU/SA/GF
9    comments_work_permit_applicable    Num       8    BEST.     comments work permit applicable

Re: Why is SAS 9.4 converting variable to VAR8?

Kurt_Bremser — Fri, 29 Jul 2022 06:44:55 GMT

Concur with @ballardw . Save to a text file, and read it with a DATA step where you have control.