Error reading in column to be renamed

[JibJam221]

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?

[PaigeMiller]

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.

[JibJam221]

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

[ballardw]

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.

[PaigeMiller]

---

@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.

[JibJam221]

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.

[PaigeMiller]

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;

[JibJam221]

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

[PaigeMiller]

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;

[JibJam221]

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

[PaigeMiller · Accepted solution]

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.

[JibJam221]

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!

[PaigeMiller]

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?

[JibJam221]

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!

[FreelanceReinh]

---

@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;