Help with Blind Quoting Program

[mcook]

I am trying to write a program that will check, and transform character strings into Valid variable names.  As ValidVarName=V7 does upon import, replacing all invalid characters with underscores.

I have the following, and so far it appears to work.  However if the string contains % or &... It will run, and replace those characters with _ , but not before outputting numerous Warnings for apparent invocation of macro not resolved, and apparent symbolic reference not resolved.

Any ideas of how to quote out those attempts to resolve them? 

*Testing Table;
data Testing1;
	infile datalines delimiter=',';
	LEngth A $ 20;
	input A $ B $;
	datalines;
?D%a-v#e's N(a*m!e, a&b"c"
item1, item2
;
run;

*Transpose so COL1 contains Wanted Strings;
proc transpose data=testing1 out=testing1;
	var A B;
run;

*Select COL1 into VarNames with no leading or trailing spaces;
proc sql noprint;
	select Strip(Col1) into :VarNames
		separated by '|'
	from Testing1;
quit;

*Replace spaces, and single/double quotes with Underscores;
%LET VarNames = %SYSFUNC(TRANSLATE(%NRBQUOTE(&VarNames.),___,%STR( )%STR(%')%STR(%")));
%PUT VarNames=%NRBQUOTE(&VarNames.);

*VNum= number of elements in VarNames;
%LET VNum=%SYSFUNC(CountW(%NRBQUOTE(&VarNames.),|));
%PUT VNum=&VNum.;

%MACRO CheckV7();
	%DO L=1 %TO &VNum.;

		*Select Lth element of Varnames as NameCheck;
		%LET NameCheck=%SCAN(%NRBQUOTE(&VarNames.),&L.,|);
		%PUT NameCheck=%NRBQUOTE(&Namecheck);

		*If NameCheck is not a valid Variable Name, then;
		%IF %SYSFUNC(NVALID(%NRBQUOTE(&NameCheck.),V7))=0 %THEN
			%DO;
				*Repeat until NameCheck is a valid Variable Name;
				%DO %UNTIL(%SYSFUNC(NVALID(%NRBQUOTE(&NameCheck.),V7))=1);

					*InvSpot = position of first invalid character;
					%Let InvSpot = %SYSFUNC(NOTNAME(%NRBQUOTE(&NameCheck.)));
					%PUT InvSpot=&InvSpot.;

					*replace invalid character with underscore;
					%LET NameCheck= %SYSFUNC(TRANSLATE(%NRBQUOTE(&NameCheck.),_,%NRBQUOTE(%SUBSTR(%NRBQUOTE(&NameCheck.),&InvSpot.,1))));
					%PUT NameCheck=&NameCheck.;
				%END;
			%END;

		%GLOBAL STRING&L.;
		%LET String&L.=&NameCheck.;
	%END;
%MEND CheckV7;

%CheckV7();
%PUT String1=&String1. String2=&String2.;

I recently posted two other questions regarding this topic.

https://communities.sas.com/t5/SAS-Programming/function-to-change-character-strings-into-acceptable-...

https://communities.sas.com/t5/New-SAS-User/Help-With-NRSTR-code/m-p/714378#M27293

[Tom · Accepted solution]

Why are you processing the names with macro code?  Just process the names with SAS code and the macro processor will not come into play, so the presence of the & and % macro triggers will not matter. 

[mcook]

I am trying to import all variables as character. So i've imported with GETNAMES=NO so the first row is my variable names, forcing the column to be character. I now have to rename all of my variable names with my first row values. But they are not guaranteed to be valid variable names. Granted, It might be unlikely that one of those values will contain % or &, and I have a working code that assumes there are no % or & present. But for thoroughness and my own programming practice, I want to consider those possibilities as well.

[Tom]

So to test the code in my posting against one of your example datasets you can just transpose the first observation (the one with the names).  For example:

proc transpose data=have(obs=1) out=names(rename=(col1=NAME)) ;
  var _all_;
run;

So this will generate a dataset with two variables.  _NAME_ is the variable name that PROC IMPORT with GETNAMES='NO" generated. NAME is the values read from the first row in the source.  You can now run the datastep I posted and use that to generate a V7 compatible name and also the NLITERAL needed to reference the current name.  Note that the NLITERAL() function will use single quotes when the name contains macro triggers & or % so that using the NLITERAL value will not trigger the macro processor.

You could also run the PROC IMPORT with VALIDVARNAME=ANY and then use PROC TRANSPOSE with OBS=0 to get a dataset that only has the _NAME_ variable.  So then feed that _NAME_ value into the algorithm to generate V7 compatible names.

Once you have NLITERAL and V7 names you can generate RENAME pairs from that.

proc sql noprint;
select catx('=',nameliteral,v7name) into :renames separated by ' '
 from want 
 where name ne v7name 
;
quit;

proc datasets lib=work nolist;
  modify have ;
    rename &renames;
  run;
quit;

[mcook]

yes, doing it all in a data step was a much easier solution.

[Tom]

Here is one example:

data contents;
  infile datalines truncover;
  input name $32. ;
datalines;
?D%a-v#e's N(a*m!e
a&b"c"
item1
2021
;

data want ;
  set contents;
  length v7name $32 nameliteral $64 ;
  nameliteral=nliteral(name);
  v7name =translate(trim(prxchange('s/([^a-zA-Z0-9]+)/ /',-1,name)),' _','_ ');
  v7name=prxchange('s/(^[^_a-zA-Z])/_$1/',1,v7name);
run;

proc print;
run;

Obs    name                  v7name                nameliteral

 1     ?D%a-v#e's N(a*m!e    _D_a_v_e_s_N_a_m_e    '?D%a-v#e''s N(a*m!e'N
 2     a&b"c"                a_b_c                 'a&b"c"'N
 3     item1                 item1                 item1
 4     2021                  _2021                 "2021"N