topic Re: PROC Import - why is it creating an automatic variable name? in SAS Programming

PROC Import - why is it creating an automatic variable name?

dbjosiah — Tue, 15 Jun 2021 00:19:19 GMT

Hi, I'm importing an Excel dataset with 53 variables. PROC Import is renaming 7 of the variables as Varx, with x based on their original column number. For example, two of the Excel column names (quotes added) are "Resource(s) needed?" and "Department (Regarding) (Individual)." I understand that Import renames "Resource(s) needed?" to VAR15 because of the question mark, but why does it rename "Department..." to VAR18? All of the long names are truncated at 32 characters but not necessarily changed and there aren't any other fields that when truncated would be identical. The log says (sample):

NOTE: Variable Name Change. Resource(s) needed? -> VAR18

NOTE: Variable Name Change. Department (Regarding) (Individu -> VAR23

NOTE: Variable Name Change. Reporting Organization (Regardin -> Reporting_Organization__Regardin

The issue is that I need to automate the program for others to use. I can leave instructions to remove question marks and other such ("national characters"?) from Excel before importing, but I can't figure out what's wrong with "Department (Regarding... ." I just rename the variables after import but don't want the others to have to do that, and I can't guarantee that the columns will always be in the same order and thus the "x" suffix may change arbitrarily.

Thanks for any suggestions!

Re: PROC Import - why is it creating an automatic variable name?

tarheel13 — Tue, 15 Jun 2021 00:40:36 GMT

Did you try any system options like validvarname?

Re: PROC Import - why is it creating an automatic variable name?

dbjosiah — Tue, 15 Jun 2021 00:46:29 GMT

I saw "=ANY" but it seemed as though the restrictions were too tight. But I'll try it.

Re: PROC Import - why is it creating an automatic variable name?

tarheel13 — Tue, 15 Jun 2021 00:48:27 GMT

Okay, I’m curious if you find a position because I ran into this before and I also ended up renaming them but I agree with you that it seems very manual.

Re: PROC Import - why is it creating an automatic variable name?

dbjosiah — Tue, 15 Jun 2021 00:52:54 GMT

OK, I just tried that and it kind of worked, but I would have to refer to (e.g.,) "Resource(s) needed?"n each time which is pretty onerous. Just seems like there should be something I'm missing about the ones with no obvious special characters.

Re: PROC Import - why is it creating an automatic variable name?

jimbarbour — Tue, 15 Jun 2021 01:13:24 GMT

Yeah, that's no good. You can't rely on VARx because x will vary for a given column.

OPTIONS VALIDVARNAME=ANY; may fix it for you, but sometimes name literals are a pain to work with; you have to put the column names in quotes followed by an "n", for example: Department (Regarding) (Individual) would be 'Department (Regarding) (Individual)'n. Try it. That should be quick and repeatable so long as it doesn't cause some kind of problem downstream. Downstream users would have to also use "ANY" as the valid var name. Hopefully that's no big deal.

If that becomes a hassle, you could try using the XLSX engine on a Libname and reading the Excel spreadsheet that way. You have to have the "SAS/ACCESS Interface to PC Files" product licensed You can run the below to see if you've got it.

PROC SETININT;
RUN;

If you read the Excel file via a Libname, the renaming may be different. It might just do the same re-naming, but I suspect that it may be a little more sophisticated since it's a relatively new product.

If you use a libname with the XLSX engine, it would be something like this:

LIBNAME My_Excel  XLSX "&path\My_Excel_Filename.xlsx";

Path is just the path on your system of the Excel file.

Then in your Data step (or Proc), you would use the sheet name as though it were a SAS dataset name. Usually the default for the first sheet name in Excel is "Sheet1." If we use Sheet1, it would look like this:

DATA  sas_copy_of_excel_file;
    SET My_Excel.Sheet1;
RUN;

Can't hurt to try.

Jim

Re: PROC Import - why is it creating an automatic variable name?

SASKiwi — Tue, 15 Jun 2021 01:13:57 GMT

Please post your PROC IMPORT SAS log including your source code. It's hard to offer advice when we can't see what options you are using.

Re: PROC Import - why is it creating an automatic variable name?

Tom — Tue, 15 Jun 2021 01:45:26 GMT

What type of Excel file are you reading? Modern XLSX format? Older XLS format? Or are you instead not reading an Excel file at all?

In general the trick is to read the first row as DATA and use that data to issue the RENAME command to change the names that PROC IMPORT (or XLSX libname engine) generated for the variables.

proc import datafile='myfile.xslx' out=want dbms=xlsx;
  getnames=yes;
run;
proc import datafile='myfile.xslx' out=nonames dbms=xlsx;
  getnames=no;
run;
proc transpose data=want(obs=0) out=names;
  var _all_;
proc transpose data=nonames(obs=1) out=rawnames ;
   var _all_;
run;
data names ;
  set names ;
  set rawnames(keep=col1 rename=(col1=rawname));
  length newname $32 ;
  newname=translate(strip(combl(translate(rawname,' ',compress(rawname,'_','kad')))),'_',' ');
run;
proc sql noprint;
  select catx('=',nliteral(_name_),newname) into :renames separated by ' '
  from names
  where upcase(_name_) ne upcase(newname)
  ;
quit;
proc datasets lib=work nolist ;
  modify want ;
    rename &renames;
  run;
quit;

Re: PROC Import - why is it creating an automatic variable name?

Kurt_Bremser — Tue, 15 Jun 2021 07:43:50 GMT

You want to take control of your import process, which means you need to take the Excel file format out of it.

Save the spreadsheet to a CSV file, and read that with a DATA step, which allows you to set reasonable and valid variable names, set the variable types and other attributes correctly, and deal with aberrations in the data (e.g. "N/A" in an otherwise numeric column) as necessary.

Re: PROC Import - why is it creating an automatic variable name?

ballardw — Tue, 15 Jun 2021 08:43:01 GMT

@dbjosiah wrote:

Hi, I'm importing an Excel dataset with 53 variables. PROC Import is renaming 7 of the variables as Varx, with x based on their original column number. For example, two of the Excel column names (quotes added) are "Resource(s) needed?" and "Department (Regarding) (Individual)." I understand that Import renames "Resource(s) needed?" to VAR15 because of the question mark, but why does it rename "Department..." to VAR18? All of the long names are truncated at 32 characters but not necessarily changed and there aren't any other fields that when truncated would be identical. The log says (sample):

NOTE: Variable Name Change. Resource(s) needed? -> VAR18

NOTE: Variable Name Change. Department (Regarding) (Individu -> VAR23

NOTE: Variable Name Change. Reporting Organization (Regardin -> Reporting_Organization__Regardin

The issue is that I need to automate the program for others to use. I can leave instructions to remove question marks and other such ("national characters"?) from Excel before importing, but I can't figure out what's wrong with "Department (Regarding... ." I just rename the variables after import but don't want the others to have to do that, and I can't guarantee that the columns will always be in the same order and thus the "x" suffix may change arbitrarily.

Thanks for any suggestions!

Proc import "likes" data that has

1) exactly one row of header values

2) data that starts in rows immediately after the headers

3) columns align nicely.

Things that people do in Excel files frequently that make Proc Import behave other than expected:

1) have headers in more than one row

