topic Re: Error reading in column to be renamed in SAS Software for Learning Community

Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 14:14:54 GMT

Hi all,

I have a dataset with some complicated column names. Id like to rename these columns to follow SAS naming conventions and output into a new table.

All was going well, but now there are a bunch of (more complicated) column names that will not get read in.

data Master;
	set work.master
	WORK.TABLE1(rename=("#"n=Number REC=Record  Comm=Comments "Processed IN/OUT"n=Processed_in_out "Reading IN/OUT"n=Reading));
run;

the error states that the variables "Processed IN/OUT" and "Reading IN/OUT" are not on file WORK.TABLE1. So to confirm, I ran a code that outputs all of the column names in TABLE1, and copied/pasted the names of the columns so its exactly what is listed in the table. However it still doesn't work...

am I missing something?

Re: Error reading in column to be renamed

PaigeMiller — Wed, 01 Nov 2023 14:29:48 GMT

Please show us the ENTIRE log for this DATA step. We need the ENTIRE log for this data step, not just the errors. Please copy the log as text and paste it into the window that appears when you click on the </> icon.

Re: Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 14:37:55 GMT

26         data Master;
27         	set work.Master
28         	WORK.TABLE1(rename=("#"n=Number REC=Record Comm=Comments REC=Record "Processed IN/OUT"n=Processed_in_out));
ERROR: Variable 'Processed IN/OUT'n is not on file WORK.TABLE1.
ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.TABLE1.
29         
30         
31         run;

Here it is

Re: Error reading in column to be renamed

ballardw — Wed, 01 Nov 2023 14:51:45 GMT

Depending on exactly how you "looked" at the names you might be missing a leading space in the name.

If you ran proc contents or similar and looking in the results window a leading space is not shown in the table.

Trailing spaces don't seem to have this issue though.

Example:

options validvarname=any;

data junk;
  "name no leading space"n=3;
  " name with leading space"n=4;
  "name with trailing space "n=5;
run;

proc contents data=junk;
run;

The proc contents output:

Alphabetic List of Variables and Attributes
#	Variable	Type	Len
2	name with leading space	Num	8
1	name no leading space	Num	8
3	name with trailing space	Num	8

Note that "name with leading space does not show the actual space. Clue: sort order is alphbetical. Why does "name with" appear before "name no"???

and using rename syntax:

38   data newjunk;
39      set junk (rename = ( "name no leading space"n=noleadspace
40                           "name with leading space"n=withlead
41                           "name with trailing space"n=trailspace
42                          )
43                )
44      ;
ERROR: Variable 'name with leading space'n is not on file WORK.JUNK.
ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.JUNK.
45   run;

So I suspect one or more leading spaces in your name.

Re: Error reading in column to be renamed

PaigeMiller — Wed, 01 Nov 2023 14:53:31 GMT

@JibJam221 wrote:

26         data Master;
27         	set work.Master
28         	WORK.TABLE1(rename=("#"n=Number REC=Record Comm=Comments REC=Record "Processed IN/OUT"n=Processed_in_out));
ERROR: Variable 'Processed IN/OUT'n is not on file WORK.TABLE1.
ERROR: Invalid DROP, KEEP, or RENAME option on file WORK.TABLE1.
29         
30         
31         run;

So the variable is not in the data set TABLE1. Please run PROC CONTENTS on data set TABLE1 and show us the output.

Re: Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 15:04:03 GMT

Member	Num	Variable	Type	Len	Pos	Format	Informat	Label
WORK.TABLE1	1	#	Num	8	0	BEST.		#
WORK.TABLE1	2	REC	Char	3	316	$3.	$3.00	REC
WORK.TABLE1	3	Comm	Char	3	319	$3.	$3.00	Comm
WORK.TABLE1	4	Processed IN/OUT	Char	3	322	$3.	$3.00	Processed IN/OUT
WORK.TABLE1	5	Reading IN/OUT	Char	3	325	$3.	$3.00	Reading IN/OUT

I have tried copying the variable name directly from this table and still no luck.

Re: Error reading in column to be renamed

PaigeMiller — Wed, 01 Nov 2023 15:11:01 GMT

