Error reading in column to be renamed

[Ksharp]

FreelanceReinh,
This trick is a smart idea !
Why didn't I think out of it ?

[FreelanceReinh]

---

@Ksharp wrote:
FreelanceReinh,
This trick is a smart idea !
Why didn't I think out it ?

---

Thanks. It took me a while to figure it out. Maybe, like me at first, you thought that the data step compiler would see the carriage return, line feed after resolution of the macro variable reference in the same way as if it had been typed in manually (i.e., producing a line break) and, of course, the latter does not work.

[Ksharp]

Yes. I tried it ,but the latter does not work.

[Tom]

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.

[JibJam221]

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;

[Tom]

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.

[ballardw]

---

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

[ballardw]

---

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