2) have headers that span more than one column

3) have hidden columns

4) have headers that have mixed vertical cell alignment, some merged, some not

5) have hidden rows

6) have data in merged cells

7) duplicate column headings

😎 have column headers that exceed 32 characters and are exactly the same (except possibly for case) for the first 32 characters.

9) Use characters that do not play well in SAS variable names, i.e. anything other than letter, digit or _ characters.

Any of the first 4 have the possibility of having "Varn" , where N is a column number, variable names created.

8 appears to be likely in your case. SAS data sets can only have 32 characters for variables names. If the "duplicate" column headers are less than 32 characters SAS will append a column number but when the full text is exactly the same that isn't possible. so it names the column based on the column number: Varn.

I have received Excel "data" with as many as 20 column headings of Total in a single file. So Import generates Total, total8 , total15, total18, total21 ...

Has anyone promised you that the file structure will never ever change? Then you may be able to use either the LIBNAME EXCEL to bring in data but you will need to write data step code to make things nice and likely parse data if you want to have a chance at "automating" anything because Proc Import will make guesses for every file and depending on the values in the data may have changing types and lengths.

Re: PROC Import - why is it creating an automatic variable name?

dbjosiah — Tue, 15 Jun 2021 15:36:44 GMT

Thanks everyone. I'm guessing from the array of answers that there's no easy fix here. In response to your questions:

