BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ss59
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

11 REPLIES 11
Reeza
Super User

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!


 

ss59
Obsidian | Level 7

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.

Reeza
Super User

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

ss59
Obsidian | Level 7

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

Reeza
Super User

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. 

ss59
Obsidian | Level 7

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

Reeza
Super User

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. 

Reeza
Super User
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.
ss59
Obsidian | Level 7

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.

Reeza
Super User

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

ss59
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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