DATA Step, Macro, Functions and more

Rename strings in all variables with given IDs

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Rename strings in all variables with given IDs

[ Edited ]

Hi,

 

I have created the following IDs based on repeated 'states' in my input data. 

 

statesID
ABC_MNO_S3S1
ABC_S1S2
ABC_S2S3
ABC_PQR_S3S4
XYZ_MNO_S3S5
XYZ_S1S6
XYZ_S2S7
XYZ_PQR_S3S8

 

Is there an easy way to rename all the variable (column) names of my current data with the IDs?

 

Basically, whenever 'XYZ_PQR_S3' appears, I want it to be 'S8'. If there's a column name 'FromXYZ_PQR_S3_to_A' I want it to be renamed to 'FromS8_to_A'. This is true for all 8 (could be more) 'state'here.

 

Thanks!


Accepted Solutions
Solution
‎12-19-2017 12:17 PM
Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

Well, you can do this dynamically which is what I think you're after. 

 

  1. Use SASHELP.VCOLUMN to get the list of names in your table (or PROC CONTENTS). 
  2. In a data step:
    1. Load the names you're looking up into a temporary array
    2. Search through each name and use TRANWRD to replace the text you want, making sure to store it in a new variable. 
    3. This will create a data set with the old names and the new names you want. 
  3. Use PROC SQL to build your RENAME statement (this can be done in previous data step but I find it easier to separate it out at first.
  4. Use PROC DATASETS with the RENAME statement to rename the variables.

 

A portion of that - the latter part at least is demo'd here:

https://gist.github.com/statgeek/82d9f2854edc01560e0f

View solution in original post


All Replies
Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

I think you need to provide more details of what you're trying to achieve here. It's not clear to me if you're trying to change variable names or values.

 


ss59 wrote:

Hi,

 

I have created the following IDs based on repeated 'states' in my input data. 

 

states ID
ABC_MNO_S3 S1
ABC_S1 S2
ABC_S2 S3
ABC_PQR_S3 S4
XYZ_MNO_S3 S5
XYZ_S1 S6
XYZ_S2 S7
XYZ_PQR_S3 S8

 

Is there an easy way to rename all the variable (column) names of my current data with the IDs?

 

Basically, whenever 'XYZ_PQR_S3' appears, I want it to be 'S8'. If there's a column name 'FromXYZ_PQR_S3_to_A' I want it to be renamed to 'FromS8_to_A'. This is true for all 8 (could be more) 'state'here.

 

Thanks!


 

Contributor
Posts: 42

Re: Rename strings in all variables with given IDs

[ Edited ]

Hi @Reeza, I'm trying to change variable names. So, most of the variables in my data contains one of these eight strings, and I want to replace them with ith IDs in the ID column without changing the actual data.

Solution
‎12-19-2017 12:17 PM
Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

Well, you can do this dynamically which is what I think you're after. 

 

  1. Use SASHELP.VCOLUMN to get the list of names in your table (or PROC CONTENTS). 
  2. In a data step:
    1. Load the names you're looking up into a temporary array
    2. Search through each name and use TRANWRD to replace the text you want, making sure to store it in a new variable. 
    3. This will create a data set with the old names and the new names you want. 
  3. Use PROC SQL to build your RENAME statement (this can be done in previous data step but I find it easier to separate it out at first.
  4. Use PROC DATASETS with the RENAME statement to rename the variables.

 

A portion of that - the latter part at least is demo'd here:

https://gist.github.com/statgeek/82d9f2854edc01560e0f

Contributor
Posts: 42

Re: Rename strings in all variables with given IDs

I am being able to get the new column with new names, but proc datasets with rename statement giving me errors. Also after using 'tranwrd' the order of the variables are changing and it is difficult to sort if that happens

Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

Post your code. 

If the order is changing you likely did something incorrect in the code, the order shouldn't change in a data step. 

Contributor
Posts: 42

Re: Rename strings in all variables with given IDs

[ Edited ]

@Reeza here it is:

 

proc contents data =  parameters out= parameter_names (Keep= Name) order=varnum;
run;

/*make it dynamic later*/

data new_names;
set parameter_names;
Name = tranwrd(Name, "NFBE_NUTP_S3", "S5");
Name = tranwrd(Name, "NFBE_S1", "S6");
Name = tranwrd(Name, "NFBE_S2", "S7");
Name = tranwrd(Name, "NFBE_UTP_S3", "S8");
Name = tranwrd(Name, "FBE_NUTP_S3", "S1");
Name = tranwrd(Name, "FBE_S1", "S2");
Name = tranwrd(Name, "FBE_S2", "S3");
Name = tranwrd(Name, "FBE_UTP_S3", "S4");
put Name;
run;

/*create the list*/
proc sql;
select Name 
into :rename_list
separated by " "
from new_names;
quit;


%put &rename_list;

proc datasets library=work nolist;
modify parameters;
rename &rename_list;
run; quit;

 'tranwrd' is changing the order of the 'names', because it is affecting selective rows.

Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

Step 2.2 -> make sure to save it to a new variable....

To rename you need to have two variables, one is the new name and one is the old name, did you run/try the example I linked to? You can see how the macro variable looks and what yours should look like.

 

Search through each name and use TRANWRD to replace the text you want, making sure to store it in a new variable. 

Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

And you can keep varnum to sort it if desired - you don't keep it at the moment to maintain order, though I still don't see how it could possibly reorder the data.
Contributor
Posts: 42

Re: Rename strings in all variables with given IDs

sorry, I created the correct list of things to rename, but now I'm getting following errors in the proc datasets:

 

ERROR: Variable Year already exists on file WORK.PARAMETERS.
ERROR: Variable Period already exists on file WORK.PARAMETERS.
ERROR: Variable Segment1 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment2 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment3 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment4 already exists on file WORK.PARAMETERS.
ERROR: Variable Segment5 already exists on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S1_NFBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S2_NFBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S1_NFBE_S2 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S2_NFBE_S1 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S2_NFBE_UTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_NFBE_S1_NFBE_UTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S2_FBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S1_FBE_NUTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S1_FBE_S2 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S2_FBE_S1 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S2_FBE_UTP_S3 is not on file WORK.PARAMETERS.
ERROR: Variable TR_FBE_S1_FBE_UTP_S3 is not on file WORK.PARAMETERS.

 

I am supposed to modify the names, so if variable already exists, that should not be a problem. And the other variable do exist in the data.

Super User
Posts: 23,980

Re: Rename strings in all variables with given IDs

The error is in your code...you have to post your code as well, not just the errors.

Contributor
Posts: 42

Re: Rename strings in all variables with given IDs

Never mind, it was done correctly before, which I didn't check and then in the second run it didn't find many of the variables since they were already renamed.

 

Thanks for your help!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 150 views
  • 0 likes
  • 2 in conversation