1. SASKiwi: Just simple PROC IMPORT code:

(ps - we visited family in NZ last year; what a wonderful country!)

2. jimbarbour: This PROC setinint output doesn't seem to include your option:

3. Tom & Ballardw: I'm using a regular .xlsx file with all the normal conventions listed by Ballardw. I currently do the renames in the next data step. One of the issues is that the file structure *is* likely to change at some unforeseen and unannounced point (out of my control).

4. KurtBremser and Irackley: As an addition to my note above about cumbersome literal name changes, your solutions are similar and if no one has a simple fix that's probably what I'll do. The original names shouldn't change, so I can use the validvarname=any option, then rename the renames (e.g., "Resource(s) needed?"n) in the first data step.

I do wish, though, someone could explain why it can't deal with "Reporting Organization (Regardin"...

Re: PROC Import - why is it creating an automatic variable name?

jimbarbour — Tue, 15 Jun 2021 16:03:03 GMT

I do wish, though, someone could explain why it can't deal with "Reporting Organization (Regardin"...

Let's try something if you have a moment:

Create a copy of your Excel file. We only need a few rows, so it shouldn't take up a lot of space.
Manually remove all special characters from the column title, in particular, the parentheses. You can leave underscores; those are valid in SAS var names. Then, delete each space in the column title and then re add the space with the space bar on your keyboard. This should remove any possible non-display characters that might somehow have gotten in there.
Run a proc import against the manually renamed copy of Excel.

Let's see what the names are after doing the above. I think it might be the parentheses or a non-display character that is causing this behavior.

This PROC setinint output doesn't seem to include your option:

Actually, I'm looking for the log output that is just after that. It should look something like this:

This will tell you what is installed on your particular system. The sixth one down is the one I'm asking about, "SAS/ACCESS Interface to PC Files." Do you have that on your system?

Jim

Re: PROC Import - why is it creating an automatic variable name?

ballardw — Tue, 15 Jun 2021 16:04:31 GMT

@dbjosiah wrote:

Thanks everyone. I'm guessing from the array of answers that there's no easy fix here. In response to your questions:

3. Tom & Ballardw: I'm using a regular .xlsx file with all the normal conventions listed by Ballardw. I currently do the renames in the next data step. One of the issues is that the file structure *is* likely to change at some unforeseen and unannounced point (out of my control).

If a file structure is going to change, especially without warning Proc Import is not to be recommended in any way, at least IMHO. I have some such data sources. I use a data step to read a CSV version because then when I specify INFORMATS for variables that don't match the expected values I get invalid data messages and I can then work with the file and data step code to read the new layout.

Some of these sources just randomly switch order of columns in the Excel. Sometimes they manage to set variables like Income as Dates, or account identifiers and dates as Currency and sometimes just for some rows of the file.

EXCEL is a crappy data interchange file format because it places no restrictions on what you can place where and then you have to clean up other's poor decisions.

One of the advantages of a data step is you get to set the variable names a do not rely on the actual text in the file. Then you can assign labels to them that can use much more text to describe the variables when used by most procedures.

Re: PROC Import - why is it creating an automatic variable name?

Tom — Tue, 15 Jun 2021 16:24:05 GMT

I do not have any trouble reading that name. Here is test program.

filename out temp;
data test;
  name='Reporting Organization (Regarding';
  output;
  name='123';
  output;
run;

proc export data=test replace dbms=xlsx file=out;
  putnames=no;
run;

proc import datafile=out dbms=xlsx out=test2 replace;
run;

Please share an example of your XLSX file that is having trouble with the names. You can remove the data lines (or just replace them with a single line of dummy data).

Re: PROC Import - why is it creating an automatic variable name?

dbjosiah — Tue, 15 Jun 2021 18:43:42 GMT

OK, lots of things here. I did miss that part of the output and the “…Interface to PC” line is present. I've attached a dummy file which is a straight save-copy of the original, with 1) special characters removed, 2) hard stops (“spaces”) replaced manually, 3) extraneous columns removed, and 4) some obvious changes to the actual data. In this file, the first 8 columns are the problematic fields, the others read in without problem. Row 7 (red font) has the original variable names, just for your info.

I also looked more closely at the original log, it did say: “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.” When I did that, there were a bunch of Notes although I don’t know if they’re relevant; they cover both problematic and non-problem variables:

NOTE: VARCHAR data type is not supported by the V9 engine. Variable VAR3 has been converted to CHAR data type.

NOTE: VARCHAR data type is not supported by the V9 engine. Variable Status_Reason has been converted to CHAR data type.

Then I ran the dummy file and didn’t have any problems. So I tried another dummy but not taking the hard stops out (which would be more work for the 2nd crew). And that one also worked but… it accepted the field with a question mark that it had originally renamed to Var53. So I’m really not sure what’s going on there.

Finally, the Excel file I use is a value-paste copy of the original, which does have some hidden code that I don’t know any other way to get rid of (the Community helped me figure that one out!).

It’s looking like the simplest answer is going to be KurtBremser and Irackley’s approach of using validvarname=any and coding in the renames.

Re: PROC Import - why is it creating an automatic variable name?

dbjosiah — Tue, 15 Jun 2021 18:44:59 GMT

NOTE: VARCHAR data type is not supported by the V9 engine. Variable VAR3 has been converted to CHAR data type.

NOTE: VARCHAR data type is not supported by the V9 engine. Variable Status_Reason has been converted to CHAR data type.

Finally, the Excel file I use is a value-paste copy of the original, which does have some hidden code that I don’t know any other way to get rid of (the Community helped me figure that one out!).

It’s looking like the simplest answer is going to be KurtBremser and Irackley’s approach of using validvarname=any and coding in the renames.

Re: PROC Import - why is it creating an automatic variable name?

Kurt_Bremser — Tue, 15 Jun 2021 19:22:35 GMT

Even with VALIDVARNAME=ANY, the technical limit of 32 for the length of SAS variable names is still there. Save to csv, read with a data step, use sensible variable names, and keep the original Excel column names as labels.

Re: PROC Import - why is it creating an automatic variable name?

jimbarbour — Tue, 15 Jun 2021 19:35:41 GMT

Yes! Always look at the log. Always.

It's usually not a problem to have these VARCHAR to CHAR messages. That's normally a very safe conversion which why you don't even get those messages unless you have the message level to I.

That's very interesting that the variable names are far more reasonable (if am I understanding what you're saying correctly) if you make a copy but not with the original. That sure sounds like a non-display character in the column name.

Since you have the PC Files access product, you could set up a Libname and see if the XLSX engine is more sophisticated than Proc Import. I imagine it is more sophisticated, but I'm not sure how it will handle this specific instance. Worth a try.

I think all it would entail is basically what I've coded below. You'd have to replace the Sheet_Name with the actual sheet name of the sheet inside the Excel file that you are using. You'd also have to change my example path and file names to real ones. This should at least tell you what the column names in SAS will be.

VALIDVARNAME	=	V7;

%LET	Sheet_Name	=	Sheet1;	**  Default sheet name.  Replace with actual sheet name  **;

LIBNAME	Excel_In	XLSX	'C:\some_folder\Excel_sub_folder\The_Excel_File.xlsx';
LIBNAME	SAS_Out				'C:\some_folder\SAS_sub_folder\';

**------------------------------------------------------------------------------**;

DATA	SAS_Out.&Sheet_Name;
	SET	Excel_In.&Sheet_Name;
RUN;

Jim

Re: PROC Import - why is it creating an automatic variable name?

Tom — Tue, 15 Jun 2021 20:48:47 GMT

Ignore the data type messages. That is just PROC IMPORT being stupid. SAS only supports two data types. Fixed length character strings and floating point numbers. So PROC IMPORT is saying that it is internally treating the cells in the XSLX file as variable length strings and is saying it cannot create variable length string variables in a SAS dataset. Why it feels a need to tell you that makes no sense at all.