It's impossible to know from this presentation if the character(s) after Processed and before IN/OUT are one or more blanks, or even if they are special non-printing characters.

How about showing us the output from this:

proc contents data=work.table1 noprint out=_contents_;
run;
proc print data=_contents_(where=(name=:'P'));
    var name;
    format name $hex.;
run;

Re: Error reading in column to be renamed

ballardw — Wed, 01 Nov 2023 15:18:11 GMT

@JibJam221 wrote:

Member Num Variable Type Len Pos Format Informat Label

WORK.TABLE1 1 # Num 8 0 BEST. #

WORK.TABLE1 2 REC Char 3 316 $3. $3.00 REC

WORK.TABLE1 3 Comm Char 3 319 $3. $3.00 Comm

WORK.TABLE1 4 Processed
IN/OUT Char 3 322 $3. $3.00 Processed
IN/OUT

WORK.TABLE1 5 Reading
IN/OUT Char 3 325 $3. $3.00 Reading
IN/OUT

I have tried copying the variable name directly from this table and still no luck.

ODS output routinely strips leading characters in display. See my other post for a created example.

This is one way to get the name in a data set that you can copy from and see leading spaces.

options validvarname=any;

data junk;
  "name no leading space"n=3;
  " name with leading space"n=4;
  "name with trailing space "n=5;
run;

proc sql;
   create table names as
   select name 
   from dictionary.columns 
   where libname='WORK' and memname='JUNK';
quit;

The libname and memname (data set) names in the code must be in uppercase as they are stored in the SAS metadata as such.

Note that if you have changed the VALIDVARNAME setting then the SQL will return 0 rows as the names aren't actual "valid" in the session anymore.

Re: Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 16:25:05 GMT

LIBNAME	MEMNAME	MEMLABEL	TYPEMEM	NAME	TYPE	LENGTH	VARNUM	LABEL	FORMAT	FORMATL
WORK	TABLE1			#	1	8	1	#	BEST	0
WORK	TABLE1			REC	2	3	2	REC	$	3
WORK	TABLE1			Comm	2	3	3	Comm	$	3
WORK	TABLE1			Processed IN/OUT	2	3	4	Processed IN/OUT	$	3
WORK	TABLE1			Reading IN/OUT	2	3	5	Reading IN/OUT	$	3

Re: Error reading in column to be renamed

PaigeMiller — Wed, 01 Nov 2023 16:27:45 GMT

Hi, I don't know what this is, but it is not what I asked for. This is what I asked for:

proc contents data=work.table1 noprint out=_contents_;
run;
proc print data=_contents_(where=(name=:'P'));
    var name;
    format name $hex.;
run;

Re: Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 16:33:51 GMT

that was the output data from that exact code run..

I can paste the results as well but not sure what that is...

Obs	NAME
34	53637265656E696E670D0A494E2F4F5554202020202020202020202020202020
35	5365637572697479202020202020202020202020202020202020202020202020
36	536F757263652020202020202020202020202020202020202020202020202020

as well as the log:

NOTE: There was 1 observation read from the data set WORK._CONTENTS_.
      WHERE name=:'P';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: Error reading in column to be renamed

PaigeMiller — Wed, 01 Nov 2023 16:56:17 GMT

So this tells us that the 10th character (the first 9 being the word Processed) in the variable name is '0D'x which means that it is not a space, it is a carriage return character, and the 11th character is '0A'x which is a line feed character.

So when you refer to the variable name in SAS, you cannot refer to it with a space in it.

I believe this will work, but I cannot test it, you can.

set work.Master
    WORK.TABLE1(rename=("#"n=Number REC=Record Comm=Comments REC=Record "53637265656E696E670D0A494E2F4F5554"x=Processed_in_out));

This illustrates an important point. I think people should avoid like the plague creating variables with names that are not valid SAS variable names. Using variable names that are not valid SAS variable names causes all sorts of problems, this is just one illustration.

Re: Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 17:00:12 GMT

WOW!

Thanks so much for the explanation. Im trying to clean up and combine multiple very very messy datasets from Excel... created by non-data people. I think it may be worth me changing the name directly in excel before moving forward, since im assuming multiple columns will have this issue (they have similar formatting).

