Why '&loc1'n doesn't replace " " with a "_" in SAS studio university edition?

[Wendy_J]

Hello Everyone,

I have a file which is already in sas7bdat form, and there is a variable with observation written in a usual way, for example I have "Derry Medical Center" instead of "Derry_Medical_Center". I need to do analysis for each of distinct observations so I use proc sql to save those distinct values as macro-variables "loc1", "loc2", ..., "loc10". 

After some data processing processes, those distinct values become the variable names for another dataset and it is automatically transferred to "Derry_Medical_Center" by SAS. Now I want to rename those variable names as "site1", "site2", ..., "site10". So I used the code:

rename=("&loc1"n=site1 "&loc2"n=site2)

The code works in SAS but is not working in my SAS Studio (University Edition). '&macro'n does not replace the blank with an underscore. Can someone please explain to me why and how to solve this issue?  

[Tom]

You will need to show more details. It is not clear if the variables are named with spaces or underscores and what you want them to changed to. It is not clear what the macro variables contain.  Also why are you changing the names to SITE1, SITE2 etc?  At first it sounded like you just wanted to change "Derry Medical Center"n to Derry_Medical_Center, not give a generic name like SITE1.

Remember that macro expressions are not evaluated inside of single quotes. So 

'&macro'n

Is looking for a variable named literally &macro instead of one whose name is contained in the value the macro variable named MACRO. Use the double quote character instead of the single quote character.

"&macro"n

[Wendy_J]

OK. Here is the list of original names:

Derry Medical Center

Princeton - Plainsboro

St. Eligus Hospital

Twin Pines Medical Center

And SAS automatically changed it to the following after I transposed the data so that those become the variable names:

Derry_Medical_Center

Princeton___Plainsboro

St__Eligus_Hospital

Twin_Pines_Medical_Center

Now I want to rename those variables to site1, site2, site3, and site4. I used "&maco"n in SAS studio and that works but after I changed to the university edition it doesn't work anymore.  

BTW, I have already used the double quote in my code:

rename=("&loc1"n=site1 "&loc2"n=site2 "&loc3"n=site3 "&loc4"n=site4)

[Tom · Accepted solution]

So you want to get a list of variable names and rename them to SITE1 to SITEn.

Just get the list of variable names, using PROC CONTENTS or other methods.

Then number them (or perhaps use the VARNUM variable that metadata like PROC CONTENTS output will already have.

Then use the name and the number to generate to rename string.  Use the NLITERAL() function and it won't matter if the names are normal SAS names or ones that include spaces and other invalid characters for names.

proc sql noprint;
select catx('=',nliteral(name),cats('site',varnum))
  into :rename_list
  from my_variable_list
;
quit;

proc datasets lib=mylib ;
  modify my_dataset;
    rename &rename_list;
  run;
quit;

You might also explain what process you are doing that is changing the names. Perhaps you can avoid the need for name changes if you change that step instead.