I do not see any issue with your cleaned up first row when reading into SAS dataset.

4     proc import datafile="&path/&fname" dbms=xlsx out=test1 replace;
5     run;

NOTE:    Variable Name Change.  Full Name Associated Case Indivi -> Full_Name_Associated_Case_Indivi
NOTE:    Variable Name Change.  Address3 State Province Associat -> Address3_State_Province_Associat
NOTE:    Variable Name Change.  Hand-off Outcome Marian -> Hand_off_Outcome_Marian
NOTE:    Variable Name Change.  Resources needed -> Resources_needed
NOTE:    Variable Name Change.  Department Regarding Individual -> Department_Regarding_Individual
NOTE:    Variable Name Change.  Marian ID -> Marian_ID
NOTE:    Variable Name Change.  Individual ID Regarding Individu -> Individual_ID_Regarding_Individu
NOTE:    Variable Name Change.  Part of Household Regarding Indi -> Part_of_Household_Regarding_Indi
NOTE:    Variable Name Change.  Diagnosis Date time Associated C -> Diagnosis_Date_time_Associated_C
NOTE:    Variable Name Change.  Activity Status -> Activity_Status
NOTE:    Variable Name Change.  Status Reason -> Status_Reason
NOTE:    Variable Name Change.  Date Created -> Date_Created
NOTE:    Variable Name Change.  Person is a minor -> Person_is_a_minor
NOTE:    Variable Name Change.  Reached Completed all attempts -> Reached_Completed_all_attempts
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 6 observations and 15 variables.
NOTE: WORK.TEST1 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

However your last row, which I take to be the actual column headers from your XLSX file are not handled as well.

13    proc import datafile="&path/&fname" dbms=xlsx out=test2 replace;
14      range='$A7:';
15    run;

