Applying functions to variable names with special characters

[JROTE]

I've got a problem trying to deal with variable names with special characters (actually ending with +).

I am using options validvarname=any but when I use 'proc freq' or 'put' on these variable names, I can't get them to work because they thinks it's part of an expression/sum.

Also tried doing a workaround where I change the names of the variables, but that's failing too because any function doesn't like the variable name as an input.

Any ideas?

My code is below.

It gets the variable names using proc contents, and uses macro variables to loop through numeric and char variables separately.

The errors come when trying to do the proc freq and put, so basically need to know how to get these functions to read the full variable name without truncating/removing the + from end of variable name.

I am seeking to apply to a dataset I didn't create which has the variable names with + at the end - obviously I wouldn't choose to name my variables like that!!!:

OPTIONS VALIDVARNAME=ANY MLOGIC MPRINT SYMBOLGEN;



data shoes_2;

set sashelp.shoes;

format 'new_var_30+'n 8.;

'new_var_30+'n = 1000;

run;



%macro check_static(LName, DSName);



proc contents data=&LName..&DSName. noprint out=pc(keep=memname name type length nobs);

run;



/* Numeric Variables */

proc sql noprint;

select name into : num_vars separated by " "

from pc

where type = 1;

quit;



proc sql noprint;

select count(*) into : nvars_num

from pc

where type = 1;

quit;



/* Character Variables */

proc sql noprint;

select name into : char_vars separated by " "

from pc

where type = 2;

quit;



proc sql noprint;

select count(*) into : nvars_char

from pc

where type = 2;

quit;





/* Create empty dataset */

proc sql;

create table &DSName._Static

(

Dataset char length 32 format $20.,

Variable char length 32 format $20.,

Is_Static num length 8 format 3.,

Static_Value char length 20 format $20.

);

quit;



/* Loop for numeric variables*/

%do i=1 %to &nvars_num.;

              %let var = %scan(&num_vars., &i., ' ');

             

              proc freq noprint data=&LName..&DSName. order=freq;

              tables &var. / missing out=pf;

              run;

             

              data pf_one;

              set pf(obs=1);

              Variable = "&var.";

              run;

             

              proc sql;

              create table num_data as select

              a.memname as Dataset format $20.,

              a.name as Variable format $20.,

              case when b.count=a.nobs then 1 else 0 end as Is_Static format 3.,

              case when b.count=a.nobs then put(b.&var.,20.) else "" end as Static_Value format $20.

              from pc as a

              left join pf_one as b

              on a.name = b.variable

              where a.name = "&var.";

              quit;

             

              proc append base=&DSName._Static

                             data=num_data force;

              run;

             

%end;



/* Loop for character variables*/

%do j=1 %to &nvars_char.;

              %let var = %scan(&char_vars., &j., ' ');

             

              proc freq noprint data=&LName..&DSName. order=freq;

              tables &var. / missing out=pf;

              run;

             

              data pf_one;

              set pf(obs=1);

              Variable = "&var.";

              run;

             

              proc sql;

              create table char_data as select

              a.memname as Dataset format $20.,

              a.name as Variable format $20.,

              case when b.count=a.nobs then 1 else 0 end as Is_Static format 3.,

              case when b.count=a.nobs then put(b.&var.,$20.) else "" end as Static_Value format $20.

              from pc as a

              left join pf_one as b

              on a.name = b.variable

              where a.name = "&var.";

              quit;

             

              proc append base=&DSName._Static

                             data=char_data force;

              run;

             

%end;



proc sort data=&DSName._Static;

by Variable;

run;



%mend;



%check_static(work, shoes_2);

[Reeza · Accepted solution]

This is part of your problem.

/* Numeric Variables */
proc sql noprint;
select name into : num_vars separated by " "
from pc
where type = 1;
quit;

Your macro variables are not being created correctly to reference variables with + at the end. Look at the NLITERAL function to get them selected correctly. When debugging with macro code, remember to use the MPRINT/MLOGIC option and check each step during the development.

/* Numeric Variables */
proc sql noprint;
select nliteral(name) into : num_vars separated by " "
from pc
where type = 1;
quit;

---

@JROTE wrote:

I've got a problem trying to deal with variable names with special characters (actually ending with +).

I am using options validvarname=any but when I use 'proc freq' or 'put' on these variable names, I can't get them to work because they thinks it's part of an expression/sum.

Also tried doing a workaround where I change the names of the variables, but that's failing too because any function doesn't like the variable name as an input.

Any ideas?

My code is below.

It gets the variable names using proc contents, and uses macro variables to loop through numeric and char variables separately.

The errors come when trying to do the proc freq and put, so basically need to know how to get these functions to read the full variable name without truncating/removing the + from end of variable name.

I am seeking to apply to a dataset I didn't create which has the variable names with + at the end - obviously I wouldn't choose to name my variables like that!!!:

OPTIONS VALIDVARNAME=ANY MLOGIC MPRINT SYMBOLGEN;



data shoes_2;

set sashelp.shoes;

format 'new_var_30+'n 8.;

'new_var_30+'n = 1000;

run;



%macro check_static(LName, DSName);



proc contents data=&LName..&DSName. noprint out=pc(keep=memname name type length nobs);

run;



/* Numeric Variables */

proc sql noprint;

select name into : num_vars separated by " "

from pc

where type = 1;

quit;



proc sql noprint;

select count(*) into : nvars_num

from pc

where type = 1;

quit;



/* Character Variables */

proc sql noprint;

select name into : char_vars separated by " "

from pc

where type = 2;

quit;



proc sql noprint;

select count(*) into : nvars_char

from pc

where type = 2;

quit;





/* Create empty dataset */

proc sql;

create table &DSName._Static

(

Dataset char length 32 format $20.,

Variable char length 32 format $20.,

Is_Static num length 8 format 3.,

Static_Value char length 20 format $20.

);

quit;



/* Loop for numeric variables*/

%do i=1 %to &nvars_num.;

              %let var = %scan(&num_vars., &i., ' ');

             

              proc freq noprint data=&LName..&DSName. order=freq;

              tables &var. / missing out=pf;

              run;

             

              data pf_one;

              set pf(obs=1);

              Variable = "&var.";

              run;

             

              proc sql;

              create table num_data as select

              a.memname as Dataset format $20.,

              a.name as Variable format $20.,

              case when b.count=a.nobs then 1 else 0 end as Is_Static format 3.,

              case when b.count=a.nobs then put(b.&var.,20.) else "" end as Static_Value format $20.

              from pc as a

              left join pf_one as b

              on a.name = b.variable

              where a.name = "&var.";

              quit;

             

              proc append base=&DSName._Static

                             data=num_data force;

              run;

             

%end;



/* Loop for character variables*/

%do j=1 %to &nvars_char.;

              %let var = %scan(&char_vars., &j., ' ');

             

              proc freq noprint data=&LName..&DSName. order=freq;

              tables &var. / missing out=pf;

              run;

             

              data pf_one;

              set pf(obs=1);

              Variable = "&var.";

              run;

             

              proc sql;

              create table char_data as select

              a.memname as Dataset format $20.,

              a.name as Variable format $20.,

              case when b.count=a.nobs then 1 else 0 end as Is_Static format 3.,

              case when b.count=a.nobs then put(b.&var.,$20.) else "" end as Static_Value format $20.

              from pc as a

              left join pf_one as b

              on a.name = b.variable

              where a.name = "&var.";

              quit;

             

              proc append base=&DSName._Static

                             data=char_data force;

              run;

             

%end;



proc sort data=&DSName._Static;

by Variable;

run;



%mend;



%check_static(work, shoes_2);

---

[JROTE]

Thanks Reeza - that has sorted my problem.

Much appreciated!

[Tom]

Easiest solution is to stop making names like that. What purpose does it serve?  

Use VALIDVARNAME=V7 and then SAS will not allow you make names that are hard to deal with.

If you are forced to use such names by someone then make sure you are using name literals when referencing them.

proc sql noprint;
  select case when (type=1) then nliteral(name) else ' ' end
       , case when (type=2) then nliteral(name) else ' ' end
       , sum( type=1 ) format=32.
       , sum( type=2 ) format=32.
  into :num_vars separated by ' '
     , :char_vars separated by ' '
     , :nvars_num trimmed
     , :nvars_char trimmed
  from pc
;
quit;

...

%let var = %scan(&char_vars., &j., %str( ), q);

[JROTE]

Thanks Tom; that's very useful.

Tell me about it - I certainly didn't create the variable names with + sign at the end, and until yesterday didn't know it was possible.

At least I will know how to deal with it in future.

[Tom]

Make sure to update the code that is pulling the names back out of the macro variable using %SCAN().

Your original code is treating both space and single quote as delimiters. So if NLITERAL() decided to use single quotes to because the name contained double quote character or macro triggers & or % then your %SCAN() call will generate wrong strings.

If the names are the result of PROC IMPORT converting column headers from some report into names then perhaps just changing the VALIDVARNAME option to V7 will prevent the strange names from being created.