Thanks for helping me out and giving an explanation - definitely a learning lesson for me!

Re: Error reading in column to be renamed

PaigeMiller — Wed, 01 Nov 2023 17:02:14 GMT

Well, yes I agree, if you can clean this up in Excel you will be better off than trying to clean this up in SAS.

But you really didn't provide the feedback that I was expecting (and other readers might be expecting). Did my code work, or not?

Re: Error reading in column to be renamed

JibJam221 — Wed, 01 Nov 2023 17:15:27 GMT

It did work. I received no errors and the master table was updated with the values from Table1 under the desired "processed" column. Thank you!

Re: Error reading in column to be renamed

FreelanceReinh — Wed, 01 Nov 2023 20:23:47 GMT

@JibJam221 wrote:
It did work. I received no errors and the master table was updated with the values from Table1 under the desired "processed" column.

This is interesting because it does not work on my Windows SAS 9.4M5:

829   data Master;
830   set work.master
831       WORK.TABLE1(rename=("#"n=Number REC=Record Comm=Comments REC=Record "53637265656E696E670D0A494E2F4F5554"x=Processed_in_out));
                                                                              -------------------------------------
                                                                              214
                                                                              23
ERROR 214-322: Variable name "53637265656E696E670D0A494E2F4F5554"x is not valid.

ERROR 23-7: Invalid value for the RENAME option.

832   run;

(Also note that the duplication of REC=Record alone would throw an error and that "53637265656E696E67"x = "Screening", not "Processed".)

What does work on my computer is to supply the offending characters in a macro variable:

data _null_;
call symputx('CRLF','0D0A'x);
run;

data Master;
set work.master
    WORK.TABLE1(rename=("#"n=Number REC=Record Comm=Comments "Processed&CRLF.IN/OUT"n=Processed_in_out));
run;

Re: Error reading in column to be renamed

Tom — Wed, 01 Nov 2023 21:03:31 GMT

Set the SAS option VALIDVARNAME to V7 before trying to convert the worksheet into a SAS dataset.

That way SAS will generate valid SAS names from the beginning. It should replace those goofy characters with underscore characters.

Re: Error reading in column to be renamed

JibJam221 — Thu, 02 Nov 2023 14:31:59 GMT

Hi tom,

I used a macro to read-in all of the files at once using Proc Import. Would I use the VALIDVARNAME before running this chunk of the code?

%macro Readin;
%do i = 1 %to 13;
    Proc Import Out=Work.Table&i
    datafile= "....../Table&i..xlsx"
    DBMS=XLSX REPLACE;
    GETNAMES=YES;
    run; 

%end;

%mend;

%readin;

Re: Error reading in column to be renamed

Tom — Thu, 02 Nov 2023 14:44:23 GMT

Yes.

options validvarname=v7;
%readin;

If you have other SAS dataset with non-standard names you need to deal with you might need to change it back to ANY after you have read in the XLSX files.

Re: Error reading in column to be renamed

ballardw — Thu, 02 Nov 2023 15:02:52 GMT

@JibJam221 wrote:

WOW!

Thanks so much for the explanation. Im trying to clean up and combine multiple very very messy datasets from Excel... created by non-data people. I think it may be worth me changing the name directly in excel before moving forward, since im assuming multiple columns will have this issue (they have similar formatting).

Thanks for helping me out and giving an explanation - definitely a learning lesson for me!

I don't know how you are bringing the data into SAS but strongly suspect Proc Import may play a role.

If you are reading multiple files of the exact same structure (Excel column order and "basically the same" column headers) then you would be better off by 1) save each sheet to CSV prior to reading and 2) use a data step to read the CSV. Then you won't have any of the obnoxious bits from import of changing variable names, types and lengths of character values that will occur with Import.

If you are not reading multiple files and do use Proc import then set: OPTIONS VALIDVARNAME=V7; before import. That will strip out obnoxious characters replacing them with _ so will be much more of the "what you see is actually there".

The data step to read the CSV also means that you set the names, so ugly renames may not be needed at all.