NOTE:    Variable Name Change.   Full Name (Associated Case) (In -> VAR1
NOTE:    Variable Name Change.  Address3: State/Province (Associ -> VAR2
NOTE:    Variable Name Change.  Hand-off Outcome (Marian) -> VAR3
NOTE:    Variable Name Change.  Resource(s) needed? -> VAR4
NOTE:    Variable Name Change.  Department (Regarding) (Individu -> VAR5
NOTE:    Variable Name Change.  Marian ID -> Marian_ID
NOTE:    Variable Name Change.  Individual ID (Regarding) (Indiv -> VAR7
NOTE:    Variable Name Change.  Part of Household (Regarding) (I -> VAR8
NOTE:    Variable Name Change.  Diagnosis Date time (Associated -> Diagnosis_Date_time__Associated
NOTE:    Variable Name Change.  Activity Status -> Activity_Status
NOTE:    Variable Name Change.  Status Reason -> Status_Reason
NOTE:    Variable Name Change.  Date Created -> Date_Created
NOTE:    Variable Name Change.  Person is a minor -> Person_is_a_minor
NOTE:    Variable Name Change.  Reached / Completed all attempts -> Reached___Completed_all_attempts
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 0 observations and 15 variables.
NOTE: WORK.TEST2 data set was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

There are some patterns here. It does not like the parentheses. It seems to handle the / ok. Not sure about the colon. Or the leading space.

You could use VALIDVARNAME=ANY and then it does not seem to transform those names, but then they are really not very usable in code.

Obs    _NAME_                              _LABEL_

  1     Full Name (Associated Case) (In     Full Name (Associated Case) (Individual)
  2    Address3: State/Province (Associ    Address3: State/Province (Associated Case) (Individual)
  3    Hand-off Outcome (Marian)           Hand-off Outcome (Marian)
  4    Resource(s) needed?                 Resource(s) needed?
  5    Department (Regarding) (Individu    Department (Regarding) (Individual)
  6    Marian ID                           Marian ID
  7    Individual ID (Regarding) (Indiv    Individual ID (Regarding) (Individual)
  8    Part of Household (Regarding) (I    Part of Household (Regarding) (Individual)
  9    Diagnosis Date time (Associated     Diagnosis Date time (Associated Case) (Individual)
 10    Activity Status                     Activity Status
 11    Status Reason                       Status Reason
 12    Date Created                        Date Created
 13    Person is a minor                   Person is a minor
 14    Reached / Completed all attempts    Reached / Completed all attempts ?
 15    Birthdate                           Birthdate

You can read the data without the headers by starting in the first cell of the second row.

proc import datafile="&path/&fname" dbms=xlsx out=want replace;
  getnames=no;
  range='$A2:';
run;

If want to dynamically read the first row and use those strings as labels for the variables that is the easiest solution.

proc import datafile="&path/&fname" dbms=xlsx out=names_wide replace;
  getnames=no;
run;
proc transpose data=names_wide(obs=1) out=names ;
  var _all_;
run;
filename code temp;
data _null_;
  set names end=eof;
  if _n_=1 then put 'label';
  put @3 _name_ '=' col1 :$quote. ;
  if eof then put ';' ;
run;
proc datasets lib=work nolist;
  modify want;
%include code / source2;
  run;
quit;

You could make more compatible names from the strings in the first row than what PROC IMPORT does. For example by converting all of the special characters into underscores. Personally I like to first convert them to space and then use COMPBL() to collapse multiple spaces into a single space.

So either take the COL1 generated by transposing the first row or the _LABEL_ variable that PROC IMPORT will create when you use VALIDVARNAME=ANY and transform that into a new name for the variable that does not contain any special characters or spaces.

data clean_names;
  set names ;
  length clean_name $32 ;
  clean_name=translate(strip(compbl(translate(col1,' ',compress(col1,compress(col1,'_','kad'))))),'_',' ');
run;

Obs    _NAME_    _LABEL_    COL1                                                       clean_name

  1      A          A        Full Name (Associated Case) (Individual)                  Full_Name_Associated_Case_Indivi
  2      B          B       Address3: State/Province (Associated Case) (Individual)    Address3_State_Province_Associat
  3      C          C       Hand-off Outcome (Marian)                                  Hand_off_Outcome_Marian
  4      D          D       Resource(s) needed?                                        Resource_s_needed
  5      E          E       Department (Regarding) (Individual)                        Department_Regarding_Individual
  6      F          F       Marian ID                                                  Marian_ID
  7      G          G       Individual ID (Regarding) (Individual)                     Individual_ID_Regarding_Individu
  8      H          H       Part of Household (Regarding) (Individual)                 Part_of_Household_Regarding_Indi
  9      I          I       Diagnosis Date time (Associated Case) (Individual)         Diagnosis_Date_time_Associated_C
 10      J          J       Activity Status                                            Activity_Status
 11      K          K       Status Reason                                              Status_Reason
 12      L          L       Date Created                                               Date_Created
 13      M          M       Person is a minor                                          Person_is_a_minor
 14      N          N       Reached / Completed all attempts ?                         Reached_Completed_all_attempts
 15      O          O       Birthdate                                                  Birthdate

But personally if you really just have these 15 variables I would read the data without the names and use RENAME to change the default column names that are both reasonable short and sufficiently meaningful.

rename 
  A =Full_Name
  B =Address3
  C =Hand_off_Outcome
  D =Resources_needed
  E =Department
  F =Marian_ID
  G =Individual_ID
  H =Part_of_Household
  I =Diagnosis_Datetime
  J =Activity_Status
  K =Status_Reason
  L =Date_Created
  M =Minor
  N =Reached_Completed_all_attempts
  O =Birthdate
;

Re: PROC Import - why is it creating an automatic variable name?

dbjosiah — Wed, 16 Jun 2021 21:03:41 GMT

Thanks everyone. I meant to mention before that *I* did not come up with those original variable names... I'll go the csv route and use the validvarname=any option.

Update: that solution is working just fine, without validvarname. I had to add GUESSINGROWS=MAX, and because the csv import didn't create labels like the xlsx import I found this neat little renaming code: PROC DATASETS; modify dsnname; attrib _all_ label='Original variable'; run; quit;. Then I can add better labels to the ones I care about, which I had to do before anyway. So I'll mark this discussion